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;
(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 –
(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 |
Share your thoughts in the comments
Please Login to comment...