T-SQL 101: 16 Ordering the output of a T-SQL query

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 whether that’s needed at all.

Sorting can be a very expensive operation if there is no suitable index that’s already sorted the data for you. And the question is: should SQL Server 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.

Let’s let SQL Server do it

However, let’s assume that you do want SQL Server to sort the data. An important first concept is that there’s no default order for data when you query SQL Server. I regularly see people who think that if a table has a primary key, or has a clustering key (long story we’ll talk about another time), that the data will come back in that order.

It might, but you can’t guarantee it. Don’t assume that if it does in simple tests, that it will continue to do so in production volumes and situations.

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:

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.

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.

2019-05-06