SEQUENCE vs IDENTITY in SQL Server: Choosing the Right Auto-Increment

TL;DR Pick the wrong auto-increment strategy in SQL Server and you find out in production. An INT IDENTITY column hits 2,147,483,647. Replication nodes collide on the same keys. A failover jumps the counter by 10,000. IDENTITY for simple single-table surrogate keys. SEQUENCE for anything that crosses tables, needs pre-allocation, or has to survive a migration. Neither gives you gapless numbering. - Scope decides everything. IDENTITY is table-bound. SEQUENCE is database-level. - Both produce gaps. Rollback, restart, cache flush. The number is gone. - CACHE is the performance lever on SEQUENCE. IDENTITY has no equivalent knob. - Portability? SEQUENCE follows ANSI SQL. PostgreSQL, Oracle, Db2. IDENTITY is SQL Server only. The INT that ran out A logistics system. Event-logging table, INT IDENTITY(1,1) , high insert volume. Three years of operation. The counter hit 2,147,483,647. The next insert failed with an arithmetic overflow. That table was the system's write path. Everything behind it stalled. Solution? Migrate the column to BIGINT on 900 million rows. Maintenance window out of schedule. The table should have been BIGINT from day one. IDENTITY has properties you cannot change after the fact. You cannot reseed it across tables. You cannot pre-allocate ranges for replication nodes. You cannot cycle it back to zero. When you need any of those things, SEQUENCE is the tool. How each one works IDENTITY: the column property A property on a column. The engine generates the value at insert time. Two configuration knobs: seed and step. Nothing else. CREATE TABLE Customers ( CustomerID INT IDENTITY(1, 1) PRIMARY KEY, CustomerName NVARCHAR(200) NOT NULL ); INSERT INTO Customers (CustomerName) VALUES (N'Acme Corp'); -- CustomerID: 1, then 2, then 3... SEQUENCE: the standalone object Separate schema object. Create it independently. Call it wherever you need a number. CREATE SEQUENCE dbo.OrderNumberSequence START WITH 1000 INCREMENT BY 1 MINVALUE 1000 MAXVALUE 999999 CACHE 100; -- Use it in an insert INSERT INTO Orders (OrderID, CustomerID, TotalAmount) VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 1, 150.00); -- Or grab a number without inserting anything SELECT NEXT VALUE FOR dbo.OrderNumberSequence; That last line changes how you architect things. Pre-assign an order number. Pass it to a payment gateway. Insert the row later. Share one counter across three tables. Hand out ranges to replication nodes. None of that works with IDENTITY. Configuration and performance tradeoffs IDENTITY gives you seed and increment. SEQUENCE gives you six options. Two of them matter in production: CACHE and CYCLE . | Option | IDENTITY | SEQUENCE | |---|---|---| | Start value / increment | seed, step | START WITH , INCREMENT BY | | MINVALUE / MAXVALUE | not available | yes | | CYCLE (wrap at max) | not available | yes | | CACHE (memory range) | not available | yes | CACHE holds a range of numbers in memory. Most allocations skip disk entirely. A bulk insert of 10,000 rows with SEQUENCE CACHE 1000 produces roughly 10 disk writes for number generation. IDENTITY hits the allocation map on every row. The real bottleneck for concurrent inserts is PAGELATCH_EX contention on the last physical page of the clustered index. Sequential keys. All inserts land on the same data page. That page becomes a hotspot. Threads queue waiting for the latch. CACHE on a SEQUENCE reduces the write load to sys.sequences metadata tables. It does nothing about the physical contention on the target table's last page. Fix that with a non-sequential clustered index strategy, partitioning, or In-Memory OLTP. SEQUENCE with CACHE is not a substitute for any of those. CYCLE wraps the sequence back to MINVALUE when it hits MAXVALUE . Useful for yearly invoice numbering. Dangerous if older rows remain in the table. The cycled sequence generates numbers that already exist. Every new insert fails with a primary key violation. It keeps failing until you archive or purge the old data. I watched an ordering system go down for a full morning because a CYCLE wrapped without a matching archival job. IDENTITY has no CYCLE option. It just throws an arithmetic overflow. The replication problem Merge replication. Two nodes, both inserting rows, both generating IDENTITY values. Without range management, they produce the same numbers. Primary key violations on sync. SQL Server supports IDENTITY ranges in replication. Node A gets 1 to 1,000,000. Node B gets 1,000,001 to 2,000,000. When a node exhausts its block, it asks the publisher for another. This works. Until it doesn't. Nodes run out of range under load. They fail to negotiate a new block fast enough. Inserts stall while the publisher catches up. SEQUENCE simplifies this. Each node gets its own sequence with a different START WITH . No range negotiation. No publisher dependency: -- Node A CREATE SEQUENCE dbo.DocSeq START WITH 1 INCREMENT BY 1 CACHE 1000; -- Node B CREATE SEQUENCE dbo.DocSeq START WITH 1000000000 INCREMENT BY 1 CACHE 1000; The gap between ranges is deliberate. Node A grows past 100 million? Reseed. Want more headroom? Use BIGINT and start node B at 1,000,000,000,000 . Gaps, by design Five things cause gaps in both IDENTITY and SEQUENCE: transaction rollback, server restart, cache flush under memory pressure, failed bulk insert, and explicit reseed via DBCC CHECKIDENT . The first four apply to both mechanisms. BEGIN TRANSACTION; INSERT INTO Orders (OrderID, CustomerID, TotalAmount) VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 1, 150.00); -- OrderID: 1042 ROLLBACK TRANSACTION; -- 1042 is gone. Next insert gets 1043. The number generator does not participate in your transaction. It hands out a value. Moves its internal pointer forward. Transaction rolls back? Pointer stays. Making it transactional would require a lock that serializes all inserts. Throughput dies. For surrogate keys, gaps are irrelevant. CustomerID 57 missing does not break any join or any index. Gaps matter when the number has legal or audit significance. Invoice numbers. Tax document IDs. Compliance certificates. Since SQL Server 2017, you can disable caching for IDENTITY at the database level: ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF . This prevents the jump in IDENTITY values on an unexpected server restart or an Always On failover. The trade-off is a slight insert performance penalty. The engine persists the current identity value more aggressively. Gap size after a failover is a business concern? Turn it off. High-throughput logging table where nobody cares about gaps? Leave the default. Gapless numbering when you need it Neither IDENTITY nor SEQUENCE gives you gapless. You need a counter table with explicit locking. The old way was a "quirky update": SET @NextNumber = LastNumber = LastNumber + 1 inside an UPDATE . It works. It is also an undocumented anti-pattern, and it races on January 1st. Two threads discover the row for the new year does not exist. Both pass the UPDATE with @@ROWCOUNT = 0 . Both enter the IF block. Both try to INSERT . First wins. Second gets a primary key violation. The correct approach is MERGE with HOLDLOCK and the OUTPUT clause: CREATE TABLE dbo.InvoiceNumberCounter ( Year INT NOT NULL PRIMARY KEY, LastNumber INT NOT NULL ); BEGIN TRANSACTION; DECLARE @AllocatedNumber TABLE (NextNum INT); MERGE dbo.InvoiceNumberCounter WITH (HOLDLOCK) AS target USING (SELECT YEAR(SYSUTCDATETIME()) AS InvoiceYear) AS source ON target.Year = source.InvoiceYear WHEN MATCHED THEN UPDATE SET LastNumber = LastNumber + 1 WHEN NOT MATCHED THEN INSERT (Year, LastNumber) VALUES (source.InvoiceYear, 1) OUTPUT inserted.LastNumber INTO @AllocatedNumber; DECLARE @NextNumber INT = (SELECT NextNum FROM @AllocatedNumber); INSERT INTO Invoices (InvoiceNumber, CustomerID, Amount) VALUES (@NextNumber, @CustomerID, @Amount); COMMIT TRANSACTION; HOLDLOCK takes a range lock. The row for the current year does not exist? The range lock prevents another session from inserting it. The second MERGE blocks. Then wakes up. Finds the row the first session created. No race condition. No PK violation. This serializes all invoice creation for a given year into a single queue. For most businesses, fine. Issuing thousands of invoices per second? Talk to your compliance team about whether gapless is truly a legal requirement. The serialization cost is real. There is no way around it. What I use Single-table surrogate key. No cross-table needs. No replication. IDENTITY(1, 1) . Simplest thing that works. CREATE TABLE dbo.Products ( ProductID INT IDENTITY(1, 1) NOT NULL, ProductName NVARCHAR(200) NOT NULL, CONSTRAINT PK_Products PRIMARY KEY (ProductID) ); Anything else: SEQUENCE with CACHE 50 . Loses at most 50 numbers on a restart. High-throughput workload? CACHE 1000 or higher. CREATE SEQUENCE dbo.DocumentNumberSeq AS INT START WITH 1 INCREMENT BY 1 CACHE 50; After a data migration, reseed: DECLARE @MaxID INT = (SELECT ISNULL(MAX(OrderID), 0) FROM Orders); ALTER SEQUENCE dbo.OrderNumberSequence RESTART WITH @MaxID + 1; For IDENTITY: DBCC CHECKIDENT ('Orders', RESEED, @MaxID); One last thing. Use BIGINT for anything you expect to accumulate more than a few hundred million rows. INT buys you 2.1 billion values. Sounds like a lot. Then you have a table that logs every API call, every state change, every event. The counter runs out. Top comments (0)