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 |