SQL - Sequence Object

This beautiful feature of the SQL Server was introduced within SQL Server 2012 released.

A sequence object is nothing but it’s a user-defined schema bound object that generates a sequence of numeric values according to the specification with which the sequence was created. Sequences, unlike identity columns, are not associated with specific tables.

Sequences generated by the Sequence object are similar to identity property values; however, it is not tied to one table. A sequence can be defined as any integer data type. If the data type is not specified, a sequence defaults to bigint.

Remember, a SEQUENCE is not an IDENTITY. The best things is that we can use the sequence object to generate the numeric values as in an ascending or descending order at a defined intervals and can be configured to restart (cycle) when exhausted. Applications refer to a sequence object to retrieve its next value.

The relationship between sequences and tables is controlled by the application. User applications can reference a sequence object and coordinate the values across multiple rows and tables.

CREATE SEQUENCE Statement

As we stated that it’s not an Identity, a sequence is created independently of the tables by using the CREATE SEQUENCE statement. These Options enable us to control the increment, maximum and minimum values, starting point, automatic restarting capability, and caching to improve performance of the application. Sequence object can be referenced by applications directly and can also be shared by many rows or many tables.

To understand the sequences, we can create use them as the following types-

• Single Sequences
• Multiple Sequences
• Recycling Sequences

Single Sequences - In this scenario, we are using a sequence number in a single table. To this we need to create a sequence object and target table to use it as showing below:

USE SQLDemo Go ---- Create Sequence CREATE SEQUENCE dbo.EmployeeCode as int START WITH 2015 INCREMENT BY 5; GO ---- Create Employee Table CREATE TABLE dbo.EmployeeMaster ( EmpCode Int,  EmpName Varchar(10),  EmpAge Int  )  Go

After creating the sequence object and target table, we need to fill the data into the employee master table with the help of the sequence object as given below:

USE SQLDemo Go --- Call Sequence Object SELECT NEXT VALUE FOR dbo.EmployeeCode No Column Name 2015 ---- Call Sequence Object with column name SELECT NEXT VALUE FOR dbo.EmployeeCode  Go EmpCode 2020 ---- Insert Values into Employee Master table with sequence object  INSERT INTO dbo.EmployeeMaster (EmpCode, EmpName, EmpAge) Values  (NEXT VALUE FOR dbo.EmployeeCode, 'Ryan Arjun', 25),  (NEXT VALUE FOR dbo.EmployeeCode, 'Will Smith', 35),  (NEXT VALUE FOR dbo.EmployeeCode, 'Lucy Gray', 18)  ---- Select records from the table  SELECT EmpCode, EmpName, EmpAge FROM dbo.EmployeeMaster EmpCode EmpName EmpAge 2025 Ryan Arjun 25 2030 Will Smith 35 2035 Lucy Gray 18

Multiple Sequences: We have already known that sequence object can be referenced by applications directly and can also be shared by many rows or many tables. To understand a sequence number in multiple tables, we are going to take a student class master example where registration number is common for every class as shown below:

