Sequence objects are used to sequentially generate numeric values. They were introduced in SQL Server 2012.

Sequence objects are similar to the IDENTITY column in any SQL table. However, unlike the IDENTITY column, they are independent and are not attached to any table. Sequence objects are used both independently and within the DML statements i.e. INSERT, UPDATE and DELETE.

This article will take a detailed look at sequence objects.

Syntax

The syntax for a Sequence object is as follows:

The syntax works as follows:

A Simple Example

Let’s take a look at a simple example of a sequence object. Execute the following script:

In the script, we create a sequence object named NewCounter. The type of this sequence object is integer; it starts from 5 and increments by 5.

To see what our NewCounter sequence object contains, execute the following script:

When you execute the above script for the first time, you will see ‘5’ in the output. As shown below. This is the start value for the counter.

The “NEXT VALUE FOR” statement basically increments the counter.

Execute the following script again:

This time you will see ‘10’ in the output. Each time you execute the above script, the value for the NewCounter sequence will be incremented by 5.

Retrieving Sequence Object Details

To retrieve the details of our newly created sequence, execute the following script:

The script above retrieves all the details of the sequence object such as name, minimum value, maximum value, cycled or not, cached or not, current value, date creation etc. The output of the script above looks like this:

Altering a Sequence

To modify an existing sequence, the ALTER SEQUENCE statement is used. Have a look at the script below:

The above script will modify the existing sequence object ‘NewCounter’ by updating its starting value to 7.

Now if you execute the following statement:

You will see ‘7’ in the output.

Executing the above statement again will return 12 (7+5). This is because we only updated the starting value, the value for INCREMENT BY remains same, therefore 7 plus the increment value 5 will be equal to 12.

Using Sequence Object with INSERT

Sequence objects can be used in combination with INSERT statements to insert values in a sequential manner. For instance, sequence object can be used to insert values for the primary key column.

Let’s create a simple table Students table with three columns Id, StudentName and StudentAge. We will use sequence object to insert a value in the Id column whenever a new record is inserted into the table.

Let’s create a table:

Next, we will create a sequence object with an initial value of 1. We will increment this counter by 1. Execute the following script to create such a sequence object, which we’ve called IdCounter.

Now let’s insert some records in the Students table that we just created. For the Id column of the table we will use the following statement:

The above statement will fetch the next value for the IdCounter sequence object and will insert it into the Students table. The script for inserting records into Students table is as follows:

Select all the records from the Students table, execute the following script:

The output looks like this:

You can see from the output that the Id column contains values from 1 to 5 as provided by the IdCounter sequence object.

Decrementing a Sequence Object

To decrement sequence, set the value for INCREMENT BY to a negative number. Setting INCREMENT BY to -1 decrements the sequence object by 1.

Now execute the following script twice:

You will see 9 in the output. This is because the first time you execute the command above, the value of NewCounter2 sequence object is initialized to 10. Executing the script again decrements it by 1 to 9.

Setting the Min and Max Value for Sequence Objects

You can set the minimum and maximum value that your sequence object can reach. If you try to increment or decrement a sequence object beyond the maximum or minimum values, an exception is thrown.

In the script above, we create a sequence object named ‘NewCounter4’. It has a starting value as well as increment of 10. The minimum value for this sequence object is 10 while the maximum value is 50.

Now if you increment the value of this sequence object beyond 50, an error will be thrown. For instance, executing the script 6 times attempts to set the value of the sequence object NewCounter4 to 60. The maximum value for the sequence object is set at 50, and so an error will be thrown.

The screenshot for the error is as follows:

Incrementing/Decrementing Sequence Objects in Cycle

We saw that when we tried to increment a sequence object beyond its maximum value, an error was thrown. You can use a CYCLE flag to avoid this error. If a CYCLE flag for a sequence is set to true, the value for the sequence object is again set to its starting value , whenever you try to increment or decrement its value beyond the maximum or minimum value.

This is best explained with the help of an example.

Now, if you try to increment the value of the sequence object NewCounter7 6 times, the value of the sequence object will be incremented to 60. This is greater than the maximum value 50. In this case the value of the NewCounter7 will be again set to starting value i.e. 10.

Other great articles from Ben

• Author
• Recent Posts

Ben Richardson

Ben Richardson runs Acuity Training a leading provider of SQL training the UK. It offers a full range of SQL training from introductory courses through to advanced administration and data warehouse training – see here for more details. Acuity has offices in London and Guildford, Surrey. He also blogs occasionally on Acuity’s blog

View all posts by Ben Richardson

Latest posts by Ben Richardson (see all)

• Working with the SQL MIN function in SQL Server - May 12, 2022
• SQL percentage calculation examples in SQL Server - January 19, 2022
• Working with Power BI report themes - February 25, 2021