The series so far:
- Introduction to SQL Server sequence objects
- Using SQL Server sequence objects
- How to return multiple sequence numbers with sp_sequence_get_range
- How to replace an identity column with a sequence number
A sequence object is an object that can be used to generate a sequence of integer numbers based on starting and increment values. An identity column is similar to a sequence, but the sequences object has some additional features; for example, it can be used across multiple tables. The sequence object was reintroduced with SQL Server 2012. This article will explore the basics of creating and using a sequence object, while part 2 of this series will explore the more advanced features of the sequence object.
What is a sequence object?
As the name implies, a sequence object is an object that creates a series of sequence numbers. The object is created with a set of sequencing criteria and is bound to a schema. A sequence object is defined as a numeric data type that generates sequence numbers that fall within the boundaries of the defined data type. The big difference between an identity column and a sequence object is that sequence numbers are generated with application code outside the scope of a transaction. Sequence numbers are generated by using the NEXT VALUE FOR
function. Because sequence numbers are generated with code, the generated values can be used across multiple tables or columns in a single table instead of when rows are inserted into a table. Another difference is that they can be automatically recycled based on specifications defined with the sequence object.
There are many different reasons for using a sequence object to generate sequence numbers. Here is a partial list of reasons why a sequence object might be useful:
- Requirements call for more than one column in a table to be populated with a generated sequence number.
- There is a need to keep sequence numbers synchronized across multiple tables.
- An application requires the sequence number value to be obtained before a row is inserted into a table.
- Need to have sequence numbers automatically recycled based on minimum and maximum values.
- Business rules require a sequence object criterion to be changed over time.
- Requires using a sequence number value to be stored in data types other than numeric.
- Need to have a nullable column but yet still be populated with sequence numbers.
- Want multiple sequence numbers to be acquired with code at the same time.
To better understand the basics of creating and using sequence objects, let me provide a few examples.
Creating a sequence object
Before you can start generating sequence numbers, a sequence object needs to be defined. A sequence object is created using a CREATE SEQUENCE
statement. Below is syntax for this statement, as found in the Microsoft documentation:
1 2 3 4 5 6 7 8 9 |
CREATE SEQUENCE [schema_name . ] sequence_name [ AS [ built_in_integer_type | user-defined_integer_type ] ] [ START WITH <constant> ] [ INCREMENT BY <constant> ] [ { MINVALUE [ <constant> ] } | { NO MINVALUE } ] [ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ] [ CYCLE | { NO CYCLE } ] [ { CACHE [ <constant> ] } | { NO CACHE } [ ; ] |
Refer to the Microsoft documentation for a complete explanation of each of the parameters.
A sequence object has a few more options for generating a sequence number than an identity column. Most notable are the CYCLE
and CACHE
options. The CYCLE
option allows for sequence numbers to be rolled over after reaching a maximum or minimum value. Whereas the CACHE
option improves performance when retrieving sequence numbers.
To explore using a sequence object, start by creating a sequence object. The sequence object will be used to track complaints, where each complaint has a different integer value assigned. To create this sequence object, run the TSQL code in Listing 1.
1 2 3 4 5 6 7 |
USE tempdb; GO CREATE SEQUENCE ComplaintNumber AS INT START WITH 0 INCREMENT BY 1; GO |
Listing 1: TSQL to create a simple sequence object
The code in Listing 1 creates a sequence object named ComplaintNumber. This sequence object is defined as an integer data type and will create sequence numbers starting at 0 and increment the sequence number generated by 1 each time this sequence object is used. Since I didn’t specify MINVALUE
or the MAXVALUE
, this schema object has no minimum or maximum value. But in reality, when a sequence object is created without minimum or maximum value, the database engine assigns those values based on the range of numbers supported by the sequence object’s defined data type.
NEXT VALUE FOR function
SQL Server provides the NEXT
VALUE
FOR
function, so TSQL code can be written to retrieve sequence number values from a sequence object. The numeric values returned by this function depend on the type of statement in which this function is used. When this function is used in a SELECT
statement, a new sequence number value is generated for every row in the result set produced by the SELECT
statement. A different value will be generated for each row inserted using an INSERT
statement. For an UPDATE
statement, this function will generate a new value for each row updated. For procedural statements, like DECLARE
and SET
, a new value is generated for each statement.
The NEXT
VALUE
FOR
function retrieves the next sequence number for a given sequence object. Once a value is returned, the metadata to track the last sequence number used is updated. If a sequence number is retrieved and not used in an INSERT
or UPDATE
statement, that sequence number will be lost. Therefore if you don’t want missing sequence numbers, make sure you don’t select sequence numbers without using them. To explore how the NEXT
VALUE
FOR
function works, here are some examples.
Using NEXT VALUE FOR function in an INSERT Statement
Before showing how to use this function in an INSERT
statement, you will need to create a table in which to insert rows. The example uses the ComplaintNumber sequence object created in Listing 1, so create a table named Complaint to capture different complaints. The code in Listing 2 creates this table.
1 2 3 4 5 6 7 |
USE tempdb; GO CREATE TABLE Complaint ( ComplaintID INT, ComplaintDescription VARCHAR (1000), ComplaintDate DATETIME); GO |
Listing 2: Creating Complaint table
For each new complaint, a row will be added to this table. To create the first complaint record, run the code in Listing 3.
1 2 3 4 5 6 7 8 |
USE tempdb; GO INSERT INTO dbo.Complaint (ComplaintID, ComplaintDescription, ComplaintDate) VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 'First Complaint',getdate()); SELECT * FROM dbo.Complaint; |
Listing 3: Populating first Complaint row using ComplaintNumber sequence object
The code in Listing 3 used the NEXT
VALUE
FOR
function in the VALUES
clause of the INSERT
statement to retrieve the next sequence number available from the ComplaintName object. Report 1 shows the output from Listing 3.
Report 1: Output when the code in Listing 3 is run
By looking at the output in Report 1, you can see the first ComplaintID
has a value of 0. This number happens to be the same as the START
WITH
value specified in the CREATE
SEQUENCE
statement found in Listing 1. The first time a sequence object is referenced, using the NEXT
VALUE
FOR
function, the START
WITH
value associated with the CREATE
SEQUENCE
statement will be the sequence number returned. A new sequence number will be generated for all subsequent NEXT
VALUE FOR
calls based on the last sequence number generated and the INCREMENT
value associated with the sequence object referenced.
Using the NEXT VALUE FOR function in a SELECT statement
When the NEXT
VALUE
FOR
clause is used in a SELECT
statement, the function will produce a different sequence number for each row returned. In Listing 4, a sub-query is used to produce a recordset that contains two rows. The NEXT
VALUE
FOR
function is used in the outer SELECT
statement to feed the two row result set into an INSERT
statement.
Listing 4: Code to populate two more rows in the Complaint table.
1 2 3 4 5 6 7 8 9 |
USE tempdb; GO INSERT INTO dbo.Complaint SELECT NEXT VALUE FOR ComplaintNumber, 'Complaint ' + Number, getdate() FROM (SELECT * FROM (SELECT '1' AS Number UNION SELECT '2') AS A) AS B; SELECT * FROM Complaint; GO |
When the code in Listing 4 is run, the output in Report 2 is displayed.
Report 2: Output when running the code in Listing 4.
By reviewing the output in Report 2, you can see there are now three rows in the Complaint table. The two new rows added have ComplaintID
values 1 and 2. This example shows two things. First, it shows that it produces two values when passing two rows to the NEXT
VALUE
FOR
function. Secondly, it shows how the INCREMENT
value was used to increase the sequence number by 1 for each new sequence number generated.
Using the NEXT VALUE FOR in an UPDATE statement
When an UPDATE
statement is used in conjunction with the NEXT
VALUE
FOR
function, every row updated will assign a different sequence number. This can be seen by running the code in Listing 5.
Listing 5: Using NEXT VALUE FOR function with UPDATE statement
1 2 3 4 5 6 7 |
USE tempdb; GO UPDATE Complaint SET ComplaintID = NEXT VALUE FOR ComplaintNumber, ComplaintDate = getdate(); SELECT * FROM Complaint; GO |
Report 3 contains the output, when Listing 5 is run.
Report 3: Output from Listing 5
Report 3 shows that the three existing rows in the Complaint
table were updated with new ComplaintID’s
. This example shows how each row updated got a different generated sequence number to update the ComplaintID
column.
Using a sequence object as a constraint
The examples so far have shown how to use the sequence object and the NEXT
VALUE
FOR
function to generate sequence numbers within SELECT
, INSERT
, and UPDATE
statements. Another was to use the NEXT
VALUE
FOR
function is in a constraint. By using the NEXT
VALUE
FOR
function in a constraint, you can automatically populate table column as rows are inserted, similar to how an identity column works. The code in Listing 6 drops and recreates the Complaint table and uses the ComplaintNumber
sequence object to define a constraint on the ComplaintID
column and then inserts some rows into the recreated table.
Listing 6: Using a sequence object in constraint
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE tempdb; GO DROP TABLE Complaint; GO CREATE TABLE Complaint ( ComplaintID INT NOT NULL CONSTRAINT [DF_ComplaintID] DEFAULT (NEXT VALUE FOR ComplaintNumber), ComplaintDescription VARCHAR (1000), ComplaintDate DATETIME); GO INSERT INTO Complaint (ComplaintDescription, ComplaintDate) VALUES ('Complaint 1',getdate()), ('Complaint 2',getdate()); SELECT * FROM Complaint; GO |
Report 4 is generated by the SELECT statement in Listing 6.
Report 4: Output from the SELECT statement in Listing 6
This example shows how a constraint can automatically populate a table column with a sequence number value. If you want to use a sequence object to produce sequence numbers when rows are inserted into tables without writing application code, then using a constraint is the way to go.
Altering a sequence number
One of the advantages of using a sequence object over an identity column is that a sequence object can be altered. In contrast, an identity column specifications can’t be altered once it is created. Being able to alter an existing sequence object is a great thing, especially when business requirements change, and there is a lot of application code that already uses a sequence object.
For a business requirement that requires a sequence object to be altered, assume management has changed their mind on the numbering scheme for complaints. Now they want all new complaints to start at 10000, with every complaint after that to be increment by 10. To meet these new requirements, the ComplaintNumber
sequence object will be altered using the code in Listing 7.
Listing 7: Altering Complaint sequence object
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
USE tempdb; GO ALTER SEQUENCE dbo.ComplaintNumber RESTART WITH 10000 INCREMENT BY 10; GO INSERT INTO dbo.Complaint (ComplaintID, ComplaintDescription, ComplaintDate) VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 'First complaint with new numbering scheme',getdate()); INSERT INTO dbo.Complaint (ComplaintID, ComplaintDescription, ComplaintDate) VALUES (NEXT VALUE FOR dbo.ComplaintNumber, 'Second complaint with new numbering scheme',getdate()); GO SELECT * FROM dbo.Complaint; GO |
When the code in Listing 7 is run t,he output in Report 5 is created.
Report 5: Output created when code in Listing 7 is executed.
Being able to easily change a sequence object with an ALTER
statement provides you a quick method to change the sequence object specification without changing any application code or to drop and recreate the table that uses a sequence object.
Sequence object Information
If you need to determine which sequence objects are defined in a database you have a few ways to do that. The first way is to use Object Explorer. To show a list of sequence objects in a database expand the Programmability folder, then expand the Sequences folder as shown in Figure 1.
.
Figure 1: Expanded Sequence Folder
By reviewing Figure 1, you can see there is one sequence object that has been defined in tempdb. To review the actual specifications for this sequence object just double-click it.
Figure 2 displays the ComplaintNumber object properties.
Figure 2: Sequence object specifications for ComplaintNumber
Figure 2 shows all the specifications for the ComplaintNumber sequence object. You can also use Object Explore to modify a sequence object. I’ll leave it up to you to test that out.
Another way to display the sequence object information is to use the system view sys.sequences
. This view displays one row of information for each sequence number object defined in a database. The code in Listing 5 uses the sys.sequences
view to display some of the metadata columns for the sequence objects I have defined in tempdb.
Listing 8: Using the sys.sequences system view
1 2 3 4 |
USE tempdb; GO SELECT name, create_date, start_value, increment FROM sys.sequences; |
Report 6 shows the results when Listing 8 is run.
Report 6: Sequence objects defined in tempdb
I only showed a few columns that are available when using the sys.sequences
view in Report 6. For a complete list of available columns review the Microsoft documentation.
Dropping a sequence object
Once a sequence object has been created, you might find the need to remove it from the database. The DROP
SEQUENCE
command is used to drop a sequence object. To remove the sequence object from a database, you need to remove all the objects that reference a sequence object, and then you can drop the sequence object. If all the objects that reference a sequence object are not dropped prior to dropping the sequence number, an error will occur.
In order to demonstrate dropping my ComplaintNumber sequence object, I must first drop the DF_Constraint that I created in Listing 6. Listing 9 contains the code to drop the sequence object along with dropping the one constraint.
Listing 9: Dropping a sequence objcct
1 2 3 4 5 6 |
USE tempdb; GO ALTER TABLE Complaint DROP CONSTRAINT DF_ComplaintID; GO DROP SEQUENCE ComplaintNumber; GO |
Rollback issues
Each time the NEXT
VALUE
FOR
function is called, it returns the next sequence number. This process of requesting the next sequence is done outside the scope of the current transaction. Since the metadata to track the last sequence number is done outside the code of a transaction, that means when a transaction fails, is not committed, and/or is rolled back, the last sequence number stored in the metadata will not be rolled back. Rollbacks are a reason why sequence numbers might go missing.
Limitations
As with most SQL Server features, there are limitations. Before using sequence objects, you need to be aware of the sequence object and NEXT
VALUE
FOR
function limitations. Below is a partial list of some of the key limitations:
A column populated with a sequence number value is not protected from being updated, like an identity column. Suppose you need to make sure your sequence number populated columns are not updated. In that case, you should consider placing an update trigger on columns populated by a sequence number to make sure they can’t be updated.
Sequence numbers may not be unique. For example, when a sequence object is recycled, it may reuse sequence numbers that have been generated before. If you want to make sure your columns populated with a sequence number contain unique values, then a unique index or constraint should be added to your columns.
If the NEXT
VALUE
FOR
function references the same sequence object multiple times in the same SELECT
statement, then the same value will be returned for each reference.
The NEXT VALUE FOR
function is not allowed in check constraints, default objects (deprecated), computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables, or return statements. It can be used in default constraints, as shown in the example in this article.
NEXT VALUE FOR
function cannot be used in statements that contain TOP
or OFFSET
clauses or when the ROWCOUNT
option is set.
Cannot use the NEXT
VALUE
FOR
clause in a WHERE
statement.
For a complete list of limitations for sequence objects and the NEXT VALUE FOR function, refer to the SQL Server Microsoft documentation.
Exploring the sequence number object
Prior to SQL Server 2012, the only automated method SQL Server provided to populate a column with a sequential number was to make that column an identity column. Since the rollout of SQL Server 2012, a sequence object can also be used to generate sequence to populated column values in one or more tables. Sequence number values are obtained with TSQL code. Business requirements determine whether or not an identity column or a sequence object should be used to generate a series of generated numbers. Next time you need a series of generated numbers for a table column, determine if the sequence object might be more appropriate than using an identity column. The next article dives into the properties of the sequence object.
If you liked this article, you might also like SQL Server identity column – Simple Talk (red-gate.com).
Load comments