Sean Lange (5/9/2013)
Lynn Pettis (5/9/2013)
I may be wrong, would have to test, but I don't think 6e will use an index either because of the NOT IN.
You may be right there.
Just a refresher here is #6 again.
6. Field f of a table is indexed. Which of the following query fragments is written so the index cannot be used? (Assume p1 and p2 are the correct data types for their role in each fragment.)
a. where f > :p1
b. where f between :p1 and dateadd( day, :p2, :p1)
c. where dateadd( day, :p2 , f) > :p1
d. where dateadd( day, :p2, :p1) < f
e. where f not in( select …)
The actual correct answer would that the index will be used in all of these. The difference is that the index will be scanned by some and seeked by others. 😉
It would also be dependent on if the index on f is clustered or nonclustered, what the query is returning from the table (select * vs select col1, col2, etc), how much data exists and how much is being returned, currency of the statistics, and many other factors not yet mentioned.