Does implicit casting negatively affect index usage?

Question

I have a query similar to the following:

SELECT id FROM MyTable
WHERE eventCount > eventCutoff * eventFactor

eventCount and eventCutoff are defined as a smallint, and eventFactor as a float.

I have two questions: is there an implicit cast of eventCount to a float for the comparison? And if so, will this prevent me from effectively leveraging an index on eventCount?

Answer 1

Such conversion often will occur, and they can often impact cardinality estimation (and hence can affect plan quality). It will depend though on data types, existing indexes, cardinality etc. For example this query might not be able to seek in any case, as SQL Server might be using a scan regardless.

You should be able to determine if this is an issue in your case by inspecting the execution plan - do you get a scan on eventCount if you first cast eventFactor to a smallint? What if you use a local smallint variable that would result in the same number of rows that would match the filter? We can't tell from here...