Microsoft SQL Server: The Sequencing Solution
The new Microsoft SQL Server platform features sequencing, which ought to be a huge help for SQL administrators everywhere.
Denny Cherry
There’s a welcome new addition to Microsoft SQL Server, present in the first public beta release of SQL Server “Denali.” Many members of the SQL Server community have been requesting sequencing features for years, and they’re finally going to be available.
Sequences are conceptually like the IDENTITY property, which you can place on a column of a table. The big difference between IDENTITY and sequences is that sequences aren’t bound to a specific table. In fact, you don’t have to use a table at all. A single database can include multiple sequences, limited only by the total number of objects within the database.
Creating a Sequence
When you create a sequence, you don’t have to specify how or where the database will use that sequence. The sequence is a totally independent object from other objects within the database. There are several options you will need to specify, however.
The first is the MINVALUE option, which is the lowest value the sequence can issue. The next is the MAXVALUE option, which is the highest value the sequence can issue. The next option is the START WITH option, which is the first value it will issue. When the START WITH option is omitted, it will use MINVALUE for the first value. Here are the MINVALUE, MAXVALUE and START WITH options in a basic CREATE SEQUENCE statement:
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 START WITH 1000
IF you don’t wish to specify a MINVALUE or MAXVALUE, you can omit these lines from the CREATE SEQUENCE statement or use the NO MINVALUE and NO MAXVALUE keywords, as shown here:
CREATE SEQUENCE dbo.MySequence AS INT NO MINVALUE NO MAXVALUE START WITH 1000
This is the CREATE SEQUENCE statement without a specified minimum or maximum value. However, you should specify both the MINVALUE and the START WITH value for a couple of different reasons.
First, there’s nothing that says that a sequence must go forward or increase in value with each increment. You may want a sequence that starts at a high number and gets lower each time you add a new number. Another reason both values exist is that a sequence can cycle back to the beginning of the sequence when it runs out of numbers.
You may want to create a sequence with a range from one to 10,000, but starts with 1,000 and when it runs out of numbers it starts over from one. To make the sequence cycle back to the beginning when it runs out of numbers, specify the CYCLE keyword when creating the sequence, as shown in the following code (you can use the NO CYCLE keyword to prevent the sequence from cycling back to the beginning of the sequence):
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 CYCLE
Here’s another look at this type of sequence, using the NO CYCLE keyword:
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 NO CYCLE
As you can see in these examples, the data type the sequence will use is specified after the AS keyword, which is the INT or INTEGER data type. Other data type value options include TINYINT, SMALLINT, BIGINT or any user-defined data type that’s based on acceptable data types.
Besides these whole number data types, you can also use the DECIMAL and NUMERIC data, provided they’re defined with a scale of zero. If there’s no data type specified, the sequence will default to the BIGINT data type, which could cause issues if your table has an INTEGER data type column where the value is being stored.
The next setting, which is one of the most important, is CACHE. The CACHE setting tells SQL Server—when its using the sequence—how many values to load into memory each time it needs new values. The way the CACHE works is the big difference between how sequences and the IDENTITY property work.
With the IDENTITY property, SQL Server maintains a cache of 20 values in memory, which you can’t adjust. The performance problem with the IDENTITY property is that each time you insert a new row into the table and the IDENTITY property issues a value, the fact that this value has been used is written to the SQL Server database. The faster the rows are loaded into the table, the faster SQL Server must write this metadata to the database.
Unlike the IDENTITY property, a sequence only writes to the database’s metadata that the values were issued when they are first put into the cache. For example, if a sequence issued rows in batches of 1,000, when the first batch is issued the value of 1,000 is written to the metadata. When the value of 1,001 is needed, another 1,000 values are loaded into the cache and the value of 2,000 is written to the metadata. This greatly reduces the number of metadata writes and can improve database performance.
The one downside to this is that there will be gaps in the values issued by the sequence every time SQL Server is restarted. When the database shuts down, it doesn’t write the last value actually used to metadata. So if the last value used before shutdown was 1,005 (and we have the same settings as previously described), when the next row is inserted after the database comes back online, the next value used will be 2,000.
This could cause a sequence to run out of numbers much more quickly than an IDENTITY value. It could also cause managers to become upset that values are missing, in which case they’ll need to simply get over it and accept that there will be numbers missing.
If you need SQL Server to use every possible value, configure a cache setting of NO CACHE. This will cause the sequence to work much like the IDENTITY property. However, it will impact the sequence performance due to the additional metadata writes.
To control the amount of numbers to skip, use the INCREMENT BY setting of the sequence. The INCREMENT BY value can be any whole number either positive or negative, although a value of one is probably the most common increment. This uses the INCREMENT BY keyword in the CREATE SEQUENCE statement:
CREATE SEQUENCE dbo.MySequence AS INT MINVALUE 1 MAXVALUE 100000 INCREMENT BY 3 CYCLE
Using a Sequence
There are several different ways to use a sequence. You can assign them as a table’s default value, so the column values are automatically specified when you insert rows into the table. This operates much like a table with the IDENTITY property configured.
The other option is to pull the next value from the sequence from within a stored procedure (or other T/SQL code). That way, you can use it during stored procedure processing (or other T/SQL code).
When specifying which sequence to automatically add as a column value when you add new rows to a table, set the default value of the column to pull the NEXT VALUE from the sequence, as shown here:
CREATE TABLE dbo.YourTable (YourTableId INT NOT NULL, AnotherColumn VARCHAR(10)) GO ALTER TABLE dbo.YourTable ADD DEFAULT NEXT VALUE FOR dbo.MySequence FOR YourTableId GO
This assigns values from a constraint when rows are inserted into a table in the same way as the IDENTITY property.
One of the neat things you can do with a sequence that you can’t do with the IDENTITY property is append values to the front of the value taken from the sequence. If you need to put the letter “A” in front of the values you receive from the sequence, you can easily do this by casting the output from the sequence to a character value. Then append the value to the front, as shown here:
CREATE TABLE dbo.YourTable (YourTableId INT NOT NULL, AnotherColumn VARCHAR(10)) GO ALTER TABLE dbo.YourTable ADD DEFAULT ‘A’ + CAST(NEXT VALUE FOR dbo.MySequence as VARCHAR(10)) FOR YourTableId GO
This assigns values from a constraint when rows are inserted into a table in the same way as the IDENTITY property, but while appending a character value to the beginning of the retrieved value.
Calling values from a sequence in normal T/SQL code (whether from within a stored procedure or not) is done in much the same way. Use the NEXT VALUE syntax and specify the name of the sequence from which you wish to retrieve the next value. This pulls the next available value from a sequence:
DECLARE @YourId INT SET @YourId = NEXT VALUE FOR dbo.MySequence
Another great way to use a sequence is to have it assign row numbers to rows in a row set. Use the NEXT VALUE syntax as part of your select statement, as shown in this example:
SELECT NEXT VALUE FOR dbo.MySequence, * FROM sys.objects
This assigns a value from a sequence to each row in a recordset.
As you can see, this new sequencing feature is a powerful tool. The number and variety of uses for sequences in SQL Server are only limited by your imagination.
.png)
Denny Cherry has more than a decade of experience managing SQL Server. His areas of expertise include system architecture, performance tuning, replication and troubleshooting. He holds several certifications related to SQL Server, including the Microsoft Certified Master for SQL Server 2008. He’s been a Microsoft SQL Server MVP for several years, and has written numerous technical articles and books on SQL Server management and how SQL Server integrates with various other technologies.