Implicit conversions

Or ‘How to slow down a query without apparent reason’

I’ve discussed functions in where clauses before and shown how they can prevent index usage and slow down queries. I’ve also discussed data type conversions and shown that SQL Server sometimes does the conversions in places not expected. There’s a nice little gotcha that results from the combination of these two. Let’s take a look at a simple example.

Which of the following queries will run slower? (sample code at the end)

Select ID from TestingConversion where Code = 'AAA'
Select ID from TestingConversion where Code = N'AAA'

They look like they should run the same, however they don’t. If you look at the execution plan of the second, you’ll see an implicit conversion of the column Code from Char to nChar before the filter is done. ie, the second query is equivalent to writing

Select ID from TestingConversion where CAST(Code AS nchar(3)) = N'AAA'

Hence, just like for any other filter in the where clause, that conversion prevents index usage and results in queries that run slow when they look like they should run fast.

Sample code:
CREATE TABLE TestingConversion (
    ID int identity,
    Code CHAR(3),
    InsertDate DATETIME default getdate()
)

CREATE CLUSTERED INDEX idx_TestingConversion_ID ON TestingConversion (ID)

CREATE NONCLUSTERED INDEX idx_TestingConversion _Code on TestingConversion (Code)

;WITH DataInsert (RowNo) AS (
    SELECT 1 AS RowNo
    union all
    select RowNo+1 FROM DataInsert WHERE RowNo<5000
)
insert into TestingConversion (Code, InsertDate)
SELECT char(FLOOR(Rand(RowNo*78000)*26)+65) + char(FLOOR(Rand(RowNo*15000)*26)+65) + char(FLOOR(Rand(RowNo*51000)*26)+65),
    DATEADD(mi,Rand(RowNo*78000)*575000,'2000/01/01')
    FROM DataInsert
    OPTION (MaxRecursion 5000)