1

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?

2
  • 1
    Why is event factor a float? Are you going to require dozens of decimal places? I would suspect that be defined as a decimal with a much smaller scale and precision (and more predictable behavior, e.g. rounding). Jan 21, 2013 at 23:44
  • 1
    That is not sargable anyway. You can create and index a computed column on CASE WHEN eventCount > eventCutoff * eventFactor THEN 1 ELSE 0 END to use an index. Jan 22, 2013 at 9:40

1 Answer 1

5

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...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.