SEQUENCE a new Feature of MS SQL 2012

Introduction

A long time developer's demands for an object that can generate sequential number like ORACLE have. After long journey with MS SQL server MS finally think about it and provide SEQUENCE objects in MS SQL Server 2012.

In this article I am trying to understand the feature of SEQUENCE objects of MS SQL 2012.

What About IDENTITY

As the developers knows INDENTITY columns very well (it starts from MS SQL 2000) but there are lot of difference with SEQUENCE.

First we have to understand although the both IDENTITY and SEQUENCE generate sequential number but there are certain differences between them.

SEQUENCE is a database object but IDENTITY columns are tied to table. That means we can use the SEQUENCE objects in entire database. A single SEQUENCE object can be used with multiple Table objects to insert sequential values.

We can use SEQUENCE objects with T-SQL statement also.

How we can Create and use the SEQUENCE object

To use the SEQUENCE objects we must first create it

CREATE SEQUENCE [dbo].[MySequence] AS INT

 START WITH 1

 INCREMENT BY 1

GO

CREATE TABLE dbo.Tbl_Employee(ID INT, EmpNameVARCHAR(50))

GO

INSERT INTO dbo. Tbl_Employee 

VALUES (NEXT VALUE FOR dbo.MySequence,'Joydeep Das'),

       (NEXT VALUE FOR dbo.MySequence,'Archita Dutta'),

       (NEXT VALUE FOR dbo.MySequence,'Rajesh Das')

GO

SELECT * FROM dbo.Tbl_Employee WITH(NOLOCK)

GO

ID        Name

———– —————————–

1         Joydeep Das

2         Archita Dutta

3         Rajesh Das

Regenerate SEQUENCE Number

ALTER SEQUENCE dbo.MySequence

RESTART WITH 1 ;

GO

SELECT (NEXT VALUE FOR dbo.MySequence)

                           AS SequenceValue

GO

SequenceValue
———–
1

Get the Current value of SEQUENCE

SELECT Current_Value

FROM SYS.Sequences

WHERE name='MySequence'

GO

Current_Value
——————
1

Hope you like it.