Negative Identity Seed and Negative Increment in SQL Server

A Negative Identity seed makes a column start below zero, and a negative increment makes it count down. SQL Server accepts both, and it still generates every number for you. The feature is old and simple, but it raises good questions about ranges, gaps and resets.

How a Negative Identity Works

An identity column is a number SQL Server fills in for you. You declare it as IDENTITY(seed, increment). The seed is the first value, and the increment is the step added for each new row. The default is IDENTITY(1,1), but either number can be negative.

The demo is a bakery counter that hands out queue tickets and counts down from -1. The script creates a database named IdentityDemo for this post only, so run it on a test server.

IF DB_ID(N'IdentityDemo') IS NULL CREATE DATABASE IdentityDemo;
GO
USE IdentityDemo;
GO
DROP TABLE IF EXISTS dbo.Countdown;
CREATE TABLE dbo.Countdown (
    TicketID int IDENTITY(-1,-1) NOT NULL PRIMARY KEY,
    Customer nvarchar(50) NOT NULL
);
INSERT INTO dbo.Countdown (Customer) VALUES (N'Ana'), (N'Ben'), (N'Chloe');
SELECT TicketID, Customer FROM dbo.Countdown ORDER BY TicketID DESC;

The first row got -1, and each new row went one lower. Nothing else about the table is special. TRUNCATE TABLE also puts the identity back at its seed, so a truncated countdown table starts again at -1.

Start at the Bottom of the Range

The int type holds 4,294,967,296 values, from -2,147,483,648 to 2,147,483,647. A default identity uses only the positive half. Seed the column at the lowest value and count up to use both halves in 4 bytes. A bigint doubles the storage to 8 bytes.

DROP TABLE IF EXISTS dbo.FullRange;
CREATE TABLE dbo.FullRange (
    RowID int IDENTITY(-2147483648,1) NOT NULL PRIMARY KEY,
    Note  nvarchar(50) NOT NULL
);
INSERT INTO dbo.FullRange (Note) VALUES (N'first'), (N'second');
SELECT RowID, Note FROM dbo.FullRange ORDER BY RowID;

Read the Last Value You Created

After an insert, three functions can tell you the number that was used. SCOPE_IDENTITY returns the last value created in your own scope. @@IDENTITY returns the last one in your session, even from a trigger. IDENT_CURRENT returns the last value for a table in any session. Use SCOPE_IDENTITY in your code, because the other two can return a value you didn’t create.

INSERT INTO dbo.Countdown (Customer) VALUES (N'Dev');
SELECT SCOPE_IDENTITY() AS ScopeId, @@IDENTITY AS AtAtId, IDENT_CURRENT(N'dbo.Countdown') AS CurrentId,
       IDENT_SEED(N'dbo.Countdown') AS Seed, IDENT_INCR(N'dbo.Countdown') AS Incr;

All three agree here, because nothing else touched the table. IDENT_SEED and IDENT_INCR read back how the column was declared.

Gaps Appear After a Rollback

SQL Server never hands a number back. If an insert is rolled back, its number is gone for good. This script inserts Eli, rolls that back, and then inserts Fay.

BEGIN TRANSACTION;
INSERT INTO dbo.Countdown (Customer) VALUES (N'Eli');
ROLLBACK TRANSACTION;
INSERT INTO dbo.Countdown (Customer) VALUES (N'Fay');
SELECT TicketID, Customer FROM dbo.Countdown ORDER BY TicketID DESC;

Ticket -5 belonged to Eli, and it’s missing for good. A failed insert skips a number too, for example a NULL going into a NOT NULL column. A restart can leave a gap as well, because SQL Server caches identity values in memory. Gaps don’t hurt a primary key. They matter only when people read meaning into the numbers. Never use an identity for a number that must have no gaps, such as an invoice number.

Look and Reseed with DBCC CHECKIDENT

The command DBCC CHECKIDENT shows the current identity value of a table. With NORESEED it only looks.

DBCC CHECKIDENT (N'dbo.Countdown', NORESEED);

The message looks like this. It is output, not code to run.

Checking identity information: current identity value '-6', current column value '-6'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

