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?