Sequences, Part 1

SQL Server 2012 (formerly code-named Denali), introduces support for sequences. A sequence is an object used to auto-generate numbers for different purposes, such as keys. In previous versions of SQL Server, you could use the IDENTITY column property for similar purposes, but sequences are more flexible than IDENTITY in many ways. This article is the first in a two-part series about sequences, in which I define sequences, explain how to create and use them, and discuss their advantages over IDENTITY.

Related: Sequences, Part 2

The Basics

To work with sequences, you need to be familiar with only a small set of language elements: the CREATE SEQUENCE, ALTER SEQUENCE, and DROP SEQUENCE commands, which are used to create, alter, and drop sequences, respectively; the NEXT VALUE FOR function, which is used to retrieve the next value from a sequence; the sp_sequence_get_range procedure, which is used to secure a consecutive range of sequence values; and the sys.sequences view, which is used to query information about existing sequences. I’ll start with the basics of sequences, then discuss the more advanced aspects later in the article.

A sequence is an independent object in the database, unlike IDENTITY, which is a property tied to a particular column in a particular table. A basic and typical form of a sequence definition is one in which you indicate the data type of the sequence, which value to start with, and which value to increment by, like so:

CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

This command is pretty straightforward and self-explanatory. It creates a sequence called Seq1 in the dbo schema in the database to which you’re currently connected. The sequence is created with the INT data type and is defined to start with 1 and increment by 1.

To obtain a new sequence value, the application would normally invoke the NEXT VALUE FOR function; for example, run the following code multiple times and see how you get a new value every time:

SELECT NEXT VALUE FOR dbo.Seq1 AS newval;

The fact that you use an expression leads to another advantage of sequences over IDENTITY, in addition to the fact that sequences aren’t tied to specific tables. You can obtain a new sequence value before using it, like so:

DECLARE @newval AS INT = NEXT VALUE FOR dbo.Seq1;SELECT @newval;

Of course, you’d typically use the obtained value in an INSERT statement, but you can use it for other purposes as well.

Back to the CREATE SEQUENCE command: I mentioned that this command creates the sequence in the database you’re connected to. You can’t use the three-part name including the database name; instead, you have to make sure you’re connected to the database where you want to create the sequence. If you want to first check whether such a sequence already exists in the database and, for example, drop it in such a case before creating the new one, you can use the OBJECT_ID function and look for a non-NULL value. Here’s an example in which the code first sets the database context (in this case to AdventureWorks2008R2), drops the sequence object if it already exists, and creates a new one:

USE AdventureWorks2008R2;IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;

Note that I indicated the type INT; otherwise, SQL Server would have used BIGINT as the default. Also, I indicated to start with the value 1; otherwise, SQL Server would have used the lowest value in the type as the default (e.g., -2147483648 for INT). The default value to increment by is 1, which would often be correct; still, it feels more natural to specify the value explicitly—especially if you’re already specifying the start-with value. I therefore specify it in what I refer to as “the typical definition.

You can specify several additional options in the sequence definition. Here’s the full syntax as it appears in SQL Server Books Online:

