Understanding Identity Columns

Maintaining a unique identifier for each row is one of the most basic requirements of good table design, and in SQL Server 2025 the most common way to meet it is an identity column. An identity column generates a sequential numeric value automatically every time a row is inserted, without the caller having to supply one, which makes it a natural fit for a primary key: no application code has to track the next available value or worry about two rows colliding on the same one.

A table can have only one identity column, and that column has to follow a specific set of rules: it must use an integer or exact-numeric type with no fractional component, TINYINT, SMALLINT, INT, BIGINT, DECIMAL(p,0), or NUMERIC(p,0), and it must be declared NOT NULL. SQL Server assigns the column two parameters when it's declared:

• Seed, the starting value given to the first row loaded into the table.
• Increment, the numeric step added to the previous row's value for every row after that.

The identity value is generated automatically at insert time and never changes afterward, even if the rest of the row is later updated.

An identity column is declared with IDENTITY(seed, increment). Leaving off both arguments defaults to IDENTITY(1, 1), numbering starting at 1 and increasing by 1 each time.

CREATE TABLE dbo.Products (
    ProductID   INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL,
    Price       DECIMAL(10, 2) NOT NULL
);

With the table defined this way, every insert that leaves ProductID out of its column list gets the next value in the sequence automatically:

INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Laptop', 999.99);     -- ProductID = 1

INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Smartphone', 699.99); -- ProductID = 2

Adding an identity column to a table that already has rows works the same way, and SQL Server automatically fills in sequential values for every existing row as part of the operation:

ALTER TABLE dbo.Employees
ADD EmployeeRecordID BIGINT IDENTITY(1000, 1) NOT NULL;

One limitation is worth knowing before you run into it: T-SQL has no way to add the IDENTITY property to a column that already exists with ALTER TABLE ... ALTER COLUMN; that statement can change a column's datatype or nullability, but not whether it's an identity column. To convert an existing column, you need to either add a new identity column and drop the old one, build a new table with the property and swap it in with ALTER TABLE ... SWITCH, or replace the approach entirely with a SEQUENCE object paired with a DEFAULT (NEXT VALUE FOR ...) constraint, which behaves similarly to an identity column but isn't tied to a single table.

Only a narrow set of exact numeric types qualify, since an identity value has to be a whole number with no ambiguity about what the "next" value actually is:

Data Type	Notes
TINYINT	1 byte, values 0 through 255; the smallest usable range for an identity column
SMALLINT	2 bytes, values -32,768 through 32,767
INT	4 bytes; the most common choice for an identity column
BIGINT	8 bytes; worth choosing up front for a table expected to grow past roughly two billion rows
DECIMAL(p,0) / NUMERIC(p,0)	Only valid with a scale of exactly 0; useful when a range larger than BIGINT is genuinely needed

A floating-point type like FLOAT or a type with a nonzero scale is never valid here; SQL Server rejects the IDENTITY property outright on any type that can't represent a clean sequence of whole numbers.

The same setting is available in the graphical Table Designer, which can also handle the table rebuild a conversion like this requires:

1. In Object Explorer, right-click the table and choose Design.
2. Select the target integer column in the column grid, and make sure Allow Nulls is unchecked for it.
3. In the Column Properties pane, expand Identity Specification.
4. Change (Is Identity) from No to Yes.
5. Set Identity Increment and Identity Seed as needed (both default to 1).
6. Press Ctrl+S to save.

As with other designer changes covered earlier in this module, turning an existing column into an identity column this way can require SSMS to rebuild the entire table behind the scenes, since it's effectively dropping and recreating the column. For a production table, running the equivalent T-SQL yourself is the safer route, since you can see exactly what it will do before committing to it.

Defining a new identity column is only half the question; just as often, especially when you've inherited a database someone else designed, you need to find out which columns already are identity columns. The sys.identity_columns catalog view answers that directly:

SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name                   AS TableName,
    ic.name                  AS IdentityColumnName,
    TYPE_NAME(ic.user_type_id) AS DataType,
    ic.seed_value            AS Seed,
    ic.increment_value       AS Increment,
    ic.last_value            AS LastGeneratedValue
FROM sys.identity_columns ic
INNER JOIN sys.tables t
    ON ic.object_id = t.object_id
ORDER BY SchemaName, TableName;

That single query surfaces every identity column in the database at once, along with its seed, increment, and the last value it actually generated, everything you'd need to understand an unfamiliar schema's numbering strategy without opening each table's definition individually.

Inside an ordinary query against one specific table, the $IDENTITY pseudocolumn is a shortcut that refers to whichever column is that table's identity column, without you needing to know its actual name:

SELECT $IDENTITY FROM dbo.Products;

This returns exactly the same values as SELECT ProductID FROM dbo.Products;, but the statement itself never names ProductID directly, which is useful in generic tooling or scripts meant to work against any table regardless of what its identity column happens to be called.

After an insert, it's common to need the identity value SQL Server just assigned, to use it as a foreign key in a related row, for instance. Three functions return it, and they differ in an important way that's easy to overlook:

