-1

I'm using Microsoft SQL Server 2019 and when I execute:

SELECT TOP 10 *
FROM  WideWorldImporters.Sales.Invoices

SELECT TOP 10 CustomerID
FROM  WideWorldImporters.Sales.Invoices

It gives results:

enter image description here

Which is incorrect because those aren't the "top 10" customer IDs as displayed by the first query.

Full Screenshot: enter image description here

Edit: The behaviour I expected above matches what actually happens in SQL Sever 2014. I suspect they changed the underlying implementation in SQL Server 2019, although it still satisfies the documented behaviour.

Julian
  • 33,915
  • 22
  • 119
  • 174
NoName
  • 9,824
  • 5
  • 32
  • 52

4 Answers4

5

A TOP without ORDER BY is unpredictable. This is documented by Microsoft. From Microsoft docs:

When you use TOP with the ORDER BY clause, the result set is limited to the first N number of ordered rows. Otherwise, TOP returns the first N number of rows in an undefined order.

...

In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP.

See also how does SELECT TOP works when no order by is specified?

Community
  • 1
  • 1
Julian
  • 33,915
  • 22
  • 119
  • 174
  • In the Best Practices section of the docs they say, "In a SELECT statement, always use an ORDER BY clause with the TOP clause. Because, it's the only way to predictably indicate which rows are affected by TOP." But when I right click a table and pick "Select Top 1000 Rows", I get a SELECT TOP query containing no ORDER BY. Smh... – NoName Nov 10 '19 at 01:03
4

You have no ORDER BY so the top 10 results by an indeterminate order are returned. This ordering can change, from one execution to the next.

Tables in SQL represent unordered sets. If you want particular rows using TOP, you need to have an ORDER BY.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

This is too long for a comment.

In SQL, table records are unordered. For this reason, a query like yours:

SELECT TOP 10 * FROM  WideWorldImporters.Sales.Invoices

... will produce inconsistent results, because it is missing an ORDER BY clause. You need to tell your RDBMS which column should be used to order the records so in can define which TOP records should be returned. For example, something like:

SELECT TOP 10 * FROM  WideWorldImporters.Sales.Invoices ORDER BY InvoiceID DESC
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    The behaviour I expected above matches what actually happens in SQL Sever 2015. I suspect they changed the underlying implementation in SQL Server 2019, although it still satisfies the documented behaviour of an "undefined order". – NoName Nov 10 '19 at 01:06
0

This result is absolutely normal, as your query doesn't specify an order. The top 10 returns the first 10 results.

If you don't specify any ordering clause, the results will be returned according to the previous operations of the SQL engine, which might not always be the same, and surely not what you expect them to be.

Max Voitko
  • 1,542
  • 1
  • 17
  • 32
Buberuglu
  • 1
  • 1