The sequence object is a feature that was added in SQL Server 2012 as an alternative key-generating
mechanism for identity. It is a standard feature that some of the other database platforms had already
implemented, and now migrations from those platforms are easier. The sequence object is more flex-
CHAPTER 8
Data Modification
257
One of the advantages of the sequence object is that, unlike identity, it is not tied to a particular
column in a particular table; rather, it is an independent object in the database. Whenever you need
to generate a new value, you invoke a function against the object, and use the returned value wher-
ever you like. This means that you can use one sequence object that will help you maintain keys that
would not conflict across multiple tables.
To create a sequence object, use the CREATE SEQUENCE command. The minimum required in for-
mation is just the sequence name, but note that the defaults in such a case might not be what you
want. If you don’t indicate the type, SQL Server will use BIGINT by default. If you want a different
type, indicate AS . The type can be any numeric type with a scale of zero. For example, if you
need your sequence to be of an INT type, indicate AS INT.
Unlike the identity property, the sequence object supports the specification of a minimum value
(MINVALUE ) and a maximum value (MAXVALUE ) within the type. If you don’t indicate
what the minimum and maximum values are, the sequence object will assume the minimum and
maximum values supported by the type. For example, for an INT type, those would be -2,147,483,648
and 2,147,483,647, respectively.
Also, unlike identity, the sequence object supports cycling. Note, though, that the default is not to
cycle, so if you want the sequence object to cycle, you will need to be explicit about it by using the
CYCLE option.
Like identity, the sequence object allows you to specify the starting value (START WITH ) and
the increment (INCREMENET BY ). If you don’t indicate the starting value, the default will be
the same as the minimum value (MINVALUE). If you don’t indicate the increment value, it will be 1 by
default.
So, for example, suppose you want to create a sequence that will help you generate order IDs. You
want it to be of an INT type, have a minimum value of 1 and a maximum value that is the maximum
supported by the type, start with 1, increment by 1, and allow cycling. Here’s the CREATE SEQUENCE
command you could use to create such a sequence.
CREATE SEQUENCE dbo.SeqOrderIDs AS INT
MINVALUE 1
CYCLE;
You had to be explicit about the type, minimum value, and cycling option, because they are dif-
ferent than the defaults. You didn’t need to indicate the maximum, start with, and increment values
because you wanted the defaults.
The sequence object also supports a caching option (CACHE | NO CACHE) that tells SQL
Server how many values to write to disk. If you write less frequently to disk, you’ll get better perfor-
mance when generating a value (on average), but you’ll risk losing more values in case of an unex-
pected shutdown of the SQL Server process. SQL Server has a default cache value that Microsoft
prefers not to publish so that they can change it.
www.it-ebooks.info
258
Microsoft SQL Server 2012 T-SQL Fundamentals
In addition to the type, you can change any of the other options with an ALTER SEQUENCE com-
mand (MINVAL , MAXVAL , RESTART WITH , INCREMENT BY , CYCLE | NO CYCLE,
or CACHE | NO CACHE). For example, suppose you wanted to prevent the dbo.SeqOrderIDs
from cycling. You would change the current sequence definition with the following ALTER SEQUENCE
command.
ALTER SEQUENCE dbo.SeqOrderIDs
NO CYCLE;
To generate a new sequence value, you need to invoke the function NEXT VALUE FOR
name>. It might seem strange that the aforementioned expression is a function, but nevertheless, it is.
You can just call it in a SELECT statement, like this.
SELECT NEXT VALUE FOR dbo.SeqOrderIDs;
This code generates the following output.
-----------
1
Notice that unlike with identity, you didn’t need to insert a row into a table in order to generate a
new value. Some applications need to generate the new value before using it. With sequences, you
can store the result of the function in a variable, and then use it wherever you like. To demonstrate
this, first create a table called T1 with the following code.
IF OBJECT_ID('dbo.T1', 'U') IS NOT NULL DROP TABLE dbo.T1;
CREATE TABLE dbo.T1
(
keycol INT NOT NULL
CONSTRAINT PK_T1 PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);
The following code generates a new sequence value, stores it in a variable, and then uses the vari-
able in an
INSERT statement to insert a row into the table.
DECLARE @neworderid AS INT = NEXT VALUE FOR dbo.SeqOrderIDs;
INSERT INTO dbo.T1(keycol, datacol) VALUES(@neworderid, 'a');
SELECT * FROM dbo.T1;
This code returns the following output.
keycol datacol
----------- ----------
2 a
If you need to use the new key in related rows that you need to insert into another table, you
could use the variable in those INSERT statements as well.
www.it-ebooks.info
