Using Sequences in SQL Server "Denali"

Over the years, SQL Server developers have become more and more inventive at generating sequential ranges of numbers. The identity column works fine for a single table or view, however when you want a similar effect across multiple tables, issues abound. Hence, a collective sigh of relief was heard with the introduction of the SEQUENCE keyword in SQL Server "Denali". It's also useful when you want to generate a key value before actually adding the row to a table or to use the same key value for records spread across multiple tables.

So let's start by looking at the simplest use of sequences. In the following example, I create a sequence and use it to assign customer numbers in an online shopping application.

USE MyShop;
GO

CREATE SEQUENCE Shopping.OrderNumberSequence
INCREMENT BY 10;
GO

When you create a sequence, you can specify the start value, the increment, and the minimum and maximum values. The increment can be a positive value for increasing sequences or a negative value for decreasing sequences. The minimum and maximum values define the start and end points of the sequence, with the default settings being the minimum and maximum values for the data type of the sequence.

You can use the sequence as a default value for columns in your table by using the NEXT VALUE statement. Here, I'll create a table and specify to use the sequence to generate order numbers.

CREATE TABLE [Shopping].[Orders](
[OrderNumber] int PRIMARY KEY DEFAULT (NEXT VALUE FOR Shopping.OrderNumberSequence)
[CustomerNumber] int NOT NULL,
[OrderDate] date NOT NULL,
[OrderStatus] int NOT NULL,
[TotalCost] money NOT NULL)
ON PRIMARY;
GO

When I run this code and then insert a row into the table, the OrderNumber created is -214783648 which is the minimum value for an int. It's not exactly what I'd envisaged though, so I can use ALTER SEQUENCE ... RESTART WITH to reset the initial value for the sequence.

ALTER SEQUENCE Shopping.OrderNumberSequence
RESTART WITH 10;
GO

Altering the sequence has no effect on the existing values used from the sequence, it just reinitializes the sequence to begin at 10. If I'd planned ahead though, I could have simply used the START WITH argument when creating the sequence.

Another key property of a sequence is cycling. You can configure the sequence to restart at the minimum value if the maximum value is reached. Obviously, this removes uniqueness from the sequence, however you can control uniqueness in a table by assigning a trigger to the column using the sequence.

So, what's actually happening to create these values and where are they being stored? If you query sys.sequences, you'll see all the information about the sequences in your database. This catalog view shows you definition of the sequence as well as the current value and some caching information. When you create the sequence, you can define whether to cache values and how many items to cache. If enabled, caching can increase the performance of your applications by decreasing the file access operations they use.

If you create a sequence and define a cache size of 20, when the first value is requested SQL Server will put values for items 1 through 20 into memory and the last value cached is written as the current value on disk. This means that no more disk operations are needed until the 21st value is requested. The trade off of this performance improvement is the risk that should your SQL Server stop unexpectedly, when it restarts it will allocate again from the current value stored on disk, which could result in a gap in the sequence. If that's not a concern to you, consider caching your sequence values. If you need to avoid gaps in the sequence, use the NO CACHE option, but be aware that if you request a number and then do not use it or do not commit the transaction that used it, gaps can still occur.

Finally, to remove a sequence from a database, you use the DROP SEQUENCE statement. As soon as a value from a sequence is used, it loses any links to the sequence, so dropping the sequence has no effect on the tables that used it. However, if you are referencing it from a table definition as I did for the OrderNumber column, you won't be able to drop it until the dependency is removed.