Sequence Object in SQL Server 2012

One of the many features that Microsoft is introducing to the new SQL Server 2012 is Sequence object. In this article I am going to illustrate how to create and use the Sequence object.

Sequences generated by the Sequence object are similar to identity property values; however, it is not tied to one table.

Sequence object can be referenced by applications directly and can also be shared by many rows or many tables.

Creating the Sequence

Step 1

Let’s create a database where we can create the sequence object as shown below.

USE [master]
GO
DROP DATABASE [Lab_Sequence_Test]
GO
Create database Lab_Sequence_Test;
GO
 

Step 2

Create a sequence object InvoiceNumber that would start the Invoice number from 1000 and increment by 5 every time the sequence object is accessed.

use Lab_Sequence_Test;
GO
Create SEQUENCE [dbo].[InvoiceNumber]
as int
START WITH 1000
INCREMENT BY 5;
 

Step 3

Now, the sequence value can be generated simply by using the NEXT VALUE FOR clause, as shown below. 

SELECT NEXT VALUE FOR [dbo].[InvoiceNumber]

Result: 1000

You can generate the sequence with column name as shown below.

SELECT NEXT VALUE FOR [dbo].[InvoiceNumber]  as SequenceValue 

Result

Generate the sequence with column name

Step 4

The generated sequence can be used directly when inserting rows to a table as shown below.

USE [Lab_Sequence_Test]
GO
if (object_id('LLCInvoices') is not NULL)
BEGIN
       DROP TABLE LLCInvoices
END
ELSE
BEGIN
CREATE TABLE [dbo].[LLCInvoices](
       [InvoiceID] [int] NULL,
       [Name] [varchar](100) NULL
) ON [PRIMARY]
END
GO

--Insert values

Insert into LLCInvoices (InvoiceID, Name) VALUES  (NEXT VALUE for [dbo].[InvoiceNumber],'Anderson')
Insert into LLCInvoices (InvoiceID, Name) VALUES  (NEXT VALUE for [dbo].[InvoiceNumber],'Neo')

--Query Table
Select * from LLCInvoices

Result

Inserting rows into a table

Step 5

Before accessing the NEXT VALUE of the sequence object, if you want to know the current value, you can access it by querying the DMV sys.sequences.

SELECT current_value 
FROM sys.sequences
WHERE name = 'InvoiceNumber' 

Result:

Querying the DMV

As I mentioned in the beginning of the article, the sequence can be shared by many tables. Let’s consider creating a sequence USPopulation that can be shared by all the US states.

Sharing Sequences

Step 1

Create the sequence for US Polulation.

Create SEQUENCE [dbo].[US_Population]
as Bigint
START WITH 312780968 
INCREMENT BY 1;

Note:  The sequence start is 312780968 (rough Current population)

Step2

Create tables for each state in the US. Here I am going to create tables for only  the tristate area.

Create table NJ_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
Create table NY_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
Create table CT_Population
(USPopulationID bigint, PopulationID bigint identity(1,1), FirstName varchar(100),
LastName varchar(100))
 

Step3

Insert a few rows into each table simulating data for new borns in each state.

Insert into NJ_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Anderson', 'Smith')
Insert into NY_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Amanda', 'Saunders')
Insert into NJ_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Kelly', 'Shikari')
Insert into NJ_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Sarah', 'Parker')
Insert into NY_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'July', 'cruz')
Insert into CT_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'William', 'Benz')
Insert into CT_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Kate', 'Johnston')
Insert into NJ_Population (USPopulationID, FirstName,LastName) 
VALUES  (NEXT VALUE for [dbo].[US_Population],'Len', 'cucino')
 

Step 4

Let’s query all the 3 tristate area tables to examine the sequence numbers. From the output you can see that the sequence generated by the sequence object USPopulation is shared by all three tables and none of the tables have duplicate sequence values.

SELECT * from NJ_Population
SELECT * from NY_Population
SELECT * from CT_Population

Result

The sequence generated by the sequence object is shared by all three tables

Note: This article is written using SQL Server 2012 RC0 version.

The sequence generated by the sequence object can be recycled and the same sequence numbers can be re-generated using CYCLE as an argument when creating the sequence. The default option is NO CYCLE.

Recycling Sequences

Step 1

Let’s create the sequence object  with a maximum value of 255 and that can be recycle again when generating sequence numbers.

 
Create SEQUENCE [dbo].[ReUsable_Sequence]
as tinyint
START WITH 252
INCREMENT BY 1
MINVALUE  0
MAXVALUE  255
CYCLE
 

Step 2

Let’s generate some sequence  numbers and see if the generated number is recycled.

SELECT NEXT VALUE FOR [ReUsable_Sequence]
SELECT NEXT VALUE FOR [ReUsable_Sequence]
SELECT NEXT VALUE FOR [ReUsable_Sequence]
SELECT NEXT VALUE FOR [ReUsable_Sequence]
SELECT NEXT VALUE FOR [ReUsable_Sequence]
SELECT NEXT VALUE FOR [ReUsable_Sequence]
 

Result:

See if the generated number is recycled

Step 3

The status of the sequence object can be viewed by querying the DMV sys.sequences as shown below.

SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'ReUsable_Sequence'

Result:

The status of the sequence object

Sometimes it is necessary to get a range of sequence numbers for your application or object. For this, SQL Server provides a system stored procedure sp_sequence_get_range.

Let’s generate a range of 10 sequence numbers from the Sequence object Reusable_Sequence as shown below.

Example:

declare @range_first_value sql_variant
declare @startingrangeout sql_variant  
declare @rangesize bigint
set @rangesize =10
 
EXEC sp_sequence_get_range
@sequence_name = N'dbo.ReUsable_Sequence',
@range_size = @rangesize ,
@range_first_value=  @startingrangeout OUTPUT ;
 
SELECT @startingrangeout as StartingNumber

Result:

Generate a range of 10 sequence numbers

The status of the sequence object can be viewed by querying the DMV sys.sequences as shown below.

SELECT Name,start_value,minimum_value,maximum_value ,current_value
FROM sys.sequences
WHERE name = 'ReUsable_Sequence'

The status of the sequence object

As you can see, you can use the sequence numbers from 2 to 11 for your application.

To improve the performance of the Sequence object, SQL Server provides an option called CACHE; when used a range of value is cached in memory.

CREATE SEQUENCE MySeq
    AS int 
    START WITH 2
    INCREMENT BY 3
    MINVALUE 2
    MAXVALUE 500
    CYCLE
    CACHE 10
 

In the above example, ten values will be cached in memory.

Conclusion

This article illustrated how to create a sequence object, generate sequence numbers, and how to use these generate numbers in inserting rows and sharing with many tables.

See all articles by MAK