How to Create and use Sequence in SQL Server ?

Leave a comment (6) Go to comments

Sequence can be created either

  1. Using TSQL statement or by
  2. Using SQL Server Management Studio (SSMS)

Note : Sequence is an object that has start value, increment value and end value defined in them and this sequence can be added to a column whenever required rather than defining an identity column individually for tables.

Lets take a quick look,

How to create a SEQUENCE using TSQL Statements

  • Sequence can be created using a Create SEQUENCE Syntax
------ Create a SEQUENCE object on schema "dbo" by the name of TEST_Sequence
CREATE SEQUENCE TEST_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE

Lets take a quick look, How to use to SEQUENCE in populating values in table

--- Creating a Table Named Customer
CREATE TABLE Customer
 (
  Id        INT NOT NULL,
  Name    VARCHAR(100) NOT NULL
  )
go
----Populating Customer table, using TEST_Sequence to generate the Id column:
INSERT Customer (Id, Name)
VALUES 
(NEXT VALUE FOR TEST_Sequence, 'Ram'),
(NEXT VALUE FOR TEST_Sequence, 'Rita'),
(NEXT VALUE FOR TEST_Sequence, 'Ron')

Lets’ Look at results, which we inserted using SEQUENCE

---Selecting Records form Customer Table
SELECT * FROM Customer

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE



Note, Sequence are not dependent on tables, these is global to a database. Let’s create a new table named Student and use the same Sequence to understand this Concept

--- Creating a New Table Named Student
CREATE TABLE Student
 (
  Id        INT NOT NULL,
  Name    VARCHAR(100) NOT NULL
  )
go
/*
Populating Customer table, 
using same TEST_Sequence to generate the Id column, 
which we used for Cutomer table in past
*/
INSERT Student(Id, Name)
VALUES 
(NEXT VALUE FOR TEST_Sequence, 'Ram'),
(NEXT VALUE FOR TEST_Sequence, 'Rita'),
(NEXT VALUE FOR TEST_Sequence, 'Ron')
go
select * from Student

OUTPUT of above Code

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE

So every time when you will use NEXT VALUE FOR TEST_Sequence, we will get a new value for example

---Selecting / Printing NEXT value of SEQUENCE
---- Every time we will get a New Value
select NEXT VALUE FOR TEST_Sequence

OUTPUT

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE

How to Alter a SEQUENCE ?

-- Altering a SEQUENCE and starting that with 101
ALTER SEQUENCE TEST_Sequence
    RESTART WITH 501

How to Change the MAX and MIN value of a SEQUENCE ?

/*
---- Altering SEQUENCE and specifying start as 501 and MAX as 511,
---- Once the MAX Value is reached, SEQUENCE numbers will be restarted from MINVALUE
*/
ALTER SEQUENCE TEST_Sequence
    MINVALUE 501
    MAXVALUE 511
    CYCLE

Verification of MAX value and CYCLE Clause

--- Selecting Multiple Next Values at One GO
SELECT *,NEXT VALUE FOR TEST_Sequence as NextId
FROM Student

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE

NOTE: You might get the following Error, incase you specifying the range which is not covering the current value, in our case, the current value is 13 and we specified 501 is minimum, which is logical incorrect, Thus we got this error. In that case, you might wanted to Restart the SEQUENCE with a new number using the following code.

 ---  Restarting the TEST_Sequence start value as 501

ALTER SEQUENCE TEST_Sequence
    RESTART WITH 501

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE


How to Check the Current Value of the SEQUENCE ?

---- Selecting Current Value of a Specified SEQUENCE name TEST_SEQUENCE
SELECT current_value FROM sys.sequences WHERE name='TEST_Sequence'

How to Restart a SEQUENCE ?

-- Altering a SEQUENCE and starting that with 101
ALTER SEQUENCE TEST_Sequence
    RESTART WITH 501

How to Drop a SEQUENCE ?

--- Dropping a SEQUENCE
DROP SEQUENCE TEST_Sequence

How to create a SEQUENCE using SSMS ?

SSMS >>> Databases >>> User Database >>> Programmability >>> SEQUENCE>>> Right Click and you will get a new SEQUENCE Option as shown in figure below

How to Create and use Sequence in SQL Server ? use to SEQUENCE in populating values in table Sequence are not dependent on tables Restart a SEQUENCE in sql server How to create a SEQUENCE using TSQL Statements How to Change the MAX and MIN value of a SEQUENCE How to Alter a SEQUENCE Drop SEQUENCE in sql server Create and use Sequence in SQL Server create a SEQUENCE using SSMS Check the Current Value of SEQUENCE

Click Here to know, Difference between Identity and Sequence

If you liked this post then,

Subscribe to this Blog via Email:

Click here to Subscribe to FREE email updates from "DBATAG ", so that you do not miss out anything on SQL Server !!!

EOF - How to Create and use Sequence in SQL Server ?, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

6 Comments.

  1. Which version of sql server support this ? In sql server 2008, I am getting an error when I use

    Create Sequence …

  2. witch version is this sql server because i can’t find the sequence in sql server 2008.
    It’s an installation problem?
    thanks for help

    • this is in SQL Server Denali, which future upcoming version for SQL Server. currently it’s available for user testing.

  3. Gonzalez , This would be very useful in case you want to have a Globally Unique Identifier in your database.

  4. Thanks for the informative article. One question, buy why would I want to use this. Can you provide a production example?

  5. タオ チューン

    Nice introduction! Keep up your good works!

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.