June 24, 2012 at 2:57 pm
The INDEX(1) query hint forces the query to use an INDEX scan or seek. Are there exactly two ways to use an INDEX, seeking or scanning? If not, why would you force one of two options with INDEX(1)?
Why would you force an INDEX scan? My guess is to not use what is in the cache and check the entire INDEX.
June 24, 2012 at 5:16 pm
The INDEX(1) hint forces SQL to use the clustered index (if there is one) for that query. It does not specify how the clustered index should be used, whether it be a seek or a scan, the optimiser is free to decide that.
If you use the INDEX(0) hint, it forces a table scan.
It's got nothing to do with cache, all queries read from cache. They're for the rare cases when SQL is picking a sub-optimal plan, you know why it is doing so and chose to force either the use of the clustered index or a table scan and know why that forced access path is better.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply