Sequence Numbers In SQL Server Denali (MSSQL 2012)

SEQUENCE : New User Defined Object has been introduced in SQL Server Denali. With this object you can define numeric sequence. You Can Define

• Sequence Start Number
• Sequence Increment By
• Sequence Minimum Value
• Sequence Maximum Value
• Do we want to Repeat The Sequence From Start Number Again When Sequence Reaches Maximum Value ( CYCLE).

Everything looks like IDENTITY Column In A Table. But Main Advantages Are

• Sequences Are Schema Bound Objects i.e You Can Create Sequence Under Schemas.
• Sequences Are Not Bound To a Particular Table, Mean You can use One Sequence with Many Tables.
• You Can Define Maximum Value For A Sequence.
• You Can Define CYCLE of Sequence

Let’s See Working Of SEQUENCE.

Create Table With Following Script

Create Table BillGenerate(BillId int,BillAmount Decimal(10,2))
Go

How to Create A Sequence ?

Create Sequence BillNo
AS Int
   Start With 1
   INCREMENT By 1
Go

How To Use Sequence ?

Insert Into BillGenerate(BillId,BillAmount)
Values(Next Value For BillNo,240.00)
Go
Insert Into BillGenerate(BillId,BillAmount)
Values(Next Value For BillNo,900.00)
Go
Insert Into BillGenerate(BillId,BillAmount)
Values(Next Value For BillNo,760.00)
Go

Let See The Data In Table

Select * From BillGenerate

Output :

BillId	BillAmount
1	240
2	900
3	760

Sequence Can Be Inserted Into Table Using Variable As Well. See Following

Declare @BillId Int
Set @BillId=Next Value For BillNo --Assigning SEQUENCE To Variable
Insert Into BillGenerate Values(@BillId,450.00)
Go
Select * from BillGenerate
Go

Output :

BillId	BillAmount
1	240
2	900
3	760
4	450

---

Creating Sequence Using CYCLE.

Create Table With Following Script.

Create Table CheckCycleSequence(Id Int,Desciption Varchar(100))
Go

Create Sequence With CYCLE

Create Sequence MySeq
As Int
Start With 1
Increment By 1
MinValue 1
MaxValue 4
CYCLE
Go

So Defining Sequence with CYCLE, Means If the sequence reaches MAXValue Defined in SEQUENCE then sequence is again started with Start Number.

Now Lets Insert Some Data Into Table

Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item1')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item2')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item3')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item4')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item5')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item6')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item7')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item8')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item9')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item10')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item11')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item12')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item13')
Insert Into CheckCycleSequence Values(Next Value For MySeq,'Item14')
Go

Check The Result Of Table

Select * From CheckCycleSequence
Go

Output:

Id	Desciption
1	Item1
2	Item2
3	Item3
4	Item4
1	Item5
2	Item6
3	Item7
4	Item8
1	Item9
2	Item10
3	Item11
4	Item12
1	Item13
2	Item14

As you can see Value of ID column is Repeating Every After Exhausting Value Of SEQUENCE.

---

Using SEQUENCE In Multiple Tables

Create Sequence

Create Sequence MultiUse
As Int
START With 1
INCREMENT By 1
Go

Create Tables

Create Table Employee_Account(Id Int,EmpName Varchar(50))
Go
Create Table Employee_Operations(Id Int,EmpName Varchar(50))
Go
Create Table Employee_Management(Id Int,EmpName Varchar(50))
Go

Insert Records

Insert Into Employee_Account Values (Next Value For MultiUse,'Rajesh Sharma')  -- Table 1
Insert Into Employee_Operations Values (Next Value For MultiUse,'Savy John')   -- Table 2
Insert Into Employee_Management Values (Next Value For MultiUse,'J D Sharma')  -- Table 3
Insert Into Employee_Account Values (Next Value For MultiUse,'Praveen Kumar')  -- Table 1
Insert Into Employee_Operations Values (Next Value For MultiUse,'Seb Noval')   -- Table 2
Insert Into Employee_Management Values (Next Value For MultiUse,'P R Chopra')  -- Table 3
Go

The above Insert Records Script Inserts Database Into Tables Randomly.

Now Let’s Check The Tables Data

Select * From Employee_Account
Select * From Employee_Operations
Select * From Employee_Management
Go

Output :

So Sequence Is Maintained Even If You Use SEQUENCE with Multiple Tables.