SQL Server Training | Tutorials | How to's

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

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

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 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

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 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

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 !!!