Which index is used in select and why?
Posted
by Lukasz Lysik
on Stack Overflow
See other posts from Stack Overflow
or by Lukasz Lysik
Published on 2010-05-27T08:29:49Z
Indexed on
2010/05/27
8:31 UTC
Read the original article
Hit count: 182
I have the table with zip codes with following columns:
id - PRIMARY KEY
code - NONCLUSTERED INDEX
city
When I execute query
SELECT TOP 10 * FROM ZIPCodes
I get the results sorted by id
column. But when I change the query to:
SELECT TOP 10 id FROM ZIPCodes
I get the results sorted by code
column. Again, when I change the query to:
SELECT TOP 10 code FROM ZIPCodes
I get the results sorted by code
column again. And finally when I change to:
SELECT TOP 10 id,code FROM ZIPCodes
I get the results sorted by id
column.
My question is in the title of the question. I know which indexes are used in the queries, but my question is, why those indexes are used? I the second query (SELECT TOP 10 id FROM ZIPCodes
) wouldn't it be faster if the clusteder index was used? How the query engine chooses which index to use?
© Stack Overflow or respective owner