SQL Server 2012 introduces a feature that Oracle professionals will certainly recognize: SEQUENCE. SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you. The last-used value is stored in memory and so no storage is required. Here is an example:

CREATE SEQUENCE dbo.OrderIDs
    AS INT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;
 
SET NOCOUNT ON;
 
SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
2
3

Notice I did not have to create any tables or worry about persisting information to disk in any way. What about reseeding? This is a common problem with IDENTITY columns. You can "reseed" a sequence using ALTER and RESTART WITH:

ALTER SEQUENCE dbo.foo
    RESTART WITH 20;
 
SET NOCOUNT ON;
 
SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
20
21
22

And if you want to roll back the clock or re-use an existing sequence, you can use a lower number with RESTART WITH as well – though I'd be careful with this depending on how you are using the values. Want to increment by a value other than 1 (the default)? You can use INCREMENT BY:

ALTER SEQUENCE dbo.foo
    RESTART WITH 1
    INCREMENT BY 5;
 
SET NOCOUNT ON;
 
SELECT NextOrderID = NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs
UNION ALL     SELECT NEXT VALUE FOR dbo.OrderIDs;

Results:

NextOrderID
-----------
1
6
11

You can start a sequence at a value greater than 1 simply by changing the MINVALUE. You can also start at the low end of the data range, e.g. to use all 4 billion values for INT instead of just the 2 billion positive values. And you can even use INCREMENT BY to count down instead of up; just use a negative value.

You can also ensure that the sequence doesn't use all the possible values, for example if you wanted to have a maximum of 20 values in a TINYINT sequence, you could use the following code to produce a max of 20 values instead of all 255:

CREATE SEQUENCE dbo.foo
    AS TINYINT
    MINVALUE 1
    MAXVALUE 20
    START WITH 1;

When you create such a sequence, you will get the following warning message (note that it is not an error):

And if you actually try to go past the first 20 values:

SELECT NEXT VALUE FOR dbo.foo;
GO 21

You will get this error message:

You can restart the sequence using ALTER SEQUENCE … RESTART WITH. If you want to use up all of the values and then loop around and start over, you can use the CYCLE option.

Surprisingly, if you simply overflow the datatype, you get the same sequence-related error message as opposed to the more familiar Msg 220 (arithmetic overflow) error:

SELECT NEXT VALUE FOR dbo.foo;
GO 256

It is important to note that SEQUENCE will not provide you a transactionally consistent and gap-free stream of values. For example, just like IDENTITY, if your transaction rolls back, the next value is still taken:

CREATE SEQUENCE dbo.bar
    AS INT
    MINVALUE 1
    NO MAXVALUE 
    START WITH 1;
GO
 
BEGIN TRANSACTION;
    SELECT NextBar = NEXT VALUE FOR dbo.bar;
ROLLBACK TRANSACTION;
 
SELECT NextBar = NEXT VALUE FOR dbo.bar;

Results:

NextBar
-----------
1

NextBar
-----------
2

And if you want to check the current value of the sequence without actually using up a value (like checking IDENT_CURRENT() or DBCC CHECKIDENT), you can check the new catalog view, sys.sequences:

SELECT current_value 
    FROM sys.sequences
    WHERE name = 'OrderIDs';

