SQL Server Sequence

SQL Server Sequence is used to create a sequence object and its properties can also be specified.

A sequence object can be defined as a user-defined object which is bound to the schema that generates a numeric values sequence according to the increment value that is defined at the time of the creation of the sequence.

The numeric values sequence that is generated can be in a descending or ascending order depending upon an already preset incremental value and it can also be started again (cycle) when it reaches its termination limit. Unlike identity columns, the sequences are not associated with some particular tables. In order to get the next value of the sequence generated, the applications can refer to an object of sequence.

The application controls the relationship between tables and sequences. A sequence can be seen as a schema-level object which any user can access. Unlike a procedure, a sequence belongs to no user.

The syntax for creating a Sequence Object is:

In the above-written syntax:

• Name_of_schema: The name_of_schema specifies the schema to which that sequence will belong.
• Type_as_integer: The type_as_integer data type of the Sequence object. Some of the data types supported for Sequence objects are NUMERIC, BIGINT, INT, TINYINT, DECIMAL, and SMALLINT.
  We can any of them according to our requirement:
  • 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
• Starting_value_of_sequence: The starting_value_of_sequence represents the starting value of the sequence. The sequence object is initialized with this value and further incrementation or decrementation is done on this value.
• Incremental_index: The incremental_index represents the value or index by which the starting value of the sequence is going to be increment each time the next value of the sequence object is fetched.
• Minimum_value: The minimum_value represents the minimum value a sequence object can go upto. It acts as a lower bound.
• Maximum_value: The maximum_value represents the maximum value a sequence object can go upto. It acts as an upper bound.
• Size_of_cache: The size_of_cache represents the number of values to be cached to improve the performance of the sequence object.

How to use Sequence in SQL Server?

Let us take an example for a better understanding. Let us create a table named students having three columns named studID, rollNo, Name to store the student ID, roll no, and Name of the student respectively. The command for creating a table in SQL server with the above-mentioned schema will be:

As shown in the image we have successfully created a table having the above-mentioned schema that is three columns two having data type as varchar and one as int.

Now, let us add some data to the students' table. The syntax of the INSERT command in SQL Server to add data in the students' table will be:

As we can see in the image, we have successfully added seven rows to the students' table and the same can be seen in the result of the SELECT query.

Now we add data in the studID column using a sequence object. For this, a sequence object needs to be created. The syntax for the same is:

As shown in the image above we have created a sequence object named stud_seq having the initial value as 101 and it gets incremented by 1 where the value from the sequence object named stud_seq is fetched. For retrieving the next value from the sequence object, the syntax is:

So, we have added the data in the studID column of the students' table using the query:

So this article helps us to understand the working of the Sequence in SQL Server and how to use it according to our problem requirement.