SQL: There is no default output order for SQL queries

There is a really common misconception about output order for T-SQL queries. I can’t tell you how often I’ve heard people say that if you don’t specify an ORDER BY clause, then you’ll see the rows returned in the order of the primary key.

The assumption they’re also making is that the primary key is the clustering key. And that’s not necessarily true either. You can have a non-clustered primary key on a table.

Either way, both assumptions are wrong. SQL Server does not return data in either the primary key or the clustering key order, if you don’t use an ORDER BY. Early on, there were some qwirks that caused it to often be the case, but it was never true.

SQL Server will retrieve the rows in whatever way it feels is either most efficient (for large numbers of rows) or trivial (where that applies). You can imagine, for example, that if it used a separate thread to retrieve the data from different partitions of a table, it’s hardly likely to then sort all the output for no reason.

Even if it seems to come back in a predictable order in simple tests, do not assume that it will always do that.

Specifying ORDER BY

There’s a simple rule:

If you want rows to come back in a specific order, you need to say which order you want.

And we do that with the ORDER BY clause. Look at these queries:

SELECT ProductID, [Description]
FROM dbo.Products
ORDER BY [Description], ProductID;

SELECT ProductID, [Description]
FROM dbo.Products
ORDER BY [Description] DESC, ProductID;

The simplest example would be to just order the rows by a single column. In the first query though, I’ve ordered by two columns. That means that the rows are sorted by Description first, and if the Description values are the same, they are then sorted by ProductID, within each Description.

Ordering can also be ascending (ASC) or descending (DESC). Ascending is the default and we usually don’t put ASC in the queries, but there’s nothing to stop you doing that if you prefer it.

When you have multiple columns in the ordering, each column can be sorted in a different order. In the second query, I’ve made the Description column sort descending, but then within each Description, the ProductID would be sorted ascending.

But do you need it sorted at all?

When you run a query against SQL Server, you might want the rows to be returned in a specific order. However, the first thing you should ask yourself is do I need the output sorted?.

Sorting can be a very expensive operation if there is no suitable index that’s already has the data sorted like you want. Ask yourself if SQL Server should be the one doing the sorting. In many cases, the client application that’s reading the data might be a better place to do the sorting.

That’s not always true but it’s worth considering. You often have way more clients than your single database server, and sorting data unnecessarily in the database server could limit your ability to scale your system to higher volumes.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-08-02