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