Sequence with examples in SQL Server

Sequence :
A sequence is a list of numbers, in an ordered manner. For example, {1, 2, 3} is a sequence and {3, 2, 1} is also sequence but a different sequence.

It is a user-defined schema object that produces a list of numbers in accordance to specified value in SQL server.

Syntax :

CREATE SEQUENCE schema_name.sequence_name  
AS integer_type 
START WITH start_value
INCREMENT BY increment_value 
MINVALUE min_value 
MAXVALUE max_value ;

Parameters used :

• sequence_name –
  Define a name for the sequence which is unique in the database.
• AS integer_type –
  Use any integer type for the sequence for example; TINYINT, INT, or DECIMAL. By default, the sequence uses BIGINT.
• START WITH start_value –
  Define the first value that the sequence.
• INCREMENT BY increment_value –
  Define the increment_value of the sequence object to call the NEXT VALUE FOR function, the increment_value cannot be zero.
• MINVALUE min_value –
  Define the lower value for the sequence.
• MAXVALUE max_value –
  Define the upper value for the sequence.

Use the below statement to get the detailed information of sequences in SQL server :

SELECT * 
FROM sys.sequences; 

Example-1 : Creating a simple sequence

CREATE SEQUENCE geeks_num
AS INT
START WITH 10
INCREMENT BY 10; 

Output –

SELECT NEXT VALUE FOR geeks_num; 

Current_value
10

(1 row affected)

Run the following statement again, that the value of geeks_num will be increased by 10.

SELECT NEXT VALUE FOR geeks_num; 

Output –

Current_value
20

(1 row affected)

Example-2 : Using a sequence object in a table.
Let us create a schema named geeksch :

CREATE SCHEMA geeksch;
GO 

And create a new table named geektab :

CREATE TABLE geeksch.geektab
(
geek_id INT PRIMARY KEY, 
DOJ date NOT NULL 
); 

Now, to create a sequence named geek_number that starts with 1 and is increased by 1.

CREATE SEQUENCE geeksch.geek_number 
AS INT
START WITH 1
INCREMENT BY 1; 

Inserting rows into the geeksch.geektab table and use values by the geeksch.geek_number sequence :

INSERT INTO geeksch.geektab(g_id, DOJ)
VALUES(NEXT VALUE FOR geeksch.geek_number, '2019-07-15');

INSERT INTO geeksch.geektab(g_id, DOJ)
VALUES(NEXT VALUE FOR geeksch.geek_number, '2018-04-10'); 

To view the values of the geeksch.geektab table :

SELECT * FROM  geeksch.geektab; 

Output –

g_id	DOJ
1	2019-07-15
2	2018-04-10′

Example-3 : Using a sequence object in multiple tables example.

CREATE SEQUENCE geeksch.g_no
START WITH 1
INCREMENT BY 1; 

Creating a table by using geeksch sequence.

CREATE TABLE geeksch.table1
(id INT PRIMARY KEY
DEFAULT (NEXT VALUE FOR geeksch.g_no),
DOJ DATE NOT NULL,
City NVARCHAR(100) ); 

Here, table has the column id whose values are derived from the geeksch.g_no sequence.

INSERT INTO geeksch.table1(DOJ, City )
VALUES('2019-05-12', 'Delhi');

INSERT INTO geeksch.table1(DOJ, City )
VALUES(  '2019-06-18',  'Delhi'); 

Creating a another table by using geeksch sequence.

CREATE TABLE geeksch.table2
(id INT PRIMARY KEY
DEFAULT (NEXT VALUE FOR geeksch.g_no), 
DOJ DATE NOT NULL, 
City NVARCHAR(100) ); 

Here, table has the column id whose values are derived from the geeksch.g_no sequence.

Let us insert some rows into table2 without values for the id columns :

INSERT INTO geeksch.table2(DOJ, City )
VALUES('2020-02-03','Noida');

INSERT INTO geeksch.table2(DOJ, City )
VALUES('2020-03-14','Noida'); 

Output –

SELECT * 
FROM geeksch.table1; 

output –

id	DOJ	City
1	2019-05-12′	Delhi
2	2019-06-18	Delhi

SELECT * 
FROM geeksch.table2; 

id	DOJ	City
3	2020-02-03	Noida
4	2020-03-14	Noida