SQL SEQUENCES - Scaler Topics

Overview

SQL Sequences are a database feature that can automatically generate unique numbers for use as ID numbers. This can be helpful when you want to add new data to a database and need a unique identifier for each new entry. Sequences are easy to set up and use and can be customized to meet your specific needs. This article will explore the basics of SQL Sequences and how we can use them to simplify your database management.

What is Sequence in SQL Server?

Sequence is a feature supported by database systems to produce unique numeric values in a series according to specified specifications.

A sequence in SQL server is a user-defined schema-bound object that generates a set or sequence of numbers in which their orders are important.
Some database applications may require unique values in each row of a table. In these cases, sequences come handy for the easy generation of values.
The sequence of numeric values is generated in an ascending or descending order at defined intervals. It can be configured to restart when the numbers get exhausted. Once the cycle reaches the max value the next value will be reset to the minimum value. The cycle option ends up with an error if the sequence is being used for the primary key values as it will not allow the duplicates.
A Sequence is not associated with any table.
A sequence can be referred to generate values with specific increments and intervals on each execution by using NEXT VALUE FOR.

Note :
Some DBMS like MySQL supports AUTO_INCREMENT in place of Sequence. AUTO_INCREMENT allows a unique number to be generated automatically when a new record is inserted into a table. The sequence is similar to AUTO_INCREMENT but has some additional features.

Creating a Sequence in SQL Server

name_of_sequence : The name of the sequence should be unique in the database.

integer_type : This refers to the data type of the Sequence object. Some of the data types supported for Sequence objects are NUMERIC, BIGINT, INT, TINYINT, DECIMAL, and SMALLINT. The default value is BIGINT.

bigint - It ranges from $-9$ , $223$ , $372$ , $036$ , $854$ , $775$ , $808$ to $9,223,372,036,854,775,807$
int - It ranges from $-2,147,483,648$ to $2,147,483,647$
decimal and numeric with a scale of 0
smallint - It ranges from $-32,768$ to $32,767$
tinyint - It ranges from 0 to 255

initial_value : It is the value from where the sequence starts. The initial_value should be greater than or equal to the minimum value and less than equal to the maximum value. By default, the initial_value in an ascending sequence is the minimum_value and is the maximum_value in a descending sequence.

incremental_value : Value by which sequence will increment itself. The value can never be zero.

minimum_value : Minimum value is the lower limit of the sequence, that is the value of the sequence cannot be lesser than it. The value could be either negative or positive.

maximum_value : Maximum value is the upper limit of the sequence, that is the value of the sequence cannot be greater than it. The value could be either negative or positive.

cycle : When the sequence reaches its set_limit (maximum or minimum value and not from the start value), it starts from the beginning.

nocycle : In the case of nocycle once the sequence reaches its set_limit it throws an exception and the sequence does not proceed any further. Nocycle is the default set value while creating a sequence.

cache[size] | no cache : Improves application performance using sequence objects by minimizing the disk IOs required to generate sequence numbers. SQL Server pre-allocates the number of sequence numbers specified by the CACHE.

Examples of Sequence in SQL Server

Example: 1 - Creating a Simple Ascending Sequence

Explanation :
The query will create a sequence named seq. The sequence starts from 1 and is incremented by 3, having a maximum value of 27.

Output :

creating a simple ascending sequence

Example: 2 - Descending Sequence

Explanation:
The query will create a descending sequence named dseq. Sequence starts from 40 and is incremented by -3 (decremented by 3) up to 10. After that, the cycle heads over to 50 and is again incremented by -3 up to 11 (minvalue is 10).

Output :

Descending Sequence

Example: 3 - Using Sequence with a Table

Explanation :
We use the CREATE TABLE command to create a table named Train which has two columns TRAININGID, TRAININGNAME to store the training ID (datatype int), and training name (datatype varchar).

We add data directly in the TRAININGNAME column and use a sequence object in the TRAININGID column. A sequence object TrainingSequence is created using CREATE SEQUENCE command.

The sequence has an initial value of 10 and it gets incremented by 5 where the value from the sequence object named TrainingSequence is fetched.

For retrieving the next value from the sequence object, the syntax is: NEXT VALUE FOR TrainingSequence. The maximum value that can be retrieved is 50. Once the value of the sequence exceeds the MAXVALUE it restarts the sequence from the MINVALUE 10.

Output :

Use sequence with a Table

Learn More about Advanced Concepts of SQL

To get in-depth knowledge on other SQL topics head over to SQL

Conclusion

A sequence is a set of integers 1, 2, 3... that are generated in order of demand. They are used in databases and are easily generated using SQL SEQUENCES.
The order of the attributes and flags under SEQUENCE is dynamic. They are optional and can have any order.
The numeric values generated in a sequence can be in a descending or ascending order depending upon the incremental value. On reaching its termination limit, it may restart.
A sequence is a schema-level object that any user can access.