Database Simplified Headline Animator

Database Simplified

Saturday, 1 October 2011

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 :

image

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

No comments:

Post a comment