What index is used in the choice and why?

Question

I have a table with zip codes with the following columns:

id - PRIMARY KEY
code - NONCLUSTERED INDEX
city

When I execute the request

SELECT TOP 10 * FROM ZIPCodes

I get results sorted by column id. But when I change the request to:

SELECT TOP 10 id FROM ZIPCodes

I get results sorted by column code. Again, when I change the request to:

SELECT TOP 10 code FROM ZIPCodes

I get the results sorted by column again code. And finally, when I go to:

SELECT TOP 10 id,code FROM ZIPCodes

I get results sorted by column id.

. , , : ? (SELECT TOP 10 id FROM ZIPCodes) , ? , ?

Answer 1

, , . : TOP ORDER BY .

, , , , , id, . , id.

Answer 2

, , , - 10 . , , .

, , VARCHAR (4000). , . nonclusted Code, , .

Answer 3

If you have an index that has the code as the first / only column, although it is not the first column in your table, it is more likely to scan the index faster than scan the table since you are only selecting one column. You should look at the execution plan in a bit more detail.