SQL Server: Why do these queries return different result sets ???

Question

Query 1 = select top 5 i.item_id from ITEMS i

Query 2 = select top 5 i.item_id, i.category_id from ITEMS i

Even if I remove the top 5 clause they still return different rows.

if I run "select top 5 i.* from ITEMS i" this returns a completely different result set !!

Answer 1

Because the results of a "TOP N" qualified SELECT are indeterminate if you do not have an ORDER BY clause.

Answer 2

Without an ORDER BY clause, you cannot predict what order you will get results. There is probably an interesting underlying reason for why SQL Server processes those queries differently, but from a user's perspective the solution is simply to impose the ORDER BY clause that's relevant to your query, thus guaranteeing you'll know which five items come first.

Answer 3

The reason is simple: you did not specify an ORDER BY clause. So, for example, the optimizer could choose to use different indexes to satisfy two different queries, if there is a lean index that contains ItemID but not CategoryID it can be touched to satisfy the first query. A very common question, has a canned naswer:

Without ORDER BY, there is no default sort order.

Answer 4

Since you're not specifying an ORDER BY clause, the optimizer will determine the most efficient way to do the query you're asking to do. This means there might be a different indexing done for the two columns you've indicated in your two queries, which results in what you're seeing.