Identity Column in SQL Server: Seed, SCOPE_IDENTITY, Reseed and Errors

Say you’re logging web orders for a Denver shop and want order numbers that start at 1001 and count up on their own.

That’s the job of an identity column in SQL Server. Declare OrderID int IDENTITY(1001, 1), leave it out of every INSERT, and SQL Server fills it in:

SET NOCOUNT ON;
CREATE TABLE dbo.Orders (
    OrderID  int IDENTITY(1001, 1) PRIMARY KEY,
    Customer nvarchar(15) NOT NULL,
    City     nvarchar(10) NOT NULL,
    Amount   decimal(8,2) NOT NULL CHECK (Amount > 0)
);
INSERT INTO dbo.Orders (Customer, City, Amount) VALUES
(N'Hannah Brooks',  N'Denver',   129.99),
(N'Tyler Reed',     N'Austin',    54.10),
(N'Grace Miller',   N'Columbus', 310.00),
(N'Marcus Bennett', N'Tampa',     22.75),
(N'Olivia Hayes',   N'Boise',     88.40);
SELECT OrderID, Customer, City, Amount FROM dbo.Orders;

Output:

OrderID     Customer        City       Amount    
----------- --------------- ---------- ----------
       1001 Hannah Brooks   Denver         129.99
       1002 Tyler Reed      Austin          54.10
       1003 Grace Miller    Columbus       310.00
       1004 Marcus Bennett  Tampa           22.75
       1005 Olivia Hayes    Boise           88.40

Tested on SQL Server 2025 (17.0.1000.7) Developer edition, sqlcmd in VS Code. Nobody typed 1001 to 1005.

The Sample Orders Table With an Identity Column

Every example reuses that table. The CHECK (Amount > 0) is there to make an insert fail later. To rerun the script, you’ll need DROP TABLE IF EXISTS first.

How SQL Server Identity Seed and Increment Work

The seed is the first value. The increment is added to the last value for each new row. Give both or neither; skip both and you get (1, 1).

Here’s a support-ticket table that starts at 100 and steps by 10:

SET NOCOUNT ON;
CREATE TABLE dbo.Tickets (
    TicketID smallint IDENTITY(100, 10) PRIMARY KEY,
    Subject  nvarchar(15) NOT NULL
);
INSERT INTO dbo.Tickets (Subject)
VALUES (N'Refund request'), (N'Late delivery'), (N'Wrong size');
SELECT TicketID, Subject FROM dbo.Tickets;

Output:

TicketID Subject        
-------- ---------------
     100 Refund request 
     110 Late delivery  
     120 Wrong size

How does this work? Row one took the seed, 100. Rows two and three each added 10, so the last value is 120.

Allowed types: tinyint, smallint, int, bigint, and decimal/numeric with scale 0. The column must be NOT NULL. Pick bigint if the table could pass 2.1 billion rows.

Method 1: Add an Identity Column to an Existing Table (Recommended)

Table with no key? Use ALTER TABLE ADD with a new identity column and the existing rows get numbered. More in SQL Server ALTER TABLE ADD column.

SET NOCOUNT ON;
CREATE TABLE dbo.Customers (Name nvarchar(15), State char(2));
INSERT INTO dbo.Customers VALUES
(N'Hannah Brooks', 'CO'), (N'Tyler Reed', 'TX'), (N'Grace Miller', 'OH');

ALTER TABLE dbo.Customers
    ADD CustomerID int IDENTITY(1, 1) NOT NULL;

SELECT CustomerID, Name, State FROM dbo.Customers;

Output:

CustomerID  Name            State
----------- --------------- -----
          1 Hannah Brooks   CO   
          2 Tyler Reed      TX   
          3 Grace Miller    OH

How does this work? One statement adds the column and numbers all three rows: 1, 2, 3.

Important: which existing row gets which number isn’t guaranteed.

Method 2: Set an Identity Column in the SSMS Table Designer

Prefer clicking? In SSMS 21 (the full designer walk-through is in create a table in SSMS):

1. In Object Explorer, right-click Tables > New > Table.
2. Select an int column, expand Identity Specification in Column Properties and set (Is Identity) to Yes.
3. Set Identity Increment and Identity Seed, then save.

SCOPE_IDENTITY vs @@IDENTITY vs IDENT_CURRENT

Three functions return the new ID, and a trigger makes them disagree. Same Orders table, now with an audit trigger that writes to its own identity table (seed 9001). Trigger basics are in SQL Server AFTER INSERT triggers.

CREATE TABLE dbo.OrderAudit (
    AuditID int IDENTITY(9001, 1) PRIMARY KEY,
    OrderID int NOT NULL
);
GO
CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO dbo.OrderAudit (OrderID) SELECT OrderID FROM inserted;
END;

Now one insert and all three functions:

