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:

  • It is table specific
  • You cannot obtain the new value in your application before using it
  • You cannot add or remove the property from an existing column
  • You cannot generate new values in an UPDATE statement when needed, rather only in INSERT statements
  • The semantics of defining ordering in a multi-row insert are confusing, and in SELECT INTO statements are actually not guaranteed
  • You cannot define: minimum and maximum values, whether to allow cycling, and caching options
  • You can reseed an identity property, but you cannot change the step size
  • You cannot obtain a whole range of new identity values in one shot, letting the application assign the individual values

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

  • It is table-independent
  • You can obtain the new value before using it in an INSERT statement
  • You can add or remove a default constraint defined for a column with an expression that generates a new sequence value (extension to the standard)
  • You can generate new values in an UPDATE statement
  • The semantics of defining ordering in a multi-row insert are very clear using an OVER clause (extension to the standard), and are even allowed in SELECT INTO statements
  • You can define minimum and maximum values, whether to allow cycling, and a cache size option for performance (extension to the standard)
  • You can alter any of the properties of a sequence object besides the data type, including the current value, increment, minimum value, maximum value, cycle and cache size
  • You can obtain a whole range of new sequence values in one shot using the stored procedure sp_sequence_get_range (extension to the standard), letting the application assign the individual values for increased performance

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           A
6           C

col1        col2
----------- --------------------------------------------------
5           B
7           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           A
6           C
8           Road-150 Red, 62
9           Road-150 Red, 44
10          Road-150 Red, 48
11          Road-150 Red, 52
12          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 1

SELECT 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

Discuss this Blog Entry 3

on Nov 22, 2010
I guess the reason is to give us the abillity to use the value for further calculation based on it, For Example:

NEXT VALUE FOR dbo.Seq1 AS AutoNum,
1000000+NEXT VALUE FOR dbo.Seq1 AS ID..


on Nov 23, 2010
Hi Geri and William,

You're both right. The code will return 1 and 1 and not 1 and 2 as some would expect. This is standard behavior that is due to the all-at-once concept in SQL. This concept means that all expressions that appear in the same logical phase (e.g., the SELECT phase) are as if evaluated at the same point in time. Since NEXT VALUE FOR is an expression, it must follow the same behavior.



on Nov 23, 2010
I believe I'm right in saying that other than column order, the order of items in the SELECT list doesn't mean anything - so it would be wrong for SQL Server to arbitrarily provide e.g. 1 as the result of the NEXT VALUE for one column and e.g. to another column.

Please or Register to post comments.

What's Puzzled By T-SQL Blog?

T-SQL tips and logical puzzles from Itzik Ben-Gan.

Blog Archive

Sponsored Introduction Continue on to (or wait seconds) ×