Friday 13 December 2013

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, EmpName VARCHAR(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.





Posted by: MR. JOYDEEP DAS






No comments:

Post a Comment