Many applications need sequentially incremental number as unique/primary key for records. SQL Server 2008 supports identity columns as the primary solution to this challenge.

The unique number is generated upon the execution of the insert statement, this means that the value of the insertion is not known before the insert has been executed. If your application needs the value of the sequence prior to the insert, the identity function is not that helpful to you.

This is where the sequence comes to play. With SQL Server Denali and the new sequence feature it is possible to query the next value of the sequence before you do the insert. This can come in handy in many scenarios for example if you have to put data into different tables, have a parent-child scenario, or even if you need to shard your data based on the sequence value.

Let’s have a look at some code, to how this works:

First of all we have to create our Sequence. The Sequence is create in the active database, is it however possible to get the next number from another database, by using the database.schema.sequence syntax.

CREATE SEQUENCE MySequence AS Int
MINVALUE 1
NO MAXVALUE
START WITH 1
INCREMENT BY 1;

The datatype in my little example is Integer, the datatype can be any of theese type: int, bigint, smallint, tinyint, decimal or numeric.

Get the next number from the sequence by using the NEXT VALUE FOR function, this is handy in many situations, here is a few code examples:

-- The simple way
SELECT NEXT VALUE FOR MySequence
-- Together with an insert
INSERT INTO Persons (PersonId, Firstname, Lastname)
VALUES (NEXT VALUE FOR MySequence, 'Martin', 'Schmidt')

Because the sequence is an object, and not linked to any table like the old Identity is, this gives us the options to use is among several tables, if we wan’t to do that.

It is of course possible to execute alter statements on your Sequence, here is a few examples of that:

-- ALTER MySequence to set next number to 100 and increment by 25
ALTER SEQUENCE MySequence
RESTART WITH 100
INCREMENT BY 25;
-- Restart MySequence with the value 1
ALTER SEQUENCE MySequence
RESTART WITH 1
INCREMENT BY 1;

Hopefully this little blog post gives you some information on what there is on the way in the next release of SQL Server 2011 (Denali) – to be honest, I can’t wait till the day it gets released.