One common use case for sequences in SQL Server is to use them to number the results of a query. For example, we can add a column that has a number that increments with each row, similar to a row counter (but with a sequence that we define).
But we may encounter an issue if we try to order the results. The way to generate a new sequence number is with the NEXT VALUE FOR function. The problem is that this function can’t be used in queries that have the ORDER BY clause. Well, to be more specific, it can’t be used in queries that have the ORDER BY clause where it’s most commonly placed in SQL queries. Trying to order a query like this will result in an error.
The good news is that we can use it within an OVER clause. And the NEXT VALUE FOR function allows us to add an OVER clause with an ORDER BY sub clause.
Example
Here’s an example of ordering a query that uses a sequence to number the results:
SELECT
NEXT VALUE FOR Sequence1 OVER (ORDER BY AlbumName ASC) AS AlbumNumber,
AlbumName,
Genre
FROM Albums
INNER JOIN Genres
ON Albums.GenreId = Genres.GenreId;Result:
AlbumNumber AlbumName Genre ----------- ------------------------ ------- 1 All Night Wrong Jazz 2 Along Came Jones Pop 3 Big Swing Face Jazz 4 Blue Night Pop 5 Casualties of Cool Rock 6 Epicloud Rock 7 Eternity Pop 8 Killers Rock 9 Long Lost Suitcase Pop 10 No Prayer for the Dying Rock 11 No Sound Without Silence Pop 12 Piece of Mind Rock 13 Powerage Rock 14 Powerslave Rock 15 Praise and Blame Pop 16 Scandinavia Pop 17 Singing Down the Lane Country 18 Somewhere in Time Rock 19 The Sixteen Men of Tain Jazz 20 Ziltoid the Omniscient Rock 20 row(s) returned
Here, I used a sequence that starts at 1 and increments by 1. We were able to sort by the AlbumName column without any problems by specifying this in the OVER clause of the NEXT VALUE FOR function.
Let’s change it to a descending sort and run the query again:
SELECT
NEXT VALUE FOR Sequence1 OVER (ORDER BY AlbumName DESC) AS AlbumNumber,
AlbumName,
Genre
FROM Albums
INNER JOIN Genres
ON Albums.GenreId = Genres.GenreId;Result:
AlbumNumber AlbumName Genre ----------- ------------------------ ------- 21 Ziltoid the Omniscient Rock 22 The Sixteen Men of Tain Jazz 23 Somewhere in Time Rock 24 Singing Down the Lane Country 25 Scandinavia Pop 26 Praise and Blame Pop 27 Powerslave Rock 28 Powerage Rock 29 Piece of Mind Rock 30 No Sound Without Silence Pop 31 No Prayer for the Dying Rock 32 Long Lost Suitcase Pop 33 Killers Rock 34 Eternity Pop 35 Epicloud Rock 36 Casualties of Cool Rock 37 Blue Night Pop 38 Big Swing Face Jazz 39 Along Came Jones Pop 40 All Night Wrong Jazz 20 row(s) returned
As expected, the query results are now sorted by the AlbumName column in descending order, but the sequence numbers increment the same way regardless of how any other columns are sorted.
In this second example, the sequence numbers start at 21 because our previous example finished at 20.
Without the OVER Clause
As mentioned, if we try to sort the query without using the OVER clause, we’ll end up with an error:
SELECT
NEXT VALUE FOR Sequence1 AS AlbumNumber,
AlbumName,
Genre
FROM Albums
INNER JOIN Genres
ON Albums.GenreId = Genres.GenreId
ORDER BY AlbumName DESC;Result:
Msg 11723, Level 15, State 1, Line 2 NEXT VALUE FOR function cannot be used directly in a statement that contains an ORDER BY clause unless the OVER clause is specified.