Skip to content
Related Articles

Related Articles

Improve Article

Sequence with examples in SQL Server

  • Last Updated : 03 Sep, 2020

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_idDOJ
12019-07-15
22018-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 DOJCity
12019-05-12′Delhi
22019-06-18Delhi
SELECT * 
FROM geeksch.table2; 
idDOJCity
32020-02-03Noida
42020-03-14Noida

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :