Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted February 15, 2012

Sequence Object in SQL Server 2012

By Ananthakumar Muthusamy

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
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
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
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
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
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
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
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
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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM