If I run the following query without "TOP" in the Select clause then a index scan is run against table tBookingGuest. When I run the query with "TOP(10)" the optimizer chooses to do an index seek against tBookingGuest with the proper covering index. Why would the optimizer behave this way?
---------------------- Here is some schema information
The optimiser may be deciding that an index seek is 'less costly' for the TOP 10, rather than the index scan for the full results.
Also with a Top 10, you are asking the engine to do less work, and without an ORDER BY - simply return 10 'random' records. It's always quicker to give someone 10 random records than 100 specific ones.
How many rows are returned when not limited by the TOP 10?
As with any optimizer question - have you updated your index statistics?
answered May 08 '12 at 10:00 AM
Kev Riley ♦♦