August 6, 2009 at 9:56 pm
I know that the leaf level of the non-clustered index points to the clustered index by the clustered key. So for most queries using the non-clustered index, you would have a seek on the non-clustered index to the leaf level, then you would have a seek on the clustered index to the leaf level to retrieve data you are searching for.
Now, lets say you have a table with name, date, and some other columns. You have a clustered index on date, and a non-clustered index on name.
Consider the following query:
SELECT date FROM table where name = 'myname'.
In this case, the value you are trying to retrieve by seeking through the non-clustered index IS the clustered key. So the pointer to the clustered index is the value you need to retrieve. Will SQL Server stop at the leaf level of the non-clustered index and not go down to the clustered index in this situation? Or will it perform like normal and traverse the clustered index to the leaf level, wasting page reads.
Does SQL Server also stop at intermediate pages is the data it needs exists in them?
August 6, 2009 at 10:19 pm
Since the valueof the column(s) that are defined in the clustered index are stored at the leaf level of the index, SQL Server will not do a bookmark for your query.
As for detecting whether a query can be satisisfied at some higher level in an index, I am not sure but it would not surprise me. SQL Server is pretty good at working out efficient ways to satisfy a query.
How about doing some tests - create a table with indexes that match the sort of areas you want to check for. Run some queries and see what the query plans look like.
August 7, 2009 at 10:09 am
Thanks,
I tested and you are correct!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply