The implicit conversion monster

Here is one way you can avoid the use of a good index, and thus degrade performance: First, let’s create the test case.

/****** Object: Table [dbo].[Test1] Script Date: 03/24/2010 13:44:18 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Test1](

[Id1] [int] NULL,

[Id2] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (1, N'1')

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (2, N'2')

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (3, N'3')

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (4, N'4')

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (5, N'5')

INSERT [dbo].[Test1] ([Id1], [Id2]) VALUES (6, N'6')

/****** Object: Index [Test1] Script Date: 03/24/2010 13:45:34 ******/

CREATE NONCLUSTERED INDEX [Test1] ON [dbo].[Test1]

(

[Id2] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF

, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON

, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

And now let’s run two queries:

 select Id2 from Test1 where Id2 = 1 select Id2 from Test1 where Id2 = '1' 

The e

xecution plan looks like this:  In the first case we have an index scan and in the second case we have an index seek. Why? 

Because of the Implicit conversion, which occurs in case of predicates with different data types.  In this case I have only a few rows in the table, and the performance implication is not significant, however if you have millions of rows you will know the difference. The bottom line: take care of your data types. Take care of your indexes. Take care of your query syntax. Or just contact SQLService. P.S. Yes, we do offer performance tuning! (including scanning your system for Implicit Conversions).