Using Sequences in SQL Server Denali

Historically, one notable difference between SQL Server and other database platforms (such as Oracle and DB2) has been the manner in which you implement automatically assigned integer values for primary keys when inserting new rows. SQL Server provides the IDENTITY attribute for the int or bigint data types, while other platforms require you to create an independent “sequence generator” object that feeds incrementing values to new rows in the table. These are two different ways to achieve the same thing, and SQL Server’s IDENTITY attribute is arguably simpler to use, but sequence generators offer their own unique advantages as well.

SQL Server Denali now also supports sequence generators as a powerful alternative to using the IDENTITY attribute. Sequences can be created on integer types (both built-in and user-defined), and you can specify the minimum, maximum, start, and increment (or decrement) values for the sequence. You can also cycle back around to the minimum value when the maximum value is reached (or vice versa).

Sequences are most commonly used to assign new primary key values on INSERT operations as an alternative to using IDENTITY-attributed key columns. But because they exist as objects independent of the data tables that they feed new primary keys to, they offer more flexibility and pose fewer limitations. For example, you can obtain the next value in the sequence before you perform the INSERT, and you can insert any (unique) value into the primary key column without requiring SET IDENTITY INSERT ON/OFF statements. The NEXT VALUE FOR syntax can be used in any SELECT, INSERT, or UPDATE statement to request and increment the next value in the sequence. Sequences are valid in SET, CASE, and DECLARE statements that set integer values, and they can be used in stored procedures, UDFs, and triggers. Let’s see how sequences work:

CREATE TABLE Customer
(Id        int PRIMARY KEY,
 FirstName varchar(max),
 LastName  varchar(max))

-- Create the sequence with a start, increment, and min/max settings
CREATE SEQUENCE CustomerSequence AS int
 START WITH 1
 INCREMENT BY 1
 MINVALUE 0
 NO MAXVALUE

-- Use the sequence for new primary key values
INSERT INTO Customer (Id, FirstName, LastName) VALUES
(NEXT VALUE FOR CustomerSequence, 'Bill', 'Malone'),
(NEXT VALUE FOR CustomerSequence, 'Justin', 'Thorp'),
(NEXT VALUE FOR CustomerSequence, 'Paul', 'Duffy')

Our Customer table uses an integer primary key, but does not specify the IDENTITY attribute. Instead, we use the new CREATE SEQUENCE statement in SQL Server Denali to create a CustomerSequence object that feeds new integers, starting with one and incrementing by one, with no specified upper limit (beyond the maximum size for the integer data type; a 32-bit int data type in this example). We then INSERT three new rows, each of which specifies NEXT VALUE FOR CustomerSequence as the new Id value (note that we’re using the row constructor syntax introduced in SQL Server 2008 to INSERT the three rows with a single statement). The result, as you’d expect, appears like this:

SELECT * FROM Customer

Id    FirstName LastName
----- --------- -------------
1     Bill      Malone
2     Justin    Thorp
3     Paul      Duffy

(3 row(s) affected)

Most likely, you’ll want to emulate the experience of using the IDENTITY attribute; that is, you may wish to omit the primary key values from the INSERT statement and put SQL Server in charge of assigning them to new rows. This is easily done by establishing NEXT VALUE FOR as a DEFAULT constraint on the Id column, like this:

-- Set the default for IDENTITY-behavior
ALTER TABLE Customer
 ADD DEFAULT NEXT VALUE FOR CustomerSequence FOR Id

-- Generates customer ID 4
INSERT INTO Customer (FirstName, LastName) VALUES('Jeff', 'Smith')

With the DEFAULT constraint in place, the INSERT statement looks and works just the same as if we were using an IDENTITY-attributed primary key column. Customer Jeff Smith is automatically assigned an Id value of 4. But by using sequences, we can enjoy a few additional benefits. For example, we can peek at the current value without consuming it by querying the sys.sequences catalog view. Among the many parameters for each sequence object in the database exposed by this view, the current_value column reveals the currently assigned value:

SELECT current_value FROM sys.sequences WHERE name='CustomerSequence'

current_value
-------------
4

(1 row(s) affected)

You can also use the ALTER SEQUENCE statement to change the behavior of an existing sequence object, like so:

ALTER SEQUENCE CustomerSequence
 RESTART WITH 1100
 MINVALUE 1000
 MAXVALUE 9999
 CYCLE

Now the next value returned by CustomerSequence will be 1100, and it will continue to increment by one from there. When it tops 9999, it will start again from 1000 and continue incrementing normally. Naturally, given the uniqueness of primary keys, the sequence can no longer be used to populate the Customer table 100 rows after it recycles to 1000, because we already have customers with primary keys starting at 1100.

Of course, sequences have some restrictions, but these aren’t too onerous overall. Sequences cannot be used in a subquery, CTE, TOP clause, CHECK CONSTRAINT definition, or as an argument to an aggregate function. They also can’t be used in views, computed columns, and user-defined functions, as those object types are not allowed to cause the side effects of sequence number generation. Finally, you cannot issue a DROP SEQUENCE statement to delete a sequence while you have tables with existing DEFAULT constraints that reference the sequence; you’ll need to drop the referencing constraints before you can drop the sequence.

Download the Denali CTP now to start playing with sequences, and have fun!

Advertisements