Sequences in SQL Server

Sequences in SQL Server

Sequences in SQL Server

In this post, I will explain all about Sequences in SQL Server end to end along with all appropriate examples. So let’s start –

Sequences in SQL Server

A sequence is a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and can be configured to the restart.

Unlike identify columns values that are generated when rows are inserted an application can obtain the next sequence number without inserting the row by calling the NEXT VALUE FOR function. We can use the sp_sequence_get_range to get the multiple sequence number at once.

Syntax:

CREATE SEQUENCE [schema_name] sequence_name
[ AS [built_in_integer_type | user_defined_integer_type]]
[START WITH start_value]
[INCREMENT BY start_value]
[{MINVALUE [min_value]} | {NO MINVALUE}]
[{MAXVALUE [max_value]} | {NO MAXVALUE}]
[CYCLE | {NO CYCLE}]
[{CACHE [cache_size]} | {NO CACHE}]

As per the above syntax, we have to understand about used arguments as mentioned below –

Sequence_Name: Specify the unique name by which the sequence is known in the database.

As integer_type: We can use here any valid integer type for the sequence i.e. NUMERIC, INT, BIGINT, DECIMAL, SMALLINT, and TINYINT. By default the sequence object uses BIGINT.

START WITH: A sequence returns the first value with this. The start value must be a value less than or equal to the maximum and greater than or equal to the minimum value of the sequence object. The default start value for a new sequence object is the minimum value for an ascending sequence object and the maximum value for a descending sequence object. in other words we can say that specify the first value that the sequence returns. The start_value must be between the range (min_value and max_value) the start_value defaults to the min_value in an ascending sequence and max_value in a descending sequence.

INCREMENT BY: Specify the increment_value of the sequence object when we call the NEXT VALUE FOR function and if increment_value is negative the sequence object is descending otherwise the sequence object is ascending. The increment value can not be 0 (Zero). The default increment for a new sequence object is 1.

MINVALUE | NO MINVAUE: Specify the lower bound for the sequence object. It defaults to the minimum value of the data type of the sequence object i.e. 0(zero) for TINYINT and a negative number for all other data types.

MAXVALUE | NO MAXVALUE: Specify the upper bound for the sequence object. It defaults to the maximum value of the data type of the sequence object.

CYCLE | NO CYCLE: We can use CYCLE if we want the value of the sequence object to restart from the min_value for the ascending sequence object or max_value for the descending sequence object or throw an exception when its min_value or max_value is exceeded. SQL Server uses NO CYCLE by default for new sequence objects.

CACHE [cache_size] | NO CACHE: Specify the number of values to cache to improve the performance of the sequence by minimizing the number of disk I/O (Input/Output) required to generate sequence numbers. SQL Server uses NO CACHE by default for new sequence objects.

For example, if a cache size of 50 is chosen, SQL Server does not keep 50 individual values cached. It only caches the current value and the number of values left in the cache. This means that the amount of memory required to store the cache is always two instances of the data type of the sequence object.

Note: If the cache option is enabled without specifying a cache size, the database engine will select a size. however users should not rely upon the selection being consistent. Microsoft might change the method of calculating the cache size without notice. When created with the CACHE option an unexpected shutdown (such as a power failure) may result in the loss of sequence numbers remaining in the cache.

Uses Of Sequences in SQL Server

We can use a sequence object instead of an identity column in the following cases –

  1. The application requires a number before inserting values into the table
  2. The application requires sharing a sequence of number across multiple tables or multiple columns within the same table
  3. The application requires to restart the number when a specified value is reached
  4. The application requires multiple numbers to be assigned at the same time. Note that we can call the stored procedure sp_sequence_get_range to retrieve several numbers in a sequence at once
  5. The application needs to change the specification of the sequence like the maximum value

Note: We can use the view sys.sequences to get the detailed information of sequences using below query –

Syntax:

SELECT * FROM sys.sequences

(A) SIMPLE SEQUENCE EXAMPLE

We will create a sequence named id_counter with the integer type (INT) which will starts from 11 and increments by 10 using CREATE SEQUENCE statement –

Syntax:

CREATE SEQUENCE id_counter
AS INT
START WITH 11
INCREMENT BY 10

After executing the above query we can see the result as mentioned below –

Command(s) completed successfully.

Now we can see the sequence object under the Programmability>> Sequences as shown in the following snapshot –

Sequences in SQL Server

We can see the current value of the id_counter sequence using below statement –

Syntax:

SELECT NEXT VALUE FOR id_counter

Result:

Current Value
11

As per the above example function NEXT VALUE FOR generates a sequence number from the id_counter sequence object. Each time if we execute the following statement again, we will see that the value of the id_counter will be incremented by 10, which means here if execute it again twice then the result will be 21, for thrice it will be 31, etc….

(B) SEQUENCE IN A SINGLE TABLE EXAMPLE

We will create a new schema named Admissions here first to understand better use of sequence in a single table –

Syntax:

CREATE SCHEMA Admissions

Now we will create a table name Admission using below query syntax –

Syntax:

CREATE TABLE Admissions.Admission (
Adm_ID INT PRIMARY KEY,
Student_ID INT NOT NULL,
Adm_Date DATE NOT NULL)

