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:
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:
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:
Then use the NEXT VALUE FOR expression as part of INSERT statements against the tables:
You can also define default constraints (extension to the standard) that produce new sequence values, like so:
Run the following INSERT statements allowing the default constraints to generate the new values:
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:
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:
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:
2. Run the code. Can you explain the logic behind the result?