With RESEED you choose the new current value. Once a table has held rows, the next row gets that value plus the increment. Deleting the rows doesn’t change this. A new or freshly truncated table starts at the reseed value itself.

DBCC CHECKIDENT (N'dbo.Countdown', RESEED, -100);
INSERT INTO dbo.Countdown (Customer) VALUES (N'Gus');
SELECT TOP (1) TicketID, Customer FROM dbo.Countdown ORDER BY TicketID ASC;

The reseed value was -100 and Gus got -101, because the increment is -1. A reseed doesn’t check for clashes, so make sure the new range is free. A primary key stops a clash, but only after the insert fails.

When an Int Identity Runs Out

The next case hurts most. The table below is already at the top of the int range, with its last row at 2,147,483,647.

DROP TABLE IF EXISTS dbo.OrderNumbers;
CREATE TABLE dbo.OrderNumbers (OrderID int IDENTITY(2147483647,1) NOT NULL PRIMARY KEY, Note nvarchar(20) NOT NULL);
INSERT INTO dbo.OrderNumbers (Note) VALUES (N'last');

One more row has nowhere to go.

INSERT INTO dbo.OrderNumbers (Note) VALUES (N'one too many');

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting IDENTITY to data type int.
Arithmetic overflow occurred.

You don’t need to change the increment. Reseed the column to the bottom of the range, and the same +1 step gives you the whole negative half. You couldn’t change it anyway. ALTER COLUMN with IDENTITY is a syntax error, so an existing column keeps its increment.

DBCC CHECKIDENT (N'dbo.OrderNumbers', RESEED, -2147483648);
INSERT INTO dbo.OrderNumbers (Note) VALUES (N'after reseed');
SELECT OrderID, Note FROM dbo.OrderNumbers ORDER BY OrderID;

The new row got -2147483647, one above the reseed value. The room runs from there up to the value under the smallest ID already stored. This buys time, and a move to bigint is the lasting fix.

SEQUENCE Is the Flexible Alternative

A sequence is a number generator that lives outside any table. Several tables can share it, and you can restart it or change its step with ALTER SEQUENCE. A table uses it through a default.

DROP TABLE IF EXISTS dbo.Tickets;
DROP SEQUENCE IF EXISTS dbo.TicketNumber;
CREATE SEQUENCE dbo.TicketNumber AS int START WITH -1 INCREMENT BY -1;
CREATE TABLE dbo.Tickets (
    TicketID int NOT NULL CONSTRAINT DF_Tickets_TicketID DEFAULT (NEXT VALUE FOR dbo.TicketNumber) PRIMARY KEY,
    Customer nvarchar(50) NOT NULL
);
INSERT INTO dbo.Tickets (Customer) VALUES (N'Ana'), (N'Ben');
ALTER SEQUENCE dbo.TicketNumber RESTART WITH -100;
INSERT INTO dbo.Tickets (Customer) VALUES (N'Chloe');
SELECT TicketID, Customer FROM dbo.Tickets ORDER BY TicketID DESC;

Why Anyone Does This, and Why Not

People use negative ranges for real reasons. Rows made outside a firewall can carry negative IDs, so they never clash with positive IDs made inside. A lookup table can use 0 or -1 for an “unknown” row. An app can hand out negative temporary IDs before the database assigns real ones. And doubling the int range is a fair reason.

You could argue that negative keys are clever, and clever isn’t kind to the next person who reads the table. That’s true. Reports that filter on ID > 0 will drop those rows without a warning. A separate range also helps catch a bad join. If a negative CustomerID meets a vendor table with no negatives, the join returns nothing. That’s easy to notice. Use a negative range only when the reason is written down next to the table.

What to Remember

A Negative Identity needs only a minus sign in IDENTITY(seed, increment). Gaps are normal. A reseed adds the increment once a table has rows. An int that reaches its top needs a reseed or a bigint. SEQUENCE gives you more control than IDENTITY. Pick the seed so the sign matches the story: down for a countdown, the bottom of the range for room.

Before I reseed, I check the lowest and highest IDs already in the table. That one query tells me how much room is left. When you finish testing, remove the example database.

USE master;
GO
ALTER DATABASE IdentityDemo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE IdentityDemo;

A negative identity is not a trick, it is a design choice that needs a reason.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.