Sequence in SQL Server

Sequence Object in SQL Server

In this article, I am going to discuss the Sequence in SQL Server with an example. 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. 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 article.

But the most important point to keep in mind is that the Sequence in SQL Server is not limited to a column or table but it is scoped to an entire database.

Syntax:
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 } ]
    [ ; ] 

DataType: Built-in integer type (tinyint, smallint, int, bigint, decimal etc…) or user-defined integer type. The default is bigint.

START WITH: The Start With Value is nothing but the first value that is going to be returned by the sequence object

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.

MINVALUE: It specifies the value for the sequence object

NO MINVALUE: It specifies that there is no minimum value specified for the given sequence object.

MAXVALUE: Maximum value for the sequence object

NO MAXVALUE: It means that there is no maximum value specified for the sequence.

CYCLE: It specifies that reset the sequence object when the Sequence Object reached the maximum or Minimum value. 

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. 

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

CACHE: Cache sequence values for performance. Default value is CACHE.

NO CACHE: As the name says, if you specify the NO CACHE option then it will not cache the sequence numbers.

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.

Sequence in SQL ServerGenerating the Next Sequence Value:

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 the I got the current sequence object value as 5 as expected.

Retrieving the current sequence value:

If we want to see what 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]

Using sequence value in an INSERT query: 
CREATE TABLE Employees
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(50),
    Gender NVARCHAR(10)
)

-- Generate and insert Sequence values
INSERT INTO Employees VALUES
(NEXT VALUE for [dbo].[SequenceObject], 'Ben', 'Male')
INSERT INTO Employees VALUES
(NEXT VALUE for [dbo].[SequenceObject], 'Sara', 'Female')

Select the data from the table

SELECT * FROM Employees

sequence object in SQL Server 2012 - output

Creating the decrementing Sequence:

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.

Sequence in SQL Server

Recycling Sequence values:

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 performance?

If you want to Improve 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.

CREATE SEQUENCE [dbo].[SequenceObject]
START WITH 1
INCREMENT BY 1
CACHE 10
Drop Sequence

Once we have created our sequence in SQL Server, we might find that we need to remove it from the database.

Syntax: The syntax to a drop a sequence in SQL Server is:

DROP SEQUENCE sequence_name

sequence_name: The name of the sequence that you wish 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
INSERT INTO dbo.Customer(Name)
VALUES('Pranaya Kumar'),
('Mitali Rout')
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 Sequences folder
  4. Select New Sequence

Sequence in SQL Server

In the next article, I will discuss the differences between SEQUENCE and IDENTITY in SQL Server.

SUMMARY

In this article, I try to explain the Sequence in SQL Server step by step with a simple example. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *