Loading

Microsoft SQL Server

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

In this Video, I am going to discuss the Sequence Object in SQL Server with Examples. Please watch our previous Video before proceeding to this Video 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 Video, 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:
CREATE SEQUENCE [schema_name . ] sequence_name
[ AS [ built_in_integer_type | user-defined_integer_type ] ]
[ START WITH <constant> ]
[ INCREMENT BY <constant> ]
[ { MINVALUE [ <constant> ] } | { NO MINVALUE } ]
[ { MAXVALUE [ <constant> ] } | { NO MAXVALUE } ]
[ CYCLE | { NO CYCLE } ]
[ { CACHE [ <constant> ] } | { NO CACHE } ]
[ ; ]
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

CREATE SEQUENCE [dbo].[SequenceObject]
AS INT
START WITH 1
INCREMENT BY 1

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

Creating an Incrementing Sequence in SQL Server
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? 
CREATE TABLE Employees
(
Id INT PRIMARY KEY,
Name NVARCHAR(50),
Gender NVARCHAR(10)
)
-- Generate and insert Sequence values
Select the data from the table as SELECT * FROM Employees



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

CREATE SEQUENCE [dbo].[SequenceObject]
AS INT
START WITH 100
INCREMENT 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

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 100
INCREMENT BY 10
MINVALUE 100
MAXVALUE 150

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.

Specifying MIN and MAX values for the sequence

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

ALTER SEQUENCE [dbo].[SequenceObject]
INCREMENT BY 10
MINVALUE 100
MAXVALUE 150
CYCLE

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

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
CACHE 10
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
CREATE TABLE dbo.Customer (
ID INT DEFAULT(NEXT VALUE FOR DBO.SequenceObject),
Name VARCHAR(100)
)
GO




Check the records inserted in the table

SELECT * FROM dbo.Customer WITH(NOLOCK)

Sequence Object in SQL Server 2012

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

Sequence Object in SQL Server

See All

Comments (508 Comments)

Submit Your Comment

See All Posts

Related Posts

Microsoft SQL Server / Blog

What is SQL Server?

The SQL Server is a relational database management system (RDBMS) which is developed by Microsoft. It is also called MS SQL Server. This product is built on the basic function of storing, retrieving data as required by other applications. It can be run either on the same computer or on another across a network.
15-Feb-2022 /26 /508

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Database in SQL Server?

In this article, I am going to discuss Creating Altering and Deleting Database in SQL Server with examples. Please read our previous article where we discussed how to connect to SQL Server Database using a client tool called SQL Server Management Studio. As part of this article, we are going to discuss the following pointers.
15-Feb-2022 /26 /508

Microsoft SQL Server / Blog

How to Creating Altering and Deleting Tables in SQL server?

In this article, I am going to discuss Creating Altering and Deleting Tables in SQL Server with examples. Along the way, we will also discuss the Data Definition Language in detail. Please read our previous article before proceeding to this article where we discussed the Creating Altering and Deleting Database in SQL Server with examples. As part of this article, we are going to discuss the following important points.
15-Feb-2022 /26 /508