Implicit conversions

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)

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.