CODEWITHSUNDEEP
sql-servert-sqltop-n
Storm
Storm

Reputation: 4445

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

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 !!

Upvotes: 1

Views: 1234

Answers (4)

Joseph
Joseph

Reputation: 25513

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.

Upvotes: 3

A-K
A-K

Reputation: 17080

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.

Upvotes: 2

VoteyDisciple
VoteyDisciple

Reputation: 37803

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.

Upvotes: 3

RBarryYoung
RBarryYoung

Reputation: 56725

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

Upvotes: 7

Related Questions