Powered by Blogger.

Saturday, February 15, 2014

Sequence in SQL Server 2012



In this post we will discuss about What is Sequence in SQL Server 2012? How to create a sequence in sql server 2012? And How to use sequence in SQL Server 2012?

Also you can check out my previous posts on:

- Rename table name or column name in sql server 2008

- Show confirmation message in gridview for delete in Asp.net

- How to add Eval for hyperlink in gridview in asp.net?

What is Sequence in SQL Server 2012?
A Sequence is an object which is introduced in SQL Server 2012. A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to restart (cycle) when exhausted.

This is like a global sequence generator which is not specific to any table rather it can be used in any table.

How to create a sequence in sql server 2012?
A Sequence can be created using SQL Server Management Studio as well as using TSQL Statement.

Through SQL Server Management Studio:
To Create a Sequence through SQL Server Management Studio, From your database -> Programmability -> Sequences -> Then Right click and Click on Create New Sequence.

Through TSQL Statements:

CREATE SEQUENCE MyDBSequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

This will create a sequence that will start from 1 and it will go on by incrementing 1 everytime.

How to use sequence in SQL Server 2012?
Now we can use the Sequence like below:

CREATE TABLE Users
 (
  UserID        INT NOT NULL,
  UserName    VARCHAR(256) NOT NULL
  )
go

INSERT Users (UserID, UserName)
VALUES
(NEXT VALUE FOR MyDBSequence, 'Bijay'),
(NEXT VALUE FOR MyDBSequence, 'Sanjay'),
(NEXT VALUE FOR MyDBSequence, 'Tamanna')

Now if you will retrieve the data from the Users table then the ID will come as 1,2,3.

Now suppose you want to use in another table then it will be like below:

CREATE TABLE Students
 (
  StudentID        INT NOT NULL,
  StudentName    VARCHAR(256) NOT NULL
  )
go

INSERT Students (StudentID, StudentName)
VALUES
(NEXT VALUE FOR MyDBSequence, 'Raj'),
(NEXT VALUE FOR MyDBSequence, 'Gopal'),
(NEXT VALUE FOR MyDBSequence, 'Pooja')

Here if you will retrieve the data from the Students table then the StudentID will come as 4,5,6.



0 comments

Post a Comment