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.