Use SQLDemo GO ---- Create Reg Number Sequence Object CREATE SEQUENCE dbo.RegNumber     AS int     START WITH 201501     INCREMENT BY 1; GO --- Create BCA Student Class Table CREATE TABLE dbo.StudentBCA ( RegNumber Int Primary Key default(NEXT VALUE FOR dbo.RegNumber), RollNumber Int Identity(1,1), StudentName Varchar(25) ) --- Create BBA Student Class Table CREATE TABLE dbo.StudentBBA ( RegNumber Int Primary Key default(NEXT VALUE FOR dbo.RegNumber), RollNumber Int Identity(1,1), StudentName Varchar(25) ) --- Create MCA Student Class Table CREATE TABLE dbo.StudentMCA ( RegNumber Int Primary Key default(NEXT VALUE FOR dbo.RegNumber), RollNumber Int Identity(1,1), StudentName Varchar(25) ) --- Create MBA Student Class Table CREATE TABLE dbo.StudentMBA ( RegNumber Int Primary Key default(NEXT VALUE FOR dbo.RegNumber), RollNumber Int Identity(1,1), StudentName Varchar(25) ) ---- Insert Values into the respective tables INSERT INTO dbo.StudentBBA (StudentName) Values  ('Ryan Arjun'),('Will Smith'),('Lucy Grey') INSERT INTO dbo.StudentBCA (StudentName) Values  ('Arjun Singh'),('Smith Blue'),('Grey White') INSERT INTO dbo.StudentMBA (StudentName) Values ('Alice Wolf'),('Will Johnson'),('Apple Grey') INSERT INTO dbo.StudentMCA (StudentName) Values ('Anil Arjun'),('Gerry Smith'),('Neil Grey') ---- Fetch records from the respective tables SELECT RegNumber,RollNumber, StudentName, Class='BBA' FROM dbo.StudentBBA UNION SELECT RegNumber,RollNumber, StudentName, Class='BCA' FROM dbo.StudentBCA UNION SELECT RegNumber,RollNumber, StudentName, Class='MBA' FROM dbo.StudentMBA UNION SELECT RegNumber,RollNumber, StudentName, Class='MCA'  FROM dbo.StudentMCA RegNumber RollNumber StudentName Class 201501 1 Ryan Arjun BBA 201502 2 Will Smith BBA 201503 3 Lucy Grey BBA 201504 1 Arjun Singh BCA 201505 2 Smith Blue BCA 201506 3 Grey White BCA 201507 1 Alice Wolf MBA 201508 2 Will Johnson MBA 201509 3 Apple Grey MBA 201510 1 Anil Arjun MCA 201511 2 Gerry Smith MCA 201512 3 Neil Grey MCA

Recycling Sequences- Generating repeating sequence numbers in a result set is another very useful feature of the sequence objects where we can set the recycling limit of the sequences after reaching the maximum value of the sequence as given below:

Use SQLDemo Go ---- Create Sequence with CYCLE CREATE SEQUENCE dbo.RecyclingSequence AS TINYINT START WITH 2 ---- Start Position INCREMENT BY 1 ---- Increment By MINVALUE  0 ---- minimum value MAXVALUE  5 CYCLE ---- Set recycle again ---- Select value from the sequence object SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate SELECT NEXT VALUE FOR dbo.RecyclingSequence as RecycleNumber, Getdate() as RunDate

Alter Sequence Object: we can alter the existing sequence objects with the help of the alter command as given below:

Use SQLDemo Go ---- Alter Sequence with CYCLE ALTER SEQUENCE dbo.RecyclingSequence RESTART WITH 3 INCREMENT BY 2 MINVALUE  2 MAXVALUE  10

Drop Sequence Objects: we can drop the existing sequence objects with the help of the drop command as given below:

Use SQLDemo Go ---- drop Sequence Objects Drop Sequence dbo.RecyclingSequence

Benefits of Using Sequences
In the real world, there are lots of scenarios where we can use sequences instead of identity columns in the following:

1. If the application requires a number before the insert into the table is made.
2. The business application requires the sharing a single series of numbers for multiple tables or multiple columns within a table.
3. The application must restart the number series when a specified number is reached.
4. The application requires sequence values to be sorted by another field.
5. An application requires multiple numbers to be assigned at the same time.
6. We need to change the specification of the sequence, such as the increment value.

Limitations of Sequence Objects

1. Unlike identity columns, whose values cannot be changed, sequence values are not automatically protected after insertion into the table. To prevent sequence values from being changed, use an update trigger on the table to roll back changes.
2. Uniqueness is not automatically enforced for sequence values. The ability to reuse sequence values is by design.
3. The sequence object generates numbers according to its definition, but the sequence object does not control how the numbers are used.
4. If there are multiple instances of the NEXT VALUE FOR function specifying the same sequence generator within a single Transact-SQL statement, all those instances return the same value for a given row processed by that Transact-SQL statement.
5. This behaviour is consistent with the ANSI standard.