You can also check several other properties of the sequence this way (don't bother trying to use sp_help – all it will tell you is that it is a sequence object).

Want to get a range of SEQUENCE values? Don't use a loop; there's a stored procedure for that: sys.sp_sequence_get_range. Here is a sample:

DECLARE @fv SQL_VARIANT, @lv SQL_VARIANT;
 
EXEC sys.sp_sequence_get_range
    @sequence_name     = 'OrderIDs',
    @range_size        = 20,
    @range_first_value = @fv OUTPUT,
    @range_last_value  = @lv OUTPUT;
 
SELECT 
    fv = CONVERT(INT, @fv), 
    lv = CONVERT(INT, @lv),
    next = NEXT VALUE FOR dbo.OrderIDs;

Results:

fv     lv     next
----   ----   ----
5      24     25

But how does it perform?

I can see significant performance improvements over using a central identity table or a custom increment table (two typical ways this feature has been implemented). In the following example, I show five different ways to manage a central value generator. The first is a custom increment table, a popular method which uses a quirky update and is certainly far from standard. Next there are two methods using an IDENTITY column: one where the old rows are deleted before the next value is generated (for no other reason other than to save space), and the other that doesn't bother to do the delete, letting the table grow as new values are generated. Finally, we use the sequence, once with a procedure wrapper to simplify the changes to existing scripts, and once with the new NEXT VALUE syntax inserted into the code:

USE [master];
GO
 
-- drop database and #temp table if they exist
-- then create the database
 
IF DB_ID(N'BookStore') > 0
BEGIN
   DROP DATABASE [BookStore];
END
GO
 
IF OBJECT_ID(N'tempdb..#timing') IS NOT NULL
BEGIN
   DROP TABLE #timing;
END
GO
 
CREATE DATABASE [BookStore];
GO
 
USE [BookStore];
GO
 
SET NOCOUNT ON;
GO
 
-- create a table with an IDENTITY column
CREATE TABLE dbo.CentralIdentity
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY
);
 
-- create a "roll your own" increment table 
-- and seed initial value
CREATE TABLE dbo.CentralIncrement
(
    ID BIGINT PRIMARY KEY
);
GO
 
INSERT dbo.CentralIncrement(ID) SELECT 0;
GO
 
-- create a sequence with the same seed 
-- and increment properties
CREATE SEQUENCE dbo.CentralSequence
    AS BIGINT
    MINVALUE 1
    NO MAXVALUE
    START WITH 1;
GO
 
-- stored procedure for increment using quirky update
CREATE PROCEDURE dbo.Central_CreateIncrement
   @NextID BIGINT OUTPUT
AS
BEGIN
   SET NOCOUNT ON;
 
   UPDATE dbo.CentralIncrement 
       SET @NextID = ID = ID + 1;
END
GO
 
-- stored procedure for increasing IDENTITY
-- optionally deleting existing value(s) first
CREATE PROCEDURE dbo.Central_CreateIdentity
   @NextID BIGINT OUTPUT,
   @Delete BIT = 0
AS
BEGIN
   SET NOCOUNT ON;
 
   IF @Delete = 1
   BEGIN
       DELETE dbo.CentralIdentity;
   END
 
   INSERT dbo.CentralIdentity DEFAULT VALUES;
   SELECT @NextID = SCOPE_IDENTITY();
END
GO
 
-- stored procedure for handling bumping the SEQUENCE
CREATE PROCEDURE dbo.Central_CreateSequence
   @NextID bigint OUTPUT
AS
BEGIN
   SET NOCOUNT ON;
   SELECT @NextID = NEXT VALUE FOR dbo.CentralSequence;
END
GO
 
-- simple table that will be the recipient of key values
CREATE TABLE dbo.Orders
(
   OrderID int PRIMARY KEY
);
GO
 
-- simple table for tracking timings
CREATE TABLE #timing
(
   Step    varchar(64),
   dt      datetime2(7) NOT NULL DEFAULT SYSUTCDATETIME()
);
GO
 
-- start each step with a blank orders table
TRUNCATE TABLE dbo.Orders;
GO
 
INSERT #timing(Step) 
  SELECT 'Starting increment';
GO
 
DECLARE @NextID BIGINT;
 
EXEC dbo.Central_CreateIncrement
   @NextID = @NextID OUTPUT;
 
INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000
 
INSERT #timing(Step) 
  SELECT 'Finished increment';
GO
 
TRUNCATE TABLE dbo.Orders;
GO
 
INSERT #timing(Step) 
  SELECT 'Starting identity + delete';
GO
 
