Wednesday, August 3, 2011

Which index will get used to locate a record in this scenario?

Just came across this question in interview. If any one knows the answer then please let me know -->
In SQL server, if a table column has both indexes applied over it i.e. clustered as well as non clustered then which index will get used to locate a record?
I've given reply as clustered index because in clustered index leaf node refers to actual data & no further traversal needed. So optimizer will select clustered index in its execution plan. But that answer was just my guess. Later on I read that for count function over such indexed column, it uses non clustered index as it is faster... Can't find the answer over internet for question "which index will get used to locate a record?" Any one who knows the correct answer??

No comments:

Post a Comment