The SEQUENCE Object in SQL Server codenamed “Denali”

The next generation of the SQL Server product, code named “Denali”, has announced some days ago. It has many functionality enhancements to help T-SQL programmers to deliver more efficient code. The SEQUENCE object is one of the new T-SQL built-in types that automatically generates sequential numbers in specific conditions and settings.

Create a SEQUENCE Object

The new CREATE SEQUENCE statement can be used to create a database wide SEQUENCE object to use. The following code creates a SEQUENCE object on the default schema with a name MySeq.

USE TechTalk;
GO
 
-- Create a SEQUENCE object on schema "dbo".
CREATE SEQUENCE MySeq;
GO
 
-- Verify if the MySeq is created.
SELECT * FROM sys.sequences WHERE name = N'MySeq';

In the above example, I created a SEQUENCE object MySeq, of default type int, with default start value –2147483648 (the minimum value of a 32-bit signed integer) and auto-increment by 1. To check the next value of MySeq object, use NEXT VALUE FOR function, as shown below:

SELECT NEXT VALUE FOR MySeq;

In addition, to create a SEQUENCE object with an initial start value, an increment, and specify a integral type for this object, you can use the following code:

USE TechTalk;
GO
 
-- Create a SEQUENCE object on schema "dbo".
CREATE SEQUENCE MySeq AS tinyint
	START WITH 0
	INCREMENT BY 5;
GO
 
-- Verify if the MySeq is created.
SELECT * FROM sys.sequences WHERE name = N'MySeq';
 
-- Get the next value for MySeq.
SELECT NEXT VALUE FOR MySeq;
SELECT NEXT VALUE FOR MySeq;

When I run this code, I get the following result:

Note: The default start value for a SEQUENCE object is the min value of the type of that object, for example, if the SEQUENCE object is of type int, the default start value is -2147483648. To make code readable, it is highly suggested that you always specify the start value and the incremental value of a CREATE SEQUENCE statement.

Drop a SEQUENCE Object

Similarly, you can use the DROP SEQUENCE statement to drop a SEQUENCE object, like I do as follows:

USE TechTalk;
GO
 
-- Create a SEQUENCE object on schema "dbo" if not exist.
IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'MySeq')
	DROP SEQUENCE MySeq;
GO
 
CREATE SEQUENCE MySeq AS tinyint
	START WITH 0
	INCREMENT BY 5;
GO

Use SEQUENCE Object

When created a SEQUENCE object, it is database wide, meaning different tables can access the same SEQUENCE object. This behavior is different as an IDENTIFY column – the last one can only be used in the table or view that defined it.

The following code shows the simple usage of SEQUENCE object MySeq defined by previous code.

-- Declare table variable.
DECLARE @contact TABLE
(
	Id int NOT NULL PRIMARY KEY,
	Name nvarchar(50) NOT NULL,
	Age int NOT NULL
);
 
-- Insert values into this table.
INSERT @contact (Id, Name, Age)
VALUES 
	(NEXT VALUE FOR MySeq, N'Mark Zhou', 27),
	(NEXT VALUE FOR MySeq, N'Dixin Yan', 25),
	(NEXT VALUE FOR MySeq, N'Wei Ye', 28);
 
-- Update the table.
UPDATE @contact
SET Id = NEXT VALUE FOR MySeq
WHERE Name = N'Mark Zhou';
 
-- Get the next value of MySeq.
DECLARE @nextValue int = NEXT VALUE FOR MySeq;
 
-- Use MySeq in different tables.
UPDATE @myTable
SET Id = NEXT VALUE FOR MySeq
FROM @contact AS c
WHERE c.Id = @nextValue;

Advanced Usages

1. Specify min and max Value for a SEQUENCE Object

To specify the minimum or maximum value for a SEQUENCE object, add MINVALUE or MAXVALUE directives in CREATE SEQUENCE statement. Note that the min value must be less than or equal to start value, and both min and max values must in boundaries. For example, the min value cannot be less than -2147483648 if the type of this SEQUENCE object is of 32-bit integer.

The following code shows how you can specify min or max value.

CREATE SEQUENCE MySeq AS tinyint
	START WITH 0
	INCREMENT BY 5
	MINVALUE 0
	MAXVALUE 255
GO

2. Specify Whether a SEQUENCE Object is Cycled

If you turn this option on, when the next value exceeds the maximum value of the SEQUENCE object specified by the MAXVALUE directive, or the default max value of the SEQUENCE type, it will be restarted to the minimum value that specified by the MINVALUE directive, or the default min value of the SEQUENCE type, and vice versa if the INCREMENT BY directive specifies a negative integer value.

The following code shows how to turn cycling on.

CREATE SEQUENCE MySeq AS tinyint
	START WITH 0
	INCREMENT BY 5
	MINVALUE 0
	MAXVALUE 255
	CYCLE;
GO

If you specify NO CYCLE, the cycling feature will be turned off. When it turned off, an exception will be thrown when the current value exceeds to the min or max value of the SEQUENCE.

3. Restart a SEQUENCE

To restart the current value of a SEQUENCE object to its initial or specified value, use ALTER SEQUENCE statement, as shown below:

ALTER SEQUENCE MySeq
	RESTART;

To specify a restart value, use the following code:

ALTER SEQUENCE MySeq
	RESTART WITH 10;

4. Use NEXT VALUE FOR with OVER Clause

Combine the NEXT VALUE FOR function with OVER clause to sort the result set prior to generate sequence numbers. The following example shows how to use it:

USE TechTalk;
GO
 
-- Create a SEQUENCE object on schema "dbo" if not exist.
IF EXISTS (SELECT * FROM sys.sequences WHERE name = N'MySeq')
	DROP SEQUENCE MySeq;
GO
 
CREATE SEQUENCE MySeq AS tinyint
	START WITH 1
	INCREMENT BY 1
	MINVALUE 1
	NO MAXVALUE
	CYCLE;
GO
 
-- Declare table variable.
DECLARE @score TABLE
(
	Id int NOT NULL PRIMARY KEY,
	Name nvarchar(50) NOT NULL,
	Score int NOT NULL
);
 
-- Insert values into this table.
INSERT @score (Id, Name, Score)
VALUES 
	(NEXT VALUE FOR MySeq, N'Mark Zhou', 90),
	(NEXT VALUE FOR MySeq, N'Dixin Yan', 91),
	(NEXT VALUE FOR MySeq, N'Wei Ye', 89);
 
SELECT Name, Score, NEXT VALUE FOR MySeq OVER (ORDER BY Name DESC)
FROm @score;

5. Get Full Information for a SEQUENCE Object

Use the system view sys.sequences to retrieve all information about a SEQUENCE object, including the current value, type, is cycled, is cached, min value, max value, start value, incremental value and more.

This code retrieves all the created sequences:

SELECT * FROM sys.sequences

And results are shown as below:

The Full CREATE SEQUENCE Syntax

To get the full syntax for CREATE SEQUENCE statement, see this MSDN link.