SQL Server 2012 T-SQL at a Glance – Sequences

The feature which is the focus of this entry—sequences—is probably one of the most requested features in SQL Server for a long time. The idea is to have an automatic generator of numbers, typically to be used as keys, but one that doesn’t suffer from the many restrictions that the identity column property does. SQL Server 2012 (formerly code-named "Danali"), introduces a sequence object, implemented following standard SQL with a couple of extensions to the standard for enhanced flexibility. In this entry I’m going to provide a brief introduction to the feature to get you started playing with it. Next year I’ll provide a more in-depth coverage as part of my column.

So, what are the limitations of the identity column property that makes it problematic to use in certain circumstances? Here are a few:

The new sequence object doesn’t suffer from the above limitations. Here are its characteristics as they correspond to the items above:

As an example, the following code creates a new integer sequence (default is BIGINT) starting with 1 (default is lowest value in the type, -2147483647 in the case of INT), incrementing by 1, with a cache value of 50:

SET NOCOUNT ON;USE AdventureWorks2008R2;CREATE SEQUENCE dbo.Seq1 AS INT  START WITH 1  INCREMENT BY 1  CACHE 50;

The CACHE option is an extension to the standard for increased performance. It basically means that instead of writing to a system table the last sequence value upon every individual increment, only the last value in each block (of 50 values in this case) is recorded in the system table, and the individual changes are recorded only in memory. This means that if there’s an unordered shutdown of the system (e.g., power failure), at most you will “lose” cache size number of values. With ordered shutdowns of the system you will not lose a thing.

You can also define other attributes: MINVALUE, MAXVALUE and CYCLE, all of which are self-explanatory. As mentioned, you can alter all sequence properties besides the data type using the ALTER SEQUENCE statement.

To generate new sequence values you use the standard expression NEXT VALUE FOR. For example, run the following code to generate three new sequence values:

SELECT NEXT VALUE FOR dbo.Seq1;SELECT NEXT VALUE FOR dbo.Seq1;SELECT NEXT VALUE FOR dbo.Seq1;-----------1-----------2-----------3

Of course, you can assign the result of the expression to a variable or an output parameter, if you like, or, you can embed the expression directly in INSERT statements. As an example, run the following code to create two tables:

CREATE TABLE dbo.T1(  col1 INT NOT NULL,  col2 VARCHAR(50) NOT NULL,  CONSTRAINT PK_T1 PRIMARY KEY(col1));CREATE TABLE dbo.T2(  col1 INT NOT NULL,  col2 VARCHAR(50) NOT NULL,  CONSTRAINT PK_T2 PRIMARY KEY(col1));

Then use the NEXT VALUE FOR expression as part of INSERT statements against the tables:

INSERT INTO dbo.T1(col1, col2) VALUES(NEXT VALUE FOR dbo.Seq1, 'A');INSERT INTO dbo.T2(col1, col2) VALUES(NEXT VALUE FOR dbo.Seq1, 'B');

You can also define default constraints (extension to the standard) that produce new sequence values, like so:

ALTER TABLE dbo.T1  ADD CONSTRAINT DFT_T1_col1 DEFAULT (NEXT VALUE FOR dbo.Seq1) FOR col1;ALTER TABLE dbo.T2  ADD CONSTRAINT DFT_T2_col1 DEFAULT (NEXT VALUE FOR dbo.Seq1) FOR col1;

Run the following INSERT statements allowing the default constraints to generate the new values:

INSERT INTO dbo.T1(col2) VALUES('C');INSERT INTO dbo.T2(col2) VALUES('D');SELECT * FROM dbo.T1;SELECT * FROM dbo.T2;col1        col2----------- --------------------------------------------------4           A6           Ccol1        col2----------- --------------------------------------------------5           B7           D

You can also use the NEXT VALUE FOR expression as part of a multi-row SELECT statement, and even control the order in which the values are generated using an OVER clause (extension to the standard), similar to the one used with window functions. For example, the following INSERT statement uses a query that filters the five most expensive products based on ListPrice descending ordering, and generates sequence values for those five orders based on ListPrice ascending ordering:

INSERT INTO dbo.T1(col1, col2)  SELECT NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY ListPrice ASC), Name  FROM (SELECT Name, ListPrice        FROM Production.Product        ORDER BY ListPrice DESC        OFFSET 0 ROWS FETCH FIRST 5 ROWS ONLY) AS D;SELECT * FROM dbo.T1;col1        col2----------- --------------------------------------------------4           A6           C8           Road-150 Red, 629           Road-150 Red, 4410          Road-150 Red, 4811          Road-150 Red, 5212          Road-150 Red, 56

Note that like with the identity column property, if new sequence values are generated in a transaction that is rolled back, those sequence values are lost and you will end up with gaps.

When you’re done, run the following code for cleanup:

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;IF OBJECT_ID('dbo.T2', 'U') IS NOT NULL DROP TABLE dbo.T2;IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;

This was just a glimpse to the new sequence object in SQL Server Denali to help you get started. There’s a lot more to say about sequences and I will do so next year in my column. As you can see, the new sequence object has many advantages compared to the identity column property; its flexibility makes it the preferable choice for an auto-numbering mechanism going forward.

To conclude this entry, here are a couple of puzzles for you:

1. Guess what the output of the following code is without running it:

CREATE SEQUENCE dbo.Seq1 AS INT  START WITH 1  INCREMENT BY 1SELECT NEXT VALUE FOR dbo.Seq1 AS v1, NEXT VALUE FOR dbo.Seq1 AS v2;

2. Run the code. Can you explain the logic behind the result?

Cheers,

BG