Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
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). –  Aaron Bertrand Jan 21 '13 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. –  Martin Smith Jan 22 '13 at 9:40

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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