Now we will create a new sequence object named id_counter that starts 1 and incremented by 1 using below query –

Syntax:

CREATE SEQUENCE Admissions.id_counter
AS INT
START WITH 1
INCREMENT BY 1

Now we will insert few rows using sequence object using bellow queries –

Syntax:

INSERT INTO Admissions.Admission (Adm_ID,Student_ID,Adm_Date)
VALUES (NEXT VALUE FOR Admissions.id_counter,1,GETDATE())
INSERT INTO Admissions.Admission (Adm_ID,Student_ID,Adm_Date)
VALUES (NEXT VALUE FOR Admissions.id_counter,2,GETDATE())
INSERT INTO Admissions.Admission (Adm_ID,Student_ID,Adm_Date)
VALUES (NEXT VALUE FOR Admissions.id_counter,3,GETDATE())

Now we can see the content of the Admissions.Admission table using below query –

Syntax:

SELECT Adm_ID,Student_ID,Adm_Date
FROM Admissions.Admission

Result –

Adm_IDStudent_IDAdm_Date
1118/04/2020
2218/04/2020
3318/04/2020

(C) SEQUENCE IN MULTIPLE TABLE EXAMPLE

We will create a new sequence object rec_counter here first to understand better use of sequence in a single table –

Syntax:

CREATE SEQUENCE Admissions.rec_counter
START WITH 1
INCREMENT BY 1

Then we will create another two tables Admissions.Fee_Receipt and Admissions.Invoice_Receipt here using below query –

Syntax:

CREATE TABLE Admissions.Fee_Receipt (
Receipt_ID INT PRIMARY KEY DEFAULT (NEXT VALUE FOR Admissions.rec_counter),
Order_ID INT NOT NULL,
Full_Receipt BIT NOT NULL,
Receipt_Date DATE NOT NULL,
Note NVARCHAR(250))

CREATE TABLE Admissions.Invoice_Receipt (
Receipt_ID INT PRIMARY KEY DEFAULT (NEXT VALUE FOR Admissions.rec_counter),
Order_ID INT NOT NULL,
Is_Late BIT NOT NULL,
Receipt_Date DATE NOT NULL,
Note NVARCHAR(250))

We can see that there are both tables have the Receipt_Id whose values are derived from the Admissions.rec_counter sequence. Now we will have to insert a few rows into both the tables without supplying the values for the Receipt_Id columns.

Syntax:

INSERT INTO Admissions.Fee_Receipt (Order_ID,Full_Receipt,Receipt_Date,Note)
VALUES (1,1,GETDATE(),’Fee Received while Admission’)

INSERT INTO Admissions.Fee_Receipt (Order_ID,Full_Receipt,Receipt_Date,Note)
VALUES (1,0,GETDATE(),’Fee Received while Admission’)

INSERT INTO Admissions.Invoice_Receipt (Order_ID,Is_Late,Receipt_Date,Note)
VALUES (1,0,GETDATE(),’Invoice Received’)

INSERT INTO Admissions.Invoice_Receipt (Order_ID,Is_Late,Receipt_Date,Note)
VALUES (2,0,GETDATE(),’Invoice Received’)

Now we can see the out put of the both the table using below query –

Syntax:

SELECT * FROM Admissions.Fee_Receipt
SELECT * FROM Admissions.Invoice_Receipt

Output: Here is the output in below snapshot –

Sequences in SQL Server

Sequences are different from the identify columns are not associated with a table. The relationship between the sequences and the table is controlled by applications. In addition a sequence can be shared across multiple tables.

Alter Sequences in SQL Server

We can Alter the sequence using ALTER Statement –

Syntax:

ALTER SEQUENCE [schema_name. ] sequence_name
[ RESTART [ WITH ] ]
[ INCREMENT BY ]
[ { MINVALUE } | { NO MINVALUE } ]
[ { MAXVALUE } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ ] } | { NO CACHE } ]

Or

ALTER SEQUENCE Admissions.rec_counter INCREMENT BY 1

SECURITY OR PERMISSIONS

Requires ALTER permission on the sequence or ALTER permission on the schema. To grant ALTER permission on the sequence use ALTER ON OBJECT in the following format.

Synatx:

GRANT ALTER ON OBJECT::Test.TinySeq TO [AdventureWorks\Larry]

DROP SEQUENCE

We can DROP the existing sequence object from the database –

Syntax:

DROP SEQUENCE [ IF EXISTS ] { [ database_name . [ schema_name ] . | schema_name. ]    sequence_name } [ ,…n ] 

or

DROP SEQUENCE Admissions.rec_counter

Assuming this tutorial post has well-acquainted aspiring to the complete requirement.

Also requesting, if you have any ideas or suggestions for me or you need some important blogs/Videos or guidelines related to SQL Server, Excel, WordPress, SEO, Website technologies then please feel free to write me on below comment section or you can go through the Contact Page. I will definitely come back to you with your desired requirements.

Hope this tutorial post was helpful for you and you liked very well, I am happy to serve you better. If you liked really this tutorial post, please don’t forget to share on social media like – Facebook, Whatsapp, LinkedIn, Twitter, Instagram, etc as it may help some other needy peoples too.

Leave a Reply