Implicit conversion does not affect performance

Question

I have read about implicit conversion on indexes affect performance so by that mean in the following query

select count(*)
from fpc
where SKey in (201701, 201702)

as SKey is of type int, if I change above query to

select count(*)
from fpc
where SKey in ('201701', '201702')

performance will degrade.

I tested this on a table (which has millions of rows). question is why I did not see any difference on execution plan and time.

I have non-clustered columnstore index on SKey.

There are about 20 million rows per SKey and I have about 100 different SKey

Answer 1

The issue with implicit conversion is that it can prevent the efficient use of an index. If a function has to be applied to a column to get the correct comparison value, then an index seek on that column cannot be used in the search.

However, SQL Server has to decide which value to convert when it has to compare values of different types. It does this based on its Data Type Precedence rules.

If you look at that page, you'll see that integer values have a higher precedence that varchar values. So, the varchar value is the one that gets converted.

In the example you present, the indexed column is an integer column, and the hard-coded values are varchar (or char, or nvarchar - all have a lower precedence than integer).

So, the hard-coded values are converted to integers. Since your column does not undergo a transformation, the index can still be used.

NOTE: this is not the only factor that goes into this. For example, conversions between different data types that are in the same "family" (for example, int and bigint) will still allow an index seek, regardless of which datatype is the indexed column.

This is why one should match datatypes whenever possible, or use explicit conversions on non-indexed data where needed; by controlling the conversion process, you can usually avoid the problem.

On a personal note - this precedence order has long been an annoyance. If you have a character value that you need to compare to a numeric value, unless you explicitly perform the conversion, SQL Server will try to convert the character value (which will not always have a valid numeric representation) to a numeric, instead of converting the numeric value (which should always have a valid character representation). Sigh.

---

Bob Klimes pointed out a couple of related articles:

• Implicit Conversions that cause Index Scans, by Jonathan Kehayias, which conducts a test to see which data types result in an index scan when converted implicitly; and
• Data Type Mismatches Don’t Always Cause a “Bad” Implicit Conversion and Index Scan, by Kendra Little, which covers the fact that implicit conversions between different types in the same "family" still allow an index seek instead of an index scan.