DECLARE @NextID BIGINT;
 
EXEC dbo.Central_CreateIdentity
   @NextID = @NextID OUTPUT,
   @Delete = 1;
 
INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000
 
INSERT #timing(Step) 
  SELECT 'Finished identity + delete';
GO
 
TRUNCATE TABLE dbo.Orders;
GO
 
INSERT #timing(Step) 
  SELECT 'Starting identity without delete';
GO
 
DECLARE @NextID BIGINT;
 
EXEC dbo.Central_CreateIdentity
   @NextID = @NextID OUTPUT,
   @Delete = 1;
 
INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000
 
INSERT #timing(Step) 
  SELECT 'Finished identity without delete';
GO
 
TRUNCATE TABLE dbo.Orders;
GO
 
INSERT #timing(Step) 
  SELECT 'Starting sequence with PROC';
GO
 
DECLARE @NextID BIGINT;
 
EXEC dbo.Central_CreateSequence
   @NextID = @NextID OUTPUT;
 
INSERT dbo.Orders(OrderID) SELECT @NextID;
GO 100000
 
INSERT #timing(Step) 
  SELECT 'Finished sequence with PROC';
GO
 
-- this step handles the SEQUENCE directly
TRUNCATE TABLE dbo.Orders;
GO
 
INSERT #timing(Step) 
  SELECT 'Starting sequence without PROC';
GO
 
INSERT dbo.Orders(OrderID) 
  SELECT NEXT VALUE FOR dbo.CentralSequence;
GO 100000
 
INSERT #timing(Step) 
  SELECT 'Finished sequence without PROC';
GO
 
-- ok, now we can run a query to measure timings
;WITH t AS 
(
   SELECT
       Step,
       dt,
       rn = ROW_NUMBER() OVER (ORDER BY dt) 
   FROM
       #timing
)
SELECT
   t1.Step,
   dur_s  = DATEDIFF(SECOND, t1.dt, t2.dt)
FROM t AS t1
INNER JOIN t AS t2
ON t1.rn = t2.rn - 1
WHERE t1.rn % 2 = 1
AND t2.rn % 2 = 0;
GO

The results, keeping in mind that this was in a VM on a laptop:

The clear winner is obviously the SEQUENCE operator, since it doesn't have to write the data to disk in order to persist the current value. Next I ran the same tests, but this time turned page compression on for the increment and identity tables, to see if the reduced I/O might let the existing solutions come a little closer:

CREATE TABLE dbo.CentralIdentity
(
    ID BIGINT IDENTITY(1,1) PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);
 
CREATE TABLE dbo.CentralIncrement
(
    ID BIGINT PRIMARY KEY
)
WITH (DATA_COMPRESSION = PAGE);

The results were indistinguishable. In fact, from run to run, one method would gain a second, and another would lose two; on the next run, the first one would lose three seconds and another would gain one.

I also tried putting a NEXT VALUE FOR command in a user-defined function, to see if there was an easier way to inline the syntax, and see if the UDF overhead would impact the observed benefit. However if you try to create a function with a reference to a sequence object, here is the error you will get (which will also point out a few other places where you shouldn't bother trying to use them – it's a pretty long laundry list):

Note also that you can use NEXT VALUE FOR in a UNION ALL, but not in a UNION, EXCEPT, INTERSECT, or with DISTINCT:

If you look in sys.messages, you'll see there are several other limitations in the initial implementation of SEQUENCE. Some of these error messages are quite logical, but others are quite interesting. For example, it is okay to reference a sequence object in another database, but not when defined as a default constraint.

I hope that gives a good taste of SEQUENCE. As you start contemplating the move to the next version of SQL Server, you may want to consider this feature if you are currently using a central identity or increment table – as long as you can get along with its limitations. If you want more official information on SEQUENCE, including a great description on how memory is used to cache sequence values and avoid I/O, you can start at this Books Online topic, Sequence Numbers.