Thursday, March 7, 2013

Filtered Indexes with FORCE SEEK

If you closely analyzed the below query, you will see that there is a non-clustered filtered index on ScrapReasonID column and filter condition is ScrapReasonID = 17 . In the query, there is a force seek with the above index but the where condition is 30. So, you can see there is an contradiction between index and query filter condition.

image

So what do you think about the output of the above query.

You will think that index hint will be ignored.However, this is the output for the above query.

Msg 8622, Level 16, State 1, Line 2
Query processor could not produce a query plan because of the hints defined in this query.
Resubmit the query without specifying any hints and without using SET FORCEPLAN.

No comments:

Post a Comment