Which index is used in select and why?

Question

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?

Answer 1

The query engine is using the Id index because all it needs to know to perform the query is the top 10 values in that column. If it was to use the clustered index it would have to read all the values for that row to get the values you want.

To emphasize the point, imagine that the city column was a VARCHAR(4000) column. If it used the clustered index it would have to read over the city column for every row it processed. By using the nonclusted Code index it simply has to read the bytes it will be returning.

Answer 2

If you have an index that has code as the first/only column, while it isn't the first column in your table, it's probably faster to scan the index than it is to scan the table, since you're only selecting one column. You'd have to look at the execution plan for a bit more detail.

Answer 3

As you're not asking for a specific set of data, there's no 'optimal' way to pick which rows are returned. Hint: TOP without ORDER BY is nonsensical.

I assume the queries which can be satisfied by index data alone use that index, while the data 'sorted by id' didn't use an index at all. Your tuples just happen to be stored in a specific order as the table is clustered by id.