Sequences and Series: SQL Server 2012 introduces the schema-bound sequence capability. Using predetermined settings, it produces a series of numerical values. Compared to identification columns, this feature provides more flexibility by enabling number production that is reusable and adaptable across many tables and applications.
Table of Contents
Introduction to Sequences and Series in SQL Server
In SQL Server, generating a series of numbers or creating a sequence is often necessary for tasks like pagination, ID generation, or data simulations. While SQL Server doesn’t have a built-in generate_series() function like PostgreSQL, it provides powerful alternatives through SEQUENCE objects and various T-SQL techniques.
A sequence is a user-defined schema-bound object that helps us to generate a sequence of numeric values based on the specification provided at the time of sequence creation. Unlike identity columns tied to a specific table, sequences are independent and can be reused across multiple tables or procedures.
🕰️ A Glimpse into History
Before SQL Server 2012, we generally use identity columns to generate sequential numbers or we need to write a custom logic to generate sequential numbers for the tables. But now we have sequence feature in SQL for this purpose. To create sequence and Series in SQL Server, we need to use CREATE SEQUENCE statement.
Advantages of Sequences and Series:
A few advantages of Sequences and Series are given below for more clarity:
✅ Reusability:
SQL Server provides feature to create aequences & use for single ot multiple tables and even in multiple applications also.
✅ Customizability:
We have multiple parameters to define the sequence in SQL Server. Duering sequence creation, We can define starting point, increment, minimum & maximum values, and cycling behavior, and a relavent name as per our requirement.
✅ Performance:
It also provides option to cache the data which helps us to improve the performance by reducing disk I/O.
✅ Manual Control Over Values:
It also provides full control over sequence number generation. Sequence Values can be fetched manually using “NEXT VALUE FOR”.
✅ No Table Dependency:
Sequence’s best feature is that it is independent of any table structure, making it more flexible for applications that need centralized number generation.
✅ Better for Bulk Inserts:
Another feature of Sequence is that it performs better with bulk operations than identity columns, especially when bulk inserting large datasets.
✅ Allows Pre-Generating Values:
We can generate a new sequence according to the requirement and assign the values manually. This is helpful in batching or offline scenarios.
✅ Avoids Gaps (With Proper Handling):
If we manage the transactions correctly, we can avoid unnecessary gaps in Sequences. Sequences can generally skip values during the rollback process.
Disadvantages of Sequences and Series:
A few disadvantages of Sequences and Series are given below for more clarity:
❌ Complexity:
A better understanding of sequence concepts is required when we use sequences.
❌ Possibility of Gaps:
If the transaction rolls back, that number is still lost, which may result in gaps in the Sequence.
❌ No Built-in Uniqueness Constraint:
Unlike identity columns, sequences don’t enforce uniqueness unless explicitly handled through constraints or indexes.
❌ Manual Value Assignment:
It provides a feature to explicitly use NEXT VALUE FOR assigning sequence values, which can be error-prone if developers forget it.
❌ Concurrency Management
In high-concurrency environments, managing sequences properly requires extra care to avoid contention or logic errors.
❌ Does Not Auto-Rollback:
Sequences are not transactional. Once a value is generated, it won’t roll back even if the transaction fails.
❌ Not Ideal for All Scenarios:
Identity columns may be easier and more straightforward for simple auto-incrementing keys tied directly to one table.
Why Do We Need Sequences and Series in SQL Server?
Sequences are essential when:
- Multiple tables require unique identifiers from a common sequence.
- Pre-generating values before inserting into a table is necessary.
- Custom increment or cycling behavior is needed.
- Generating sequences independent of table insert operations.
Best Practices for Sequences and Series in SQL Server
📌Use Appropriate Data Types:
Choose a data type that accommodates the expected range of values.
📌Implement Caching Wisely:
Use the CACHE option to improve performance, but be aware of potential value loss in case of server crashes.
📌Monitor Sequence Usage:
Regularly check sequence values to prevent exceeding defined limits.
📌Secure Sequences:
Grant permissions carefully to control access to sequence values.
Necessary Permissions for Sequences and Series in SQL Server
To create, alter, or drop a sequence, a user must have the CREATE SEQUENCE, ALTER, or CONTROL permission on the schema. Members of the db_owner and db_ddladmin fixed database roles inherently possess these permissions.
✅ Syntax of Sequences and Series
CREATE SEQUENCE [Schema Name].[Sequence Name]
AS [DataType] ]
START WITH <Initial Value>
INCREMENT BY <Increment By Value>
[ MINVALUE <Minimum Vakue> | NO MINVALUE ]
[ MAXVALUE <Maximum Vakue> | NO MAXVALUE ]
[ CYCLE | NO CYCLE ]
[ CACHE <Cache Value> | NO CACHE ];
🧩 Parameter Details of Sequences and Series
| Clause | Description |
| Schema Name | This is an optional parameter. Specify the schema name to categorise the sequence. |
| Sequence Name | Mendatry. Specify sequence name. |
| AS | Specify the data type of sequence. It should be INT, BIGINT, SMALLINT. Default value of sequence is BIGINT. |
| Start With | To Specify the first value of a sequence. |
| INCREMENT BY | The value by which the sequence will increase or decrease. |
| MINVALUE / NO MINVALUE | To Set the minimum value of a sequence. |
| MAXVALUE / NO MAXVALUE | To Set the maximum value of a sequence. |
| CYCLE | Restarts the sequence number when the min./max. limit is reached. |
| NO CYCLE | Throws an error when the sequence exceeds its range (default). |
| CACHE | Specifies how many sequence numbers to keep in memory to enhance the performance. |
| NO CACHE | Disables caching. Each number is written to disk immediately (slower). |
Examples of Sequences and Series
A few examples are given below for more clarity & better understanding:
🔹 1. Basic Sequence Creation
Use below script to creates a simple sequence that will start with one and increment by 1.
CREATE SEQUENCE dbo.SeqOrderID
START WITH 1
INCREMENT BY 1;🔹 2. How to generating Next Value using the Sequence
How to fetches the next value from the sequence.
SELECT NEXT VALUE FOR dbo.SeqOrderID AS NextOrderID;
🔹 3. Inserting Sequence or Series Values into Orders Table
Use below script to create a sample table & populate sample data. Adds a new record with a sequence-generated OrderID.
CREATE TABLE [dbo].[Orders](
[OrderID] [int] NOT NULL PRIMARY KEY,
[ClientName] [varchar](100) NULL,
[Zone] [varchar](10) NULL,
[OrderAmount] [decimal](10, 2) NULL,
[OrderDate] [date] NULL)
GO
CREATE SEQUENCE dbo.SeqOrderID
START WITH 1
INCREMENT BY 1;
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID, N'Reliance Industries Limited', N'West', CAST(5000.00 AS Decimal(10, 2)), CAST(N'2025-01-15' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID, N'Tata Consultancy Services', N'West', CAST(7000.00 AS Decimal(10, 2)), CAST(N'2025-01-20' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'HDFC Bank', N'West', CAST(6000.00 AS Decimal(10, 2)), CAST(N'2025-01-25' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'ICICI Bank', N'West', CAST(8000.00 AS Decimal(10, 2)), CAST(N'2025-02-01' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,'State Bank of India', N'East', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,'Life Insurance Corporation of India', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,'Infosys', N'South', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,'Bharti Airtel', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-11' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,'Hindustan Unilever Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'ITC Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-15' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'HCL Technologies Limited', N'South', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'ICICI Bank', N'West', CAST(8000.00 AS Decimal(10, 2)), CAST(N'2025-02-11' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'HDFC Bank', N'West', CAST(6000.00 AS Decimal(10, 2)), CAST(N'2025-01-20' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'ITC Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-01' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'Hindustan Unilever Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-10' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'Bharti Airtel', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-15' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'HCL Technologies Limited', N'South', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-16' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'Life Insurance Corporation of India', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-16' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'Hindustan Unilever Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-16' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'State Bank of India', N'East', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-17' AS Date)
GO
INSERT [dbo].[Orders] ([OrderID], [ClientName], [Zone], [OrderAmount], [OrderDate])
SELECT NEXT VALUE FOR dbo.SeqOrderID,N'Fortis Healthcare Limited', N'North', CAST(5500.00 AS Decimal(10, 2)), CAST(N'2025-02-17' AS Date)
GO
🔹 4. Create a Sequence with Custom Start value and Increment value
Creates a sequence starting from 2000 and incrementing by 15.
CREATE SEQUENCE SeqCustomOrder
START WITH 2000
INCREMENT BY 15;🔹 5. Using Sequence in a SELECT with Existing Orders
Assigns a sequence number alongside each row of the Orders table.
SELECT NEXT VALUE FOR SeqCustomOrder AS NewOrderID, *
FROM Orders;🔹 6. Add a Column with Sequence to Existing Data (Temporary Use)
It helps generate row numbers temporarily without modifying the table.
SELECT NEXT VALUE FOR SeqCustomOrder AS RowNumber, *
FROM Orders;🔹 7. Update table using Sequence
Assign new sequence-generated OrderIDs to all rows (⚠️ Use with caution).
UPDATE Orders
SET OrderID = NEXT VALUE FOR SeqOrderID;🔹 8. Cycle a Sequence After a Limit
This sequence will reset to 1 after reaching 5.
CREATE SEQUENCE SeqCycled
START WITH 1
INCREMENT BY 1
MAXVALUE 5
CYCLE;🔹 9. Insert Multiple Rows Using Sequence
Inserts 5 rows using sequence values (for bulk insert simulation).
INSERT INTO Orders (OrderID, CustomerName, OrderDate)
SELECT NEXT VALUE FOR SeqOrderID, 'BulkCustomer', GETDATE()
FROM sys.all_objects
WHERE object_id <= 5;🔹 10. Drop a Sequence
Removes the sequence object from the database.
DROP SEQUENCE SeqOrderID;🔹 11. Add Row Numbers to Orders Using a Sequence
Adds a sequence-generated row number ordered by the most recent order.
CREATE SEQUENCE SeqRowNum START WITH 50 INCREMENT BY 1;
go
SELECT NEXT VALUE FOR dbo.SeqRowNum,[ClientName], [Zone], [OrderAmount], [OrderDate]
From dbo.Orders 
🔹 12. Assign a Sequence-Based Order Number Per Region
Assuming you added a Region column to Orders:
CREATE SEQUENCE SeqGlobalOrder START WITH 10000 INCREMENT BY 5;
SELECT Region,
NEXT VALUE FOR SeqGlobalOrder AS GlobalOrderNumber,
CustomerName, OrderDate
FROM Orders
ORDER BY Region, OrderDate;🔹 13. Use Sequence in a MERGE Statement for Upserts
Adds a new row only if it doesn’t exist, using sequence-generated ID.
CREATE SEQUENCE SeqMergeOrder START WITH 5000 INCREMENT BY 1;
MERGE INTO Orders AS Target
USING (SELECT 'NewCustomer' AS CustomerName, GETDATE() AS OrderDate) AS Source
ON 1 = 0
WHEN NOT MATCHED THEN
INSERT (OrderID, CustomerName, OrderDate)
VALUES (NEXT VALUE FOR SeqMergeOrder, Source.CustomerName, Source.OrderDate);🔹 14. Generate Monthly Order Numbers (Restart Manually Per Month)
— Assume manual restart each month
CREATE SEQUENCE SeqMonthlyOrder START WITH 1 INCREMENT BY 1;
SELECT FORMAT(OrderDate, 'yyyyMM') AS OrderMonth,
NEXT VALUE FOR SeqMonthlyOrder AS MonthlyOrderNo,
CustomerName
FROM Orders
WHERE MONTH(OrderDate) = MONTH(GETDATE());🔹 15. Create a Composite Key Using a Sequence
Generates a formatted custom key like ORD-2025-0101.
CREATE SEQUENCE SeqCustomKey START WITH 1000 INCREMENT BY 1;
SELECT
'ORD-' + CAST(YEAR(OrderDate) AS VARCHAR) + '-' + RIGHT('0000' + CAST(NEXT VALUE FOR Seq_CustomKey AS VARCHAR), 4) AS CustomOrderKey,
CustomerName
FROM Orders;🔹 16. Use Sequence in a Stored Procedure for Controlled Inserts
Procedure to insert with sequence-generated IDs.
CREATE SEQUENCE SeqProcOrderID START WITH 5000 INCREMENT BY 1;
GO
CREATE PROCEDURE InsertOrderWithSeq
@CustomerName NVARCHAR(100)
AS
BEGIN
INSERT INTO Orders (OrderID, CustomerName, OrderDate)
VALUES (NEXT VALUE FOR SeqProcOrderID, @CustomerName, GETDATE());
END🔹 17. Generate Unique IDs for a Temp Table
Creates a temporary table with uniquely generated IDs.
CREATE SEQUENCE SeqTempID START WITH 1000 INCREMENT BY 1;
SELECT NEXT VALUE FOR SeqTempID AS TempRowID, *
INTO #TempOrders
FROM Orders;🔹 18. Update Orders with Sequential Rank Based on Order Value
Assuming the TotalAmount column exists:
CREATE SEQUENCE SeqRank START WITH 1 INCREMENT BY 1;
SELECT
NEXT VALUE FOR SeqRank AS Rank,
CustomerName, TotalAmount
FROM Orders
ORDER BY TotalAmount DESC;🔹 19. Use Sequences and Series to Generate Audit IDs
Simulates generating audit logs with sequence-generated audit IDs.
CREATE SEQUENCE SeqAuditID START WITH 1 INCREMENT BY 1;
INSERT INTO OrderAudit (AuditID, OrderID, ChangeDate)
SELECT NEXT VALUE FOR SeqAuditID, OrderID, GETDATE()
FROM Orders
WHERE OrderDate < DATEADD(DAY, -30, GETDATE());🔹 20. Reset and Restart Sequences and Series for Testing
Manually resets the sequence back to a starting point.
ALTER SEQUENCE SeqOrderID RESTART WITH 1;
SELECT NEXT VALUE FOR SeqOrderID AS TestID1;
SELECT NEXT VALUE FOR SeqOrderID AS TestID2;Conclusion
In SQL Server, sequences and numeric series offer a versatile approach to generating ordered numbers. Since they can be tailored for specific use cases and shared across different tables, they serve as a valuable resource for both developers and database administrators. Gaining a solid grasp of how they work, including proper usage and permissions, allows you to leverage them efficiently in your applications.
FAQs (Frequently Asked Questions)
Q: What are the Sequences and Series in SQL Server?
Ans: The sequences and Series are a user-defined object that generates numeric values based on specified parameters.
Q: How does a sequence differ from an identity column?
Ans: Unlike identity columns, sequences are not tied to a specific table and can be used across multiple tables.
Q: Can Sequences and Series be used in multiple tables?
Ans: Yes, sequences can generate unique values for multiple tables.
Q: What permissions are required to create a sequence?
Ans: Users need CREATE SEQUENCE, ALTER, or CONTROL permission on the schema.
Q: How can I reset a sequence?
Ans: Use the ALTER SEQUENCE statement with the RESTART WITH option.
Q: What happens if a sequence reaches its maximum value?
Ans: If the sequence is not set to cycle, it will throw an error upon reaching its maximum value.
Q: Can I create a Sequences and Series in descending order?
Ans: Yes, by specifying a negative increment value.
Q: How do I check the current value of a sequence?
Ans: Query the sys—sequences catalog view.
Q: Is it possible to cache sequence values?
Ans: Yes, using the CACHE option can improve performance but may lead to gaps if the server crashes.
Q: Can I use sequences in the Azure SQL Database?
Ans: Yes, sequences are supported in the Azure SQL Database.
Review the articles below, also.
Understand Deadlocks in SQL Server
Unleash Database Insights with Extended Events in SQL Server
Dynamic Data Masking in SQL Server
A Powerful SQL Server Developer Edition
SQL Server Configuration Manager
SQL Managed Instance: Faster & Improved
TSQL Database Backup: Top 7 Usage
Dbcc Freeproccache: A powerful command
Extended Events in SQL Server: A Deep Dive
Understand Deadlocks in SQL Server