SQL Server supports sequences. Sequences enable us to create our own sequence of numeric values.
Sequences are similar to identity columns in that they generate a unique value that can be used to identify a column. However, sequences are created completely independently of any table.
When creating a sequence, we can specify how big each increment should be, and we can also make it a decrementing sequence if required.
We can also create sequences that cycle through a range. For example, we could have multiple rows with each row being assigned a number between 1 and 4. If there are more than four rows, then the sequence will start again after the fourth row.
Once we’ve created a sequence, we can then go ahead and generate sequence numbers from that sequence. The numbers generated by a sequence can be used across multiple tables or none at all if we want. Sequences can be useful for inserting unique IDs into an ID column, but we can also generate and retrieve a sequence number without inserting any rows into a table. We can use sequences to display our own unique number in the results of a SELECT query. But we can also generate a sequence number without querying a table or going anywhere near a table.
Here are examples of using T-SQL to create sequences in SQL Server.
Basic Sequence
Here’s an example of creating a simple sequence:
CREATE SEQUENCE Sequence1
START WITH 1
INCREMENT BY 1;This sequence starts at 1 and increments by 1.
After running that statement, we can now use the NEXT VALUE FOR statement to generate a sequence number and return it:
SELECT NEXT VALUE FOR Sequence1;Result:
1
We get 1 because we specified START WITH 1 when we created the sequence.
If we run NEXT VALUE FOR again, we’ll get the next number:
SELECT NEXT VALUE FOR Sequence1;Result:
2
The reason we get 2 is because we specified INCREMENT BY 1 when we created the sequence.
It’s possible to create a sequence without specifying the START WITH and INCREMENT arguments. When we do this, SQL Server uses the default values for those arguments (and other arguments). See Create a Sequence with the Default Values later in this article for an example.
Larger Increments
We can have larger increments by specifying a larger increment in our INCREMENT BY argument.
Here’s an example of creating a sequence with a larger increment. While we’re at it, let’s also start the sequence at a different number:
CREATE SEQUENCE Sequence2
START WITH 100
INCREMENT BY 10;Now let’s generate the first number:
SELECT NEXT VALUE FOR Sequence2;Result:
100
As expected.
Now let’s generate the next number in the sequence:
SELECT NEXT VALUE FOR Sequence2;Result:
110
It has incremented by 10 as expected.
Decrements
We can set the INCREMENT argument to a negative value in order to have a decrementing sequence:
CREATE SEQUENCE Sequence3
START WITH 1
INCREMENT BY -10;
SELECT NEXT VALUE FOR Sequence3;Result:
1
Now let’s generate the next number in the sequence:
SELECT NEXT VALUE FOR Sequence3;Result:
-9
It has decremented the start value by 10 as expected.
Specify a Minimum and/or Maximum Value
We can use the MINVALUE and MAXVALUE arguments to specify a minimum and maximum value for the sequence:
CREATE SEQUENCE Sequence4
START WITH 100
INCREMENT BY 10
MINVALUE 100
MAXVALUE 200;Once the value gets to the MAXVALUE, any subsequent calls to NEXT VALUE FOR for this sequence will result in an error (unless the CYCLE argument is present).
Here’s an example of the error:
Msg 11728, Level 16, State 1, Line 1 The sequence object 'Sequence4' has reached its minimum or maximum value. Restart the sequence object to allow new values to be generated.
The same concept applies to MINVALUE, except that it applies when the minimum value is exceeded.
We have several options for overcoming this error including, changing MAXVALUE (or MINVALUE if decrementing), resetting the sequence, or using the CYCLE argument (below).
The CYCLE Argument
We can use the CYCLE argument to overcome the error produced in the previous example (although it won’t be suitable in many cases). This argument specifies whether the sequence object should restart from the minimum value (or maximum for descending sequence objects). In other words, instead of stopping at the maximum value, it starts again from the minimum value.
CREATE SEQUENCE Sequence5
START WITH 1
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 Sequence5 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 Sequence5 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
Bear in mind that when the CYCLE argument results in the count starting again, the count starts at the MINVALUE (not the START WITH value).
To demonstrate this, let’s create another sequence that starts at 2 instead of 1:
CREATE SEQUENCE Sequence6
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 Sequence6 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
Caching
By default, the sequence is cached. This increases performance for applications that use sequence objects by minimising the number of disk IOs that are required to generate sequence numbers.
However, we can also specify a cache size by using the CACHE argument followed by the desired cache size. If we don’t specify a size, the database engine will determine the cache size.
We can also specify NO CACHE if desired.
Create a Sequence with the Default Values
It’s possible to create a sequence without specifying any arguments. In this case, the default values will be used for all properties.
Here’s an example:
CREATE SEQUENCE Sequence7;That created a sequence called Sequence7 with default values. We can now use the sys.sequences system catalog view to check the properties of the sequence:
SELECT
TYPE_NAME(system_type_id) AS "Type",
start_value,
increment,
minimum_value,
maximum_value,
is_cycling,
is_cached,
current_value
FROM sys.sequences
WHERE name = 'Sequence7';Result:
Type start_value increment minimum_value maximum_value is_cycling is_cached current_value ------ -------------------- --------- -------------------- ------------------- ---------- --------- -------------------- bigint -9223372036854775808 1 -9223372036854775808 9223372036854775807 false true -9223372036854775803
Specify the Data Type
By default, sequence numbers use the bigint data type. However, we can specify any integer type (including user-defined types).
Here’s an example of specifying a different data type:
CREATE SEQUENCE Sequence8 AS tinyint;Now let’s check the sequence with sys.sequences:
SELECT
TYPE_NAME(system_type_id) AS "Type",
start_value,
increment,
minimum_value,
maximum_value,
current_value
FROM sys.sequences
WHERE name = 'Sequence8';Result:
Type start_value increment minimum_value maximum_value current_value ------- ----------- --------- ------------- ------------- ------------- tinyint 0 1 0 255 0
In this case, we specified a tinyint type, and we can see how this affects the default values of the various properties.