For years SQL Server has supported the IDENTITY property for automatically generating a unique number that is incremented from one value to another based on an increment value. With the introduction of SQL Server 2012, Microsoft introduced a new method to generate a sequence number, using a Sequence Object. This new object can be used to generate unique numbers that can be automatically incremented based on an increment value, and much, much, more. In this article I will be discussing the different features of the sequence object and how you can use it to generate sequence numbers.
What is a Sequence Object?
The sequence object is a schema_bound object that generates sequence numbers based on a specification. It is very similar to the identify column but has a number of different charactistics that I will explain further.
Sequence numbers generated by a sequence object have an increment value like an identity column. This increment value can be used to increase or decrease the current sequence number. Additionally the sequence number values can be recycled. This is something that can’t be done with an identity column
Identity column values can only be used by a single table and in that table only one column can be an identity column; whereas the sequence object can generate numbers that can be used across multiple tables in a database. Therefore if you have a criterion to keep a sequence number unique across multiple tables then you can easily do this with the sequence object. Additionally you can use the sequence object to populate multiple columns on a single table. Once again these are things that an identity column does not support.
The sequence object does not enforce uniqueness, like an identity column. This of course is by design, since you can recycle numbers, as well as use a generated number in multiple tables, or multiple columns in the same table. If you do need to create unique numbers across a group of tables then a trigger will need to be built to enforce this requirement.
Understanding and Using Sequence Objects
As already stated, the Sequence Object is similar to the Identity column but it has some distinct differences. You need to understand those differences so you can appropriately determine if the sequence object makes sense to use for your situation. Depending on your business requirements the SQL Server sequence object might be a great asset, and in other cases the identity column might do just fine.
One of the features the sequence object allows you to do is recycle sequence numbers. When you create a sequence object you can use the CYCLE or NOCYCLE option to tell SQL Server whether you want to recycle sequence numbers, or not to recycle numbers. If you pick NOCYCLE, then SQL Server will keep coming up with new sequence numbers based on the INCREMENT BY value. On the other hand if you create your Sequence Object using the CYCLE option then SQL Server will recycle values based on the MINVALUE and MAXVALUE values specified when you create your Sequence Object.
SQL Server has two difference processes it uses to maintain the value of the current sequence number for a sequence object. It can either maintain the current value in memory along with using database metadata, or it can maintain the sequence number value solely in the metadata. Having SQL Server maintain the current sequence value in memory is more efficient then having SQL Server use only the database metadata to identify the current value. But there are tradeoffs.
In order to have SQL Server maintain the current value in memory you need to specify the CACHE options when creating your sequence object. When you specify the CACHE option you also provide a constant that tells SQL Server how often it needs to update the SQL Server metadata to maintain the sequence number. By using the CACHE option SQL Server performs less I/O to the database metadata. The way this works is SQL Server will write to the metadata every X times a sequence number has been requested, where X represents the constant that was supplied with the CACHE option when creating the Sequence Object. The rest of the time SQL Server just maintains the sequence number in memory.
When using the CACHE option SQL Server updates the metadata with the last sequence number it generated prior to requesting a new set of sequence numbers. Because SQL Server uses this process of updating the metadata with a value higher than the current sequence number value it is possible that SQL Server will skip values if SQL Server should crash. This is because when SQL Server crashes the current value which is only held in memory is lost. Therefore when SQL Server starts back up it will read the last value stored in metadata as a starting point for generating new sequence numbers. If SQL Server is shut down normally SQL Server will not lose track of the current value. This is because when SQL Server is gracefully shutdown it writes the cache value for the current sequence number to database metadata prior to being shut down. If you are concerned about the possibility of gaps in your sequence numbers then do not use the CACHE option when creating your sequence object.
Here is the syntax as documented in Books Online for creating a sequence object:
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 Books online documentation to get a complete explanation of each parameter.
Uses for the Sequence Object
There are many different reasons for using a sequence object to generate a sequence number over an identity column. Here is a short list of some reasons why you might consider using a sequence object:
- Adding more than one sequential number on a single table
- When you need to keep sequence numbers synchronized across multiple tables
- Needing to know the value of the next sequence number before you actually insert a row into a table
- Need to recycle numbers based on some business rule
- You need to periodically change the increment value the sequence object uses to generate the next sequence number
- You need to store a sequence number in a format other than an integer value
- You want your sequence number column to be nullable
This is only a partial list. I’m sure there are other options you might think of where a sequence object would work better than an identity column for generating sequence numbers in your environment.
Example of Using a Sequence Number to Populate a Column
To show you how to use a sequence object to populate a column I will be creating a sequence object that will start counting at 1 and increment by 1. To create my sequence object I will run the following code:
USE tempdb; GO CREATE SEQUENCE dbo.IncrementBy1 START WITH 1 INCREMENT BY 1; GO
As you can see it is not very hard to create a sequence object. Now that I’ve created this sequence object let me show you how to use it to populate a column in a table by running the following code:
USE tempdb; CREATE TABLE dbo.SeqNum1 (SeqNum varchar(10)); INSERT INTO dbo.SeqNum1 (SeqNum) VALUES ('S' + CAST(NEXT VALUE FOR dbo.IncrementBy1 AS VARCHAR(3))), ('S' + CAST(NEXT VALUE FOR dbo.IncrementBy1 AS VARCHAR(3))); SELECT SeqNum FROM dbo.SeqNum1;
When I run the code above the following output is produced:
SeqNum ---------- S1 S2
In my code above, I did a few different things to generate the value that I used to populate my SeqNum column. The first thing I did was use the code “NEXT VALUE FOR dbo.IncrementBy1” to obtain the next sequence number from the dbo.IncrementBy1 sequence object. The second thing I did was CAST the sequence number I got as a VARCHAR(3) value. I did this so I could concatenate an “S” with the sequence number to generate the value I wanted to use to populate the SeqNum column. This is something you can’t do with an identity column. By reviewing the output you can see the first sequence number generated was a 1 and the second was a 2.
This example showed how to retrieve the next sequence value using the “NEXT VALUE FOR” option and how you can then manipulate the returned sequence number prior to using it to store a value in a table column.
Examples of Recycling the Sequence Number
If I had a business requirement that stated I needed to recycle my sequence numbers after every 10 values could I do this? Yes, I could with the sequence object. In order to implement this requirement I will need to use the CYCLE option when creating my sequence object. This isn’t something I would be able to do with an Identity column.
Let me add to my requirements and say I need to have my sequence number cycle through numbers from 1 through 10 and then recycle back to 1 after my sequence number has gotten to 10. I can do that by using the following code:
USE tempdb;
GO
CREATE SEQUENCE dbo.RecycleAfter10
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 10
CYCLE;
— Demo how Sequence Number recycles
DECLARE @I INT = 0
WHILE @I < 40
BEGIN
SET @I += 1;
SELECT @I As ValueOfI, NEXT VALUE FOR dbo.RecycleAfter10 SeqNum;
END
If you run this code you will find that the SeqNum value returned in the SELECT statement starts at 1 the first time the SELECT statement is executed. I used the “START WITH” option to make this happen. Then the second time the SELECT statement is executed the SeqNum value is 2. Then eventually when the SELECT statement is executed the 11th time the sequence object knows that the sequence number has reached its max and needs to roll over. On the 11th execution the sequence object will return a value of 1 for the SeqNum. The CYCLE option tells that SQL Server that the sequence object needs to recycle sequence numbers. The MAXVALUE controls the maximum sequence number to generate, which in my example is 10. The MINVALUE identifies the value at which the sequence number will recycle when the sequence numbers roll over, which in my case is 1.
Resetting the Sequence Number
Suppose you have a business requirement to have the sequence number start over at the MINVALUE on the first day of the month. Can this be done? The answer is yes but you can’t do it solely by the options associated with the sequence option. In addition to the sequence object you will also need to write a routine that will reset the starting number of the sequence object on the first day of the month.
In order to implement my requirements I will need to execute the ALTER SEQUENCE command with the RESTART WITH option, to make my sequence object restart the generation of sequence number at 1. To show you how this can be done first I have created the following SQL sequence object:
CREATE SEQUENCE dbo.RecycleMonthly START WITH 1 INCREMENT BY 1;
Then to implement my requirement I would need to execute the following ALTER statement on the first day of the month:
ALTER SEQUENCE dbo.RecycleMonthly RESTART WITH 1;
This ALTER statement would tell my dbo.RecycleMontly sequence object that the next sequence number requested should be 1. You can verify this method meets my requirements by running the following code:
USE tempdb; GO CREATE SEQUENCE dbo.RecycleMonthly START WITH 1 INCREMENT BY 1; -- Show the first 5 sequence numbers DECLARE @I INT = 0 WHILE @I < 5 BEGIN SET @I += 1; SELECT @I As ValueOfI, NEXT VALUE FOR dbo.RecycleMonthly SeqNum; END -- Reset Sequence to restart at 1 ALTER SEQUENCE dbo.RecycleMonthly RESTART WITH 1; SELECT @I As ValueOfI, NEXT VALUE FOR dbo.RecycleMonthly SeqNum;
If you run this code you will see that I first selected 5 different sequence numbers that started at 1 and were incremented by one for each new sequence number. Then after I ran the “ALTER SEQUENCE” statement, the next time I requested a sequence number a value of 1 was returned.
You can also use the ALTER SEQUENCE statement to alter any of the sequence number properties.
How Sequence Objects are Affected by Transaction Rollback
Another reason that sequence numbers might have missing values is because of transaction rollback. Once you request a sequence number from a sequence object that new value is committed. Meaning when you rollback a transaction in which the sequence number was requested, the sequence number will not be rolled back. To show you this, review the following code:
USE tempdb;
GO
CREATE SEQUENCE dbo.RollbackTest
START WITH 1
INCREMENT BY 1 ;
GO
USE tempdb;
CREATE TABLE dbo.SeqNum2 (SeqNum int);
BEGIN TRANSACTION;
INSERT INTO dbo.SeqNum2 (SeqNum)
VALUES (NEXT VALUE FOR dbo.RollbackTest);
ROLLBACK TRANSACTION;
INSERT INTO dbo.SeqNum2 (SeqNum)
VALUES (NEXT VALUE FOR dbo.RollbackTest);
SELECT * FROM dbo.SeqNum2;
In this code I created a sequence object named dbo.RollbackTest and then created a table named dbo.SeqNum2. After that I started a transaction and inserted one row into my dbo.SeqNum2 table and then I rolled back my INSERT statement. Following that I inserted a new row into my dbo.SeqNum2 table and then used a SELECT statement to review what was in my dbo.SeqNum2 table. When that SELECT statement was run the following output was produced:
SeqNum ----------- 2
By reviewing the output you can see that when I rolled back that transaction that the Sequence number obtained by the second SELECT statement retrieved the value of 2. You can also tell that my transaction did in fact rollback because I only have a single row in my SeqNum2 table.
Viewing and Returning Metadata about Sequence Number
In you want to return information about the different sequence objects that you have created on a database there are a have a number of ways to do this. First you can use SQL Server Management Studio to display all of your sequence objects in a database. To do that first expand the database by clicking on the + sign next to the database (shown by the red arrow in the screen shot below). Then expand the “Programmability” item (shown by the yellow arrow in the screen shot below). Then expand the “Sequence” menu item (show by the blue arrow in the screen shot below). This will display all the different sequence objects you have in your database. Then if you want to show the properties of a particular sequence object just right click one of the sequence objects for which you want to show the properties, like I did by right clicking on the “dbo.RecycleMonthly” sequence object (shown by the green arrow below).
Right click on the sequence object
When I did this the “Sequence properties” window for my Sequence Number was displayed. Note that on this screen you can see all the different properties of this sequence object, including the current value of the sequence object. Note this same information can be displayed using the “sys.sequence “ catalog view by running the following code:
SELECT * FROM sys.sequences WHERE name = 'RecycleMonthly' and schema_name(schema_id) = 'dbo';
Using the Sequence Number
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. Now with SQL Server 2012 you can use the sequence object to generate sequence numbers that you can use to populate a column in a single table, or it can be used to populate multiple tables. Depending on your business requirements you will determine whether or not to use an identity column or the sequence object to generate your sequence numbers. Next time you need a sequence number for a table, determine if the sequence object might be more appropriate than using an Identity column.
