Sequence in SQL server

This SQL Server tutorial explains, What is Sequence in SQL Server? How to create a sequence in SQL server? And How to use sequence in SQL Server? We will also discuss, the difference between Sequence and Identity in SQL Server.

Sequence in SQL Server

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.

Create a sequence in SQL Server

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?

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.

Difference between Sequence and Identity in Sql Server

Let us discuss some of the difference between Sequence and Identity in Sql Server.

Below are some of the differences between Sequence and Identity Column in SQL Server:

  • Identity columns are specific to tables inside a database, whereas Sequence is not for any table specific, it can be used in the whole database.
  • Identity columns values generated when a row inserted to a table, but you can get the sequence number before inserting a row.
  • You can not change the value of the Identity column once inserted, but the sequence number can be changed after insertion into the table.
  • Identity column guarantees uniqueness inside the table, but in the case of Sequence,e you need to create a unique index on the column.
  • In the case of the Identity column, you cannot define the minimum and maximum values, whether to allow cycling and caching options or not. But these things can be archived through Sequence in SQL Server 2008.

You may like following sql server tutorials:

I hope this SQL server tutorial explains, a sequence in SQL Server, how to create a sequence in SQL server? How to use a sequence in SQL server and finally, we will discuss the difference between Sequence and Identity in SQL Server.

Donwload Hub site pdf

Download SharePoint Online Tutorial PDF FREE!

Get update on Webinars, video tutorials, training courses etc.

>