When we create a sequence object in SQL Server, we have the option of making it a repeating sequence or a nonrepeating sequence. By repeating I mean, we can have the sequence continually start again once the min/max value has been reached. In other words, we can have the sequence reiterate over and over again.

We can do this with the CYCLE argument.

Example

Here’s an example of creating a repeating sequence:

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

Here I set a low maximum value of 3 in order to make it easier to demonstrate the repeating nature of the sequence.

In order to demonstrate this sequence, we can incorporate it into the result set of a query that returns multiple rows from a database table:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

CustomerGroup  CustomerName      
-------------  ------------------
1              Palm Pantry       
2              Tall Poppy        
3              Crazy Critters    
1              Oops Media        
2              Strange Names Inc.

5 row(s) returned

We can see that the sequence reached its MAXVALUE and then started again at the MINVALUE.

Let’s run the query again:

SELECT 
    NEXT VALUE FOR Sequence1 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

CustomerGroup  CustomerName      
-------------  ------------------
3              Palm Pantry       
1              Tall Poppy        
2              Crazy Critters    
3              Oops Media        
1              Strange Names Inc.

5 row(s) returned

Repeating Sequences Start at the MINVALUE/MAXVALUE

Bear in mind that when the CYCLE argument results in the sequence repeating, the count starts at the MINVALUE for ascending sequences and MAXVALUE for descending sequences. My point is, it doesn’t use the START WITH value to determine where to restart.

To demonstrate this, let’s create another sequence that starts at 2 instead of 1:

CREATE SEQUENCE Sequence2
    START WITH 2
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 3
    CYCLE;

Now let’s incorporate that sequence into the result set of a query that returns multiple rows from a database table:

SELECT 
    NEXT VALUE FOR Sequence2 AS CustomerGroup,
    CustomerName
FROM Customers;

Result:

CustomerGroup  CustomerName      
-------------  ------------------
2              Palm Pantry       
3              Tall Poppy        
1              Crazy Critters    
2              Oops Media        
3              Strange Names Inc.

5 row(s) returned

By default, sequences continue on from where they left off, regardless of where its being used. For example, if the above sequence got to 2, the next time we used it, it will start at 3, before cycling back to 1, then 2, and so on. This would be the case whether we used it in the same query, a different query, inserted data into a database, or whatever. It would still start off from where we left off. That is of course, unless we reset the sequence. Resetting the sequence would allow us to dictate the number it starts with the next time we use it.

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. This technique doesn’t involve a sequence object, but it can be useful in many scenarios.

See How ROW_NUMBER() Works in SQL Server for an example.