CREATE SEQUENCE [schema_name . ] sequence_name[  [ ...n ] ]     [ ; ::= {     [ AS { built_in_integer_type | user-defined_integer_type } ]     | START WITH           | INCREMENT BY           | { MINVALUE  | NO MINVALUE }         | { MAXVALUE  | NO MAXVALUE }         | { CYCLE | NO CYCLE }         | { CACHE [ ] | NO CACHE } }

As you can see, you can define minimum and maximum values in case you want them to be different from the minimum and maximum that the type supports. Also, you can indicate whether you want the sequence to automatically cycle after reaching a boundary value, as opposed to throwing an exception in such a case, which is the default behavior. CACHE is a more advanced option related to performance; I cover this option next month.

To obtain information about your sequence, query the sys.sequences view. For example, the following code returns information about the sequence Seq1:

SELECT current_value,start_value, increment, minimum_value, maximum_value, is_cycling, is_cached, cache_size FROM sys.SequencesWHERE object_id = OBJECT_ID('dbo.Seq1');

Changing Sequence Properties

You can change any of the properties of an existing sequence using the ALTER sequence command, with the exception of the sequence data type. To change the sequence data type, you must drop and recreate it. To change existing sequence properties, you use the same keywords as in the CREATE SEQUENCE command, with one exception—instead of the START WITH property used in the CREATE SEQUENCE command, you use the RESTART WITH property in the ALTER SEQUENCE command.

As an example, to change Seq1 to restart with -2147483648 and to cycle, use the following code:

ALTER SEQUENCE dbo.Seq1  RESTART WITH -2147483648  CYCLE;

To change it to restart with 1 and to not cycle, use

ALTER SEQUENCE dbo.Seq1  RESTART WITH 1  NO CYCLE;

Note that SQL Server won’t let you apply changes that aren’t sensible. For example, the current value of the sequence Seq1 is 1. An attempt to change the minimum sequence value to a value greater than 1 without restarting the current sequence value will fail. To illustrate this point, try running the following code:

ALTER SEQUENCE dbo.Seq1MINVALUE 100;

You’ll get the error message that Figure 1 shows.

Figure 1: Error message from trying to change the minimum sequence value

To change the minimum value to 100, you must also restart the current value to one that’s within the supported range, as in:

ALTER SEQUENCE dbo.Seq1  RESTART WITH 100  MINVALUE 100;

Run the following code to restart the sequence with 1 and remove the minimum:

ALTER SEQUENCE dbo.Seq1  RESTART WITH 1  NO MINVALUE;

Working with Sequences

The code samples in this section use a sequence called Seq1 and a table called T1, which you create by running the following code:

IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;IF OBJECT_ID('dbo.Seq1', 'SO') IS NOT NULL DROP SEQUENCE dbo.Seq1;CREATE SEQUENCE dbo.Seq1 AS INT START WITH 1 INCREMENT BY 1;CREATE TABLE dbo.T1(  col1 INT NOT NULL    CONSTRAINT PK_T1 PRIMARY KEY(col1),  col2 VARCHAR(10) NOT NULL);

A common reason to use sequences is to create values that will serve as keys in INSERT statements. Earlier, I showed you that unlike with IDENTITY, sequences let you produce a new value first by invoking the NEXT VALUE FOR expression, perhaps store it in a variable, and later use it in your INSERT statement. You can also use the NEXT VALUE FOR expression directly in your INSERT statement, like so:

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

If you prefer, you can define a default constraint for the column with the NEXT VALUE FOR expression such that if you don’t specify a value for the column, the constraint will obtain it from a sequence. Here’s an example for such a constraint:

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

Note that this capability is an extension to the standard.

To test the constraint, run the following code:

INSERT INTO dbo.T1(col2) VALUES('D'),('E'),('F');SELECT * FROM dbo.T1;

You should find six rows in the output with values 1 through 6 in col1. The values 1, 2, and 3 were produced by the INSERT statement that explicitly called the NEXT VALUE FOR expression, and the values 4, 5, and 6 were produced by the last INSERT statement relying on the DEFAULT expression.

You can also use the NEXT VALUE FOR expression in a SELECT statement—never mind whether or not you insert the result of the query into a target table. For example, the following query produces a sequence value for each row without inserting the result into a target table:

SELECT  NEXT VALUE FOR dbo.Seq1 AS newkey,  LEFT(FirstName, 1) + LEFT(LastName, 1) AS InitialsFROM Person.PersonWHERE PersonType = 'EM'  AND EmailPromotion = 0;

Note, however, that you can’t use a query that invokes the NEXT VALUE FOR expression in a subquery or table expression.

The same query can appear in an INSERT SELECT statement as well:

INSERT INTO dbo.T1(col1, col2)  SELECT    NEXT VALUE FOR dbo.Seq1 AS newkey,    LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials  FROM Person.Person  WHERE PersonType = 'EM'    AND EmailPromotion = 0;

SQL Server adds another extension to the standard, which lets you define logical ordering in which the sequence values are assigned using an OVER clause similar to the one used in window functions. So for example, in the following query the sequence values are assigned based on BusinessEntityID ordering:

INSERT INTO dbo.T1(col1, col2)  SELECT    NEXT VALUE FOR dbo.Seq1 OVER(ORDER BY BusinessEntityID) AS newkey,    LEFT(FirstName, 1) + LEFT(LastName, 1) AS Initials  FROM Person.Person  WHERE PersonType = 'EM'    AND EmailPromotion = 1;

Another very interesting capability you get with sequences that IDENTITY doesn’t support is that you can assign sequence values to rows in an UPDATE statement, like so:

UPDATE dbo.T1SET col1 = NEXT VALUE FOR dbo.Seq1;

Sequences and Transactions

Much like with IDENTITY, if you acquire new sequence values in a transaction that’s ultimately rolled back, the change to the current sequence value isn’t undone. I’ll demonstrate this behavior with an example. First, clear the table T1 and reseed the sequence Seq1 by running the following code:

TRUNCATE TABLE dbo.T1;ALTER SEQUENCE dbo.Seq1 RESTART WITH 1;

Next, run the following code to insert three rows in a committed transaction, followed by an insertion of three rows in a transaction that’s rolled back, followed by an insertion of three rows in a committed transaction, then query the table:

INSERT INTO dbo.T1(col2) VALUES('A'),('B'),('C');BEGIN TRAN  INSERT INTO dbo.T1(col2) VALUES('D'),('E'),('F');ROLLBACK TRANINSERT INTO dbo.T1(col2) VALUES('G'),('H'),('I');SELECT * FROM dbo.T1;

After running the code, you’ll find in T1.col1 the values 1, 2, 3, 7, 8, and 9. The change to the current sequence values performed by the second insertion wasn’t undone even though the transaction rolled back. This means that you can’t rely on sequences for cases in which you can’t allow gaps (e.g., for invoice numbers), much like you can’t rely on IDENTITY for such purposes. If you need a sequence that guarantees no gaps, you must implement your own solution. For example, you can keep track of the last used value in a table, and whenever you need a new value, increment the existing one using an UPDATE statement and retrieve it. Such a solution will give you a blocking sequence. For obvious reasons, it won’t perform as well as a sequence object, but it will guarantee no gaps.

Sequences vs. IDENTITY

This article is the first in a two-part series about the use of sequences in the next version of SQL Server. Compared with using the IDENTITY column property, sequences have many advantages. I didn’t cover all the advantages in this article, but I’ll discuss more advantages in next month’s article. This month I introduced sequences and covered the basics. I discussed modifying sequence properties, working with sequences, and using sequences in transactions. Next month I’ll cover caching, obtaining ranges of sequence values, and dealing with specialized cases in which you need to produce multiple sequence values for the same target row. Table 1 summarizes the differences between IDENTITY and sequences, including the elements I covered this month and the ones I’ll discuss next month.

Table 1: Comparison of IDENTITY and Sequences