SET NOCOUNT ON;
INSERT INTO dbo.Orders (Customer, City, Amount)
VALUES (N'Ethan Cole', N'Raleigh', 64.00);

SELECT CAST(SCOPE_IDENTITY() AS int)            AS ScopeIdentity,
       CAST(@@IDENTITY AS int)                  AS AtAtIdentity,
       CAST(IDENT_CURRENT('dbo.Orders') AS int) AS IdentCurrent;

Output:

ScopeIdentity AtAtIdentity IdentCurrent
------------- ------------ ------------
         1006         9001         1006

How does this work? SCOPE_IDENTITY() returns the last identity made in your own batch or procedure: 1006. @@IDENTITY returns the last one in the session, from any scope, so it caught the trigger’s 9001.

IDENT_CURRENT ignores session and scope, so it can return another user’s row. Use SCOPE_IDENTITY(), for example inside a stored procedure for INSERT.

Return Every New ID With OUTPUT inserted

SCOPE_IDENTITY() gives you one value. For a multi-row insert, capture all of them with OUTPUT inserted:

SET NOCOUNT ON;
DECLARE @new TABLE (OrderID int, Customer nvarchar(15));

INSERT INTO dbo.Orders (Customer, City, Amount)
OUTPUT inserted.OrderID, inserted.Customer INTO @new
VALUES (N'Ethan Cole', N'Raleigh', 64.00),
       (N'Ava Turner', N'Omaha',   41.25);

SELECT OrderID, Customer FROM @new;

Output:

OrderID     Customer       
----------- ---------------
       1006 Ethan Cole     
       1007 Ava Turner

Two rows, two IDs: 1006 and 1007. INTO @new isn’t optional here. This table has a trigger, and a bare OUTPUT clause fails with Msg 334 (see the gotchas below).

The inserted table is also what a trigger reads to get inserted values.

Why Does My Identity Column Have Gaps?

Because identity values are never handed back. Same Orders table: one insert rolled back, one blocked by the CHECK constraint, then one that worked.

SET NOCOUNT ON;
BEGIN TRAN;
INSERT INTO dbo.Orders (Customer, City, Amount) VALUES (N'Noah Price', N'Reno', 15.00);
ROLLBACK;

BEGIN TRY
  INSERT INTO dbo.Orders (Customer, City, Amount) VALUES (N'Lily Ward', N'Mesa', -5.00);
END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': CHECK constraint, insert failed'); END CATCH;

INSERT INTO dbo.Orders (Customer, City, Amount) VALUES (N'Owen Hart', N'Provo', 33.50);
SELECT OrderID, Customer FROM dbo.Orders WHERE OrderID > 1004;

SELECT CAST(name AS varchar(14)) AS Setting, CAST(value AS int) AS Value
FROM sys.database_scoped_configurations WHERE name = 'IDENTITY_CACHE';

Output:

Msg 547: CHECK constraint, insert failed
OrderID     Customer       
----------- ---------------
       1005 Olivia Hayes   
       1008 Owen Hart      
Setting        Value      
-------------- -----------
IDENTITY_CACHE           1

The CATCH block is the same pattern as TRY…CATCH in a stored procedure.

Owen got 1008. The rollback burned 1006 and the failed insert burned 1007, by design.

With IDENTITY_CACHE = 1 (the default above), an unexpected restart or failover can also skip a block of values. I didn’t restart to show it. Turn it off per database (SQL Server 2017+):

ALTER DATABASE SCOPED CONFIGURATION SET IDENTITY_CACHE = OFF;

Rollback gaps stay.

Reset Identity Column in SQL Server With DBCC CHECKIDENT

Deleted some test rows and want the numbers to continue from 1003? Reseed:

SET NOCOUNT ON;
DELETE FROM dbo.Orders WHERE OrderID > 1003;
DBCC CHECKIDENT ('dbo.Orders', RESEED, 1003) WITH NO_INFOMSGS;

INSERT INTO dbo.Orders (Customer, City, Amount)
VALUES (N'Chloe Grant', N'Boston', 47.80);
SELECT OrderID, Customer FROM dbo.Orders;

Output:

OrderID     Customer       
----------- ---------------
       1001 Hannah Brooks  
       1002 Tyler Reed     
       1003 Grace Miller   
       1004 Chloe Grant

How does this work? RESEED, 1003 sets the current value, and since rows remained, the next insert used 1003 + 1.

NORESEED only reports the current value; it changes nothing.

After TRUNCATE TABLE (or on a table that never had rows) the next row gets the reseed value itself, not value + 1.

Reseeding below the highest existing ID on a primary key gives you duplicate-key errors later. Cleaning up first? See delete duplicate rows in SQL Server.

Add Identity to an Existing Column in SQL Server

You can’t do it directly: ALTER COLUMN to identity is a syntax error (shown below). Dropping and re-adding it renumbers every row.

