Sequence with examples in SQL Server

Sequences in SQL Server are versatile tools used to generate unique numeric values in a controlled manner. Unlike identity columns, sequences are independent of tables and can be shared across multiple tables or used in various parts of a database application.

In this article, We will learn about Sequence in SQL Server along with the examples and so on.

Sequence in SQL Server

• A Sequence in SQL Server is a user-defined, schema-bound object that generates a sequence of numeric values according to a set of rules.

  It is used primarily for generating unique numbers, typically for surrogate keys or other unique values in database applications.

Key Features of Sequences

• Independent of Tables: Unlike identity columns, sequences are not tied to a specific table. You can use the same sequence across multiple tables or in different parts of your database.
• Customizable: You can define the starting value, increment, maximum, and minimum values, and whether the sequence should cycle (restart after reaching the maximum or minimum value).
• Caching: Sequences support caching of values to improve performance by reducing disk I/O.

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; 

Examples of Sequence in SQL Server

Example 1:

To generate a sequence of numbers, we can use the CREATE SEQUENCE statement in SQL Server. Below is an example of how to create a sequence named geeks_num that starts at 10 and increments by 10 each time it’s called.

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

SELECT NEXT VALUE FOR geeks_num; 

Output:

(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:

The following example demonstrates how to create a schema named geeksch and a table named geektab within that schema.

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:

Example 3:

Below is the SQL command to create a sequence named g_no within the geeksch schema:

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:

SELECT * 
FROM geeksch.table2; 

Output:

Conclusion

Sequences in SQL Server provide an efficient, flexible way to generate unique numbers, whether for primary keys or other purposes. They are especially valuable in complex database environments where unique values are needed across multiple tables or operations.