What is Sequence Object in SQL Server?. The Complete Microsoft SQL Server Developer Course 2023 [Videos].

In this article, I am going to discuss the Sequence Object in SQL Server with Examples. Please read our previous article before proceeding to this article where we discussed Identity Column in SQL Server with some examples. In SQL Server, we can create an auto number field by using sequences like identity. As part of this article, we are going to discuss the following concepts.

1. What is a Sequence Object in SQL Server?
2. Understanding Properties of Sequence Object.
3. How to Create an Incrementing Sequence Object in SQL Server?
4. How to Generate the Next Sequence Value in SQL Server?
5. Retrieving the current sequence value in SQL Server.
6. How to use the Sequence Object value in an Insert Statement in SQL Server?
7. How to specify the Min and Max value of Sequence?
8. Creating the decrementing Sequence.
9. How to Recycling the Sequence values in SQL Server?
10. How To improve the performance of Sequence Object using Cache Option?
11. Using SQL Server Graphical User Interface (GUI) to create the sequence object.

What is a Sequence Object in SQL Server?

A sequence is an object in SQL Server that is used to generate a number sequence. This can be useful when we need to create a unique number to act as a primary key. 

The Sequence Object is one of the new features introduced in SQL Server 2012. A sequence is a user-defined object and as its name suggests it generates a sequence of numeric values according to the properties with which it is created. It is similar to the Identity column, but there are many differences between them that we will discuss in our next Video. But the most important point to keep in mind is that the Sequence Object in SQL Server is not limited to a column or table but is scoped to an entire database.

Syntax of Sequence Object in SQL Server:

Properties of Sequence Object:

1. DataType: Built-in integer type (tinyint, smallint, int, bigint, decimal, etc…) or user-defined integer type. The default is bigint.
2. START WITH: The Start With Value is nothing but the first value that is going to be returned by the sequence object
3. INCREMENT BY: The Increment by value is nothing but the value to increment or decrement by the sequence object for each row. If you specify a negative value then the value is going to be decrement.
4. MINVALUE: It specifies the value for the sequence object
5. NO MINVALUE: It specifies that there is no minimum value specified for the given sequence object.
6. MAXVALUE: Maximum value for the sequence object
7. NO MAXVALUE: It means that there is no maximum value specified for the sequence.
8. CYCLE: It specifies that reset the sequence object when the Sequence Object reached the maximum or minimum value. 
9. NO CYCLE: When you specify the No Cycle option, then it will throw an error when the Sequence Object reached its maximum or minimum value.
10. CACHE: Cache sequence values for performance. The default value is CACHE.
11. NO CACHE: As the name says, if you specify the NO CACHE option then it will not cache the sequence numbers.

Note: If you have not specified either Cycle or No Cycle then the default is No Cycle in SQL Server.

Creating an Incrementing Sequence Object in SQL Server:

The following code creates a Sequence object in SQL Server that starts with 1 and increments by 1

Once we execute the above query it will create the SequenceObject in the Sequence folder as shown below.

How to Generate the Next Sequence Value in SQL Server?

Once we created the sequence object, now let see how to generate the sequence object value. To generate the sequence value in SQL Server, we need to use the NEXT VALUE FOR clause as shown below

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

Output: 1

Every time you execute the above select statement the sequence value will be automatically incremented by 1. Here I executed the above Select Statement 5 times, so I got the current sequence object value as 5 as expected.

How to Retrieving the current sequence value in SQL Server?

If you want to see the current Sequence value before generating the next, use sys.sequences
SELECT * FROM sys.sequences WHERE name = "SequenceObject"

Alter the object to reset the sequence value: 
ALTER SEQUENCE [SequenceObject] RESTART WITH 1

To ensure the value now going to starts from 1, select the next sequence value as shown below.
SELECT NEXT VALUE FOR [dbo].[SequenceObject]

How to use the sequence value in an Insert statement in SQL Server? 

Creating the Decrementing Sequence in SQL Server:

The following code creates a Sequence that starts with 100 and decrements by 1

Specifying MIN and MAX values for the sequence:

If you want to specify the minimum and maximum value for the sequence object then you need to use the MINVALUE and MAXVALUE arguments of the Sequence Object respectively. Let us understand this with an example.

Step 1: Create the Sequence

Step 2: Retrieve the next sequence value. The sequence value starts at 100. Every time we call the NEXT VALUE FOR, the value will be incremented by 10.

SELECT NEXT VALUE FOR [dbo].[SequenceObject]

If we call the NEXT VALUE FOR, when the value reaches 150 (MAXVALUE), we will get the following error.

How to Recycling the Sequence values in SQL Server?

When the sequence has reached its maximum value, and if we want to restart from the minimum value, set CYCLE option

At this point when the sequence object has reached its maximum value, and if we ask for the NEXT VALUE, the sequence object starts from the minimum value again which in this case is 100.

How To Improve the Performance of Sequence Object in SQL Server?

If you want to improve the performance then you can Cache the Sequence object values by using the CACHE option in SQL Server. When the Sequence Object values are cached, then they are simply read from the memory instead of the disk which will improve the performance of the application. When the cache option is specified, if you want then you can also specify the size of the cache that is the number of values to be cached.

The example below going to create the sequence object with 10 values in the cached. When the 11th value is requested, then the next 10 values will be cached again.

Drop Sequence object

Once we have created our sequence in SQL Server, we might find that we need to remove it from the database.
Syntax: DROP SEQUENCE sequence_name
sequence_name: The name of the sequence that you want to drop.

Example: Drop Sequence SequenceObject
This would drop the sequence called SequenceObject from the database.

Associate Sequence object to a table

Check the records inserted in the table

SELECT * FROM dbo.Customer WITH(NOLOCK)

Using SQL Server Graphical User Interface (GUI) to create the sequence object: 

1. Expand the database folder
2. Expand Programmability folder
3. Right-click on the Sequences folder
4. Select New Sequence