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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.