T-SQL 101: 90 Numbering output rows by using ROW_NUMBER

In SQL Server 2000 and earlier versions, I often heard people ask “How do I output a row number beside each row that’s output in my query?”

I remember some people arguing that it wasn’t a valid request, as it didn’t feel “set-based” but it was an appropriate request, and it could be dealt with in a set-based manner. Sometimes it’s very, very useful to be able to do that.

In SQL Server 2005, we got the ROW_NUMBER function, and it did just what people were asking for. You got your normal query output, but also got a column with 1 for the first row, 2 for the second, and so on.

What’s really good about this function though, is that it has an OVER clause that lets you specify the order for the numbering, separate to the order for the query. In the example shown above, I’ve ordered the output rows by CityName, and I’ve also ordered the row numbers by CityName. But I didn’t have to. I could have said this:

SELECT ROW_NUMBER() OVER(ORDER BY CinemaID)
           AS RowNumber,
       CityName, TradingName
FROM dbo.Cinemas
ORDER BY CityName;

That would have created row numbers based on the order of the CinemaID but the query would have output rows still in CityName order.

Note also that there was nothing special about the column alias (RowNumber) that I used. That could have been any name.

ROW_NUMBER was a powerful addition to T-SQL, but it was only the first of a set of window functions that were added to the language. We’ll see more in later posts.

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.

2021-03-15