• SCOPE_IDENTITY() returns the last identity value generated in the current session and the current scope, the same stored procedure, trigger, or batch that's actually running.
• @@IDENTITY returns the last identity value generated in the current session, in any scope, including a trigger that fired as a side effect of your own statement. That makes it easy to get back an unexpected value if an insert happens to fire a trigger that inserts into a different table with its own identity column of its own.
• IDENT_CURRENT('TableName') returns the last identity value generated for a specific table, regardless of session or scope, which makes it the only one of the three not scoped to what your own connection just did; two different connections inserting into the same table at the same time will both see the same, most-current value from this function.

Because of the trigger risk with @@IDENTITY, SCOPE_IDENTITY() is the safer default for ordinary application code that just needs the value its own statement produced:

INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Tablet', 399.99);

SELECT SCOPE_IDENTITY() AS NewProductID;

An identity column normally refuses an explicit value and generates its own, but data migrations and replication scenarios sometimes need to insert specific, pre-existing key values, say, restoring archived rows that already carried their original IDs elsewhere. SET IDENTITY_INSERT allows exactly that, for one table at a time:

SET IDENTITY_INSERT dbo.Products ON;

INSERT INTO dbo.Products (ProductID, ProductName, Price)
VALUES (100, 'Imported Record', 59.99);

SET IDENTITY_INSERT dbo.Products OFF;

Only one table per session can have IDENTITY_INSERT turned on at a time, and it's worth turning it back off as soon as the manual insert is done, so the table returns to generating its own values automatically for everything that follows.

It's a common assumption that an IDENTITY column can never hold a duplicate value, but the property itself doesn't enforce that; it only controls how new values are generated. Every example in this lesson paired IDENTITY with PRIMARY KEY, and it's the primary key constraint doing the actual uniqueness enforcement, not the identity property sitting next to it. Drop the primary key and keep only IDENTITY, and SQL Server will happily generate a value that's never been used before, while still allowing a duplicate of an existing value to be inserted explicitly through SET IDENTITY_INSERT, or to reappear after the seed is reset backward with DBCC CHECKIDENT, covered next.

In practice this distinction rarely causes trouble, because an identity column is almost always declared as a primary key or given an explicit UNIQUE constraint anyway. It's worth keeping straight conceptually, though: IDENTITY is a value-generation attribute, and PRIMARY KEY/UNIQUE are the constraints that actually guarantee no two rows ever share a value. Declaring one doesn't automatically imply the other.

DBCC CHECKIDENT both reports and corrects a table's current identity value. Run without a reseed argument, it simply reports the current value, and flags whether it's out of sync with the table's actual maximum:

DBCC CHECKIDENT ('dbo.Products');

This catches a real, if uncommon, problem: the current identity value can occasionally drift out of sync with the table's actual data, most often after a bulk load performed with identity values inserted explicitly via SET IDENTITY_INSERT, or after certain restore operations. When that happens, DBCC CHECKIDENT with a RESEED argument corrects it:

-- reseed so the next insert generates 501
DBCC CHECKIDENT ('dbo.Products', RESEED, 500);

Reseeding is also how you'd deliberately start a table's numbering over, during initial development, for instance, though doing so on a table that already has rows risks exactly the duplicate-value scenario described above if any of the old rows' identity values are still present.

A table with an identity column rarely ends up with a perfectly unbroken sequence of values, and that's expected behavior rather than a sign something's wrong. SQL Server reserves the next identity value as soon as an insert begins, before the statement is known to succeed, so a transaction that gets rolled back still permanently consumes the value it reserved:

BEGIN TRANSACTION;

INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Discontinued Item', 0.00);  -- reserves, say, ProductID = 50

ROLLBACK TRANSACTION;

INSERT INTO dbo.Products (ProductName, Price)
VALUES ('Next Real Item', 24.99);   -- gets ProductID = 51, not 50

The rolled-back insert's reserved value, 50 in this example, is gone for good; SQL Server never goes back to reclaim it. A server restart can leave a similar gap with values that were cached in memory but not yet written to disk, and deleting a row leaves one too, since deleting never reclaims or reuses a row's identity value for a future insert. None of this causes a correctness problem, since nothing about an identity column's contract promises a value for every integer in its range, only that each value it does generate is unique and increasing. If a column genuinely needs to be gap-free, that's a considerably more demanding requirement, usually handled with an explicit sequence table and serialized inserts rather than the ordinary IDENTITY property.

An identity column is tied to one specific table and generates values only for that table's own inserts. A SEQUENCE object, introduced in SQL Server 2012, is a separate, database-level object that can hand out numbers to several tables, or be called from application code directly, independent of any single table's inserts:

CREATE SEQUENCE dbo.OrderNumberSequence
    START WITH 1
    INCREMENT BY 1;

INSERT INTO dbo.Orders (OrderID, CustomerNumber)
VALUES (NEXT VALUE FOR dbo.OrderNumberSequence, 42);

Calling NEXT VALUE FOR directly, as shown here, also lets application code reserve a number before the row that will use it is actually inserted, something an identity column can never do, since it only produces a value at the moment a row is written. Reach for an identity column for ordinary per-table auto-numbering, and a sequence when several tables need to share one numbering source, or when the number has to exist before the insert happens.

In the next lesson, you'll learn about the ROWGUIDCOL property, an alternative to an identity column that marks a UNIQUEIDENTIFIER column as holding a row's globally unique identifier (GUID), useful when rows need unique keys that stay unique across multiple, independent databases rather than just within a single table.