If we ever need to assign repeating sequence numbers to a result set in SQL Server, we can use a sequence object with the appropriate arguments.

In particular, we can use the CYCLE argument to specify that the sequence will cycle through the range of numbers. In other words, once it reaches the end of the sequence, it starts again, and will continue to reiterate every time it completes the sequence.

Example

Here’s an example of creating a sequence with the CYCLE argument:

CREATE SEQUENCE Sequence1
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    MAXVALUE 4
    CYCLE;

This sequence will cycle between 0 and 4. Although our start value is 1, when it cycles back around, it will start at 0 (because it cycles between the min and max values).

We can now reference this sequence when we run a SELECT query. We can use it to add a column to the result set that either increments or decrements through a range of numbers.

Here’s an example of applying the sequence to the result set of a query:

SELECT
    NEXT VALUE FOR Sequence1 AS ArtistGroup,
    ArtistName
FROM Artists;

Result:

ArtistGroup  ArtistName            
-----------  ----------------------
1            Iron Maiden           
2            AC/DC                 
3            Allan Holdsworth      
4            Buddy Rich            
0            Devin Townsend        
1            Jim Reeves            
2            Tom Jones             
3            Maroon 5              
4            The Script            
0            Lit                   
1            Black Sabbath         
2            Michael Learns to Rock
3            Carabao               
4            Karnivool             
0            Birds of Tokyo        
1            Bodyjar               

16 row(s) returned

We can see that the sequence started at 1, then once it reached its maximum value, it returned to the MINVALUE and incremented through the range again. It then repeatedly cycled between its MINVALUE and MAXVALUE until all rows were returned.

Other Queries

We can use the same sequence on other queries. For example, let’s run a similar query, but on a different table:

SELECT
    NEXT VALUE FOR Sequence1 AS AlbumGroup,
    AlbumName
FROM Albums;

Result:

AlbumGroup  AlbumName               
----------  ------------------------
2           Powerslave              
3           Powerage                
4           Singing Down the Lane   
0           Ziltoid the Omniscient  
1           Casualties of Cool      
2           Epicloud                
3           Somewhere in Time       
4           Piece of Mind           
0           Killers                 
1           No Prayer for the Dying 
2           No Sound Without Silence
3           Big Swing Face          
4           Blue Night              
0           Eternity                
1           Scandinavia             
2           Long Lost Suitcase      
3           Praise and Blame        
4           Along Came Jones        
0           All Night Wrong         
1           The Sixteen Men of Tain 

20 row(s) returned

Notice that the sequence continues from where it left off from the previous query. It doesn’t start back at 1 just because we query a different table. And it doesn’t start back at MINVALUE. It simply continues on from where it left off when it was previously called.

If we want it to start at its initial start value (or any other value for that matter), we can reset the sequence by using the ALTER SEQUENCE statement with the RESTART WITH option.

Cycling Through Partitions

SQL Server also has a ROW_NUMBER() function that allows us to partition our result set, then have a sequential number iterate through each partition.

See How ROW_NUMBER() Works in SQL Server for an example of what I mean.