The workaround that keeps values: SWITCH the data into an identical table that has IDENTITY. It only moves metadata.

SET NOCOUNT ON;
CREATE TABLE dbo.Invoices (InvoiceNo int NOT NULL PRIMARY KEY, Amount decimal(8,2) NOT NULL);
INSERT INTO dbo.Invoices VALUES (1, 250.00), (2, 99.95), (3, 410.00);

CREATE TABLE dbo.Invoices_New (
    InvoiceNo int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Amount    decimal(8,2) NOT NULL
);
ALTER TABLE dbo.Invoices SWITCH TO dbo.Invoices_New;
DROP TABLE dbo.Invoices;
EXEC sp_rename 'dbo.Invoices_New', 'Invoices';
DBCC CHECKIDENT ('dbo.Invoices', RESEED);

INSERT INTO dbo.Invoices (Amount) VALUES (75.00);
SELECT InvoiceNo, Amount FROM dbo.Invoices;

Output:

Caution: Changing any part of an object name could break scripts and stored procedures.
Checking identity information: current identity value 'NULL', current column value '3'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
InvoiceNo   Amount    
----------- ----------
          1     250.00
          2      99.95
          3     410.00
          4      75.00

How does this work? The new table matches column for column except for IDENTITY.

After the switch, DBCC CHECKIDENT with RESEED saw current value NULL and max 3, so it moved the counter to 3. The next invoice got 4.

Skip that reseed and the next insert tries InvoiceNo 1 and hits the primary key. On a live system, wrap it in one transaction.

To remove identity from a column, SWITCH the other way into a plain int copy (I tested that too). The slower fallback is an INSERT INTO … SELECT copy with SET IDENTITY_INSERT ON.

Find Identity Columns With sys.identity_columns

To list every identity column in a database, with seed, increment and last value:

SET NOCOUNT ON;
SELECT CAST(OBJECT_NAME(object_id) AS varchar(10)) AS TableName,
       CAST(name AS varchar(8))                    AS ColumnName,
       CAST(seed_value AS int)                     AS Seed,
       CAST(increment_value AS int)                AS Incr,
       CAST(last_value AS int)                     AS LastValue
FROM sys.identity_columns
WHERE OBJECTPROPERTY(object_id, 'IsUserTable') = 1;

Output:

TableName  ColumnName Seed        Incr        LastValue  
---------- ---------- ----------- ----------- -----------
Orders     OrderID           1001           1        1005
OrderAudit AuditID           9001           1        NULL

OrderAudit shows NULL. No rows yet. If you also need each column’s type, see check the data type of a column.

Identity Column Gotchas: Msg 2749, 2744, 156, 8102, 544 and 334

All six came from the same Orders table. EXEC inside TRY…CATCH also catches compile errors:

SET NOCOUNT ON;
BEGIN TRY EXEC (N'CREATE TABLE dbo.T1 (Code nvarchar(10) IDENTITY);'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY EXEC (N'ALTER TABLE dbo.Orders ADD Seq int IDENTITY;'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY EXEC (N'ALTER TABLE dbo.Orders ALTER COLUMN Seq int IDENTITY;'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY EXEC (N'UPDATE dbo.Orders SET OrderID = 2000 WHERE OrderID = 1001;'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY EXEC (N'INSERT dbo.Orders (OrderID, Customer, City, Amount) VALUES (1, N''A'', N''B'', 1);'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
BEGIN TRY EXEC (N'INSERT dbo.Orders (Customer, City, Amount) OUTPUT inserted.OrderID VALUES (N''A'', N''B'', 1);'); END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

Output:

Msg 2749: Identity column 'Code' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, unencrypted, and constrained to be nonnullable.
Msg 2744: Multiple identity columns specified for table 'Orders'. Only one identity column per table is allowed.
Msg 156: Incorrect syntax near the keyword 'IDENTITY'.
Msg 8102: Cannot update identity column 'OrderID'.
Msg 544: Cannot insert explicit value for identity column in table 'Orders' when IDENTITY_INSERT is set to OFF.
Msg 334: The target table 'dbo.Orders' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Fixes: 156 means use the SWITCH method; 8102 means delete and re-insert with IDENTITY_INSERT on. For 544 see explicit value for the identity column and IDENTITY_INSERT in SQL Server.

Things to Keep in Mind With an Identity Column

• Identity doesn’t make a column unique. Keep the PRIMARY KEY.
• Gaps are normal. Don’t build invoice numbering that must be gapless on identity.
• Reseeding below the max ID gives duplicate-key errors on a PK.

Next step, or the same job on other engines:

• Auto-increment columns in PostgreSQL
• MariaDB AUTO_INCREMENT
• Return the new ID through an OUTPUT parameter

Frequently Asked Questions