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.

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:

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:

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:

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:

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:

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:

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:

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.T1 SET 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:

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.

From the Blogs

My initial goal in writing this series of posts was to outline some of the concerns surrounding Availability Groups (AGs) and SQL Server Agent Jobs – and call out how there is virtually no guidance from Microsoft on this front and then detail some of the pitfalls and options available for tackling this problem domain. I initially expected this series of posts to have between 25 and 30 posts – according to some of the early outlines I created ‘way back when’....More

Throughout this series of posts I’ve taken a somewhat pessimistic view of how SQL Server Agent jobs are managed within most organizations – meaning that most of the code and examples I’ve provided up until this point were based on assumptions about how CHANGE to jobs is managed. That pessimism, to date, has come in two forms:...More

In this series of posts I’ve called out some of the concerns related to SQL Server AlwaysOn Availability Groups and their interaction with SQL Server Agent jobs – both in the form of Batch Jobs (see post #3) and backups....More