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

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?

  • 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 at 1: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.

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
  • 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 at 1: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.

New contributor
Buberuglu is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.