SQL IDENTITY

What is SQL IDENTITY?

IDENTITY marks a column as auto-incrementing so the database automatically generates unique numeric values for new rows.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL IDENTITY: SQL Server, Azure SQL Database, PostgreSQL (IDENTITY/Serial), Oracle 12c+, MariaDB 10.3+, IBM Db2, Teradata 16+, SAP ASE. MySQL uses AUTO_INCREMENT instead of the IDENTITY keyword.

SQL IDENTITY Full Explanation

IDENTITY is a column property that automatically assigns a sequential numeric value whenever a row is inserted. It eliminates the need to manually supply primary-key values and is commonly used for surrogate keys. In SQL Server, IDENTITY(seed, increment) defines the starting number and the step between numbers; the database guarantees uniqueness within the table but not across tables. Standard SQL introduced IDENTITY columns in SQL:2003 with the syntax GENERATED {ALWAYS | BY DEFAULT} AS IDENTITY, and other platforms implement comparable functionality (e.g., MySQL AUTO_INCREMENT, PostgreSQL SERIAL/IDENTITY). IDENTITY values cannot be updated directly unless IDENTITY_INSERT is set ON, and reseeding requires DBCC CHECKIDENT or ALTER TABLE ALTER COLUMN RESTART WITH in newer standards. Caveats: gaps appear after rollbacks or deletes; only one IDENTITY column is allowed per table in SQL Server; replication and bulk load tools may need NOT FOR REPLICATION to bypass automatic generation.

SQL IDENTITY Syntax

-- SQL Server
CREATE TABLE dbo.Orders (
    OrderID INT IDENTITY(1,1) PRIMARY KEY,
    CustomerID INT,
    OrderDate  DATETIME
);

-- Standard SQL
CREATE TABLE Orders (
    OrderID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    CustomerID INT,
    OrderDate  TIMESTAMP
);

SQL IDENTITY Parameters

seed (numeric) - first value generated
increment (numeric) - value added to the previous identity to create the next one
NOT FOR REPLICATION (flag) - prevents the identity from being incremented during replication or bulk insert
GENERATED ALWAYS | BY DEFAULT (keyword, Standard SQL) - controls whether users may supply their own value

Example Queries Using SQL IDENTITY

-- Basic create
CREATE TABLE dbo.Users (
    user_id INT IDENTITY(1000,1) PRIMARY KEY,
    username NVARCHAR(50) NOT NULL,
    created_at DATETIME DEFAULT GETDATE()
);

-- Insert without specifying the column
INSERT INTO dbo.Users (username) VALUES ('alice');
INSERT INTO dbo.Users (username) VALUES ('bob');

-- Retrieve the last identity value in this scope
SELECT SCOPE_IDENTITY() AS last_user_id;

-- Reseed the identity to start at 5000
DBCC CHECKIDENT ('dbo.Users', RESEED, 5000);

Expected Output Using SQL IDENTITY

The Users table is created with user_id starting at 1000 and incrementing by 1
Inserting two rows generates user_id 1000 and 1001 automatically
SCOPE_IDENTITY() returns 1001
The reseed statement changes the next generated value to 5001

Use Cases with SQL IDENTITY

Defining surrogate primary keys that require no business meaning
Bulk loading data where a unique row identifier is needed automatically
Migrating from composite natural keys to single-column keys
Generating audit table row numbers without sequences

Common Mistakes with SQL IDENTITY

Assuming identity values are gap-free; deletes and rollbacks leave holes
Expecting identity values to replicate across tables or servers without configuration
Attempting to insert explicit values without enabling IDENTITY_INSERT
Creating more than one IDENTITY column in a SQL Server table
Forgetting to reseed after large data purges, leading to unexpected high numbers

First Introduced In

SQL Server 6.0 (1995); standardized in SQL:2003

Frequently Asked Questions

What is the difference between IDENTITY and SEQUENCE?

IDENTITY is a table-bound auto-increment property, while SEQUENCE is a standalone object that can serve multiple tables and can be advanced manually.

How can I insert a specific value into an IDENTITY column?

Temporarily enable IDENTITY_INSERT ON for that table, insert the desired value, then set IDENTITY_INSERT OFF. Only one table per session may have IDENTITY_INSERT ON.

Does truncating a table reset the IDENTITY counter?

Yes. TRUNCATE TABLE resets the identity seed back to its original starting value unless it was reseeded manually.

Are IDENTITY columns automatically indexed?

If the column is defined as PRIMARY KEY or has a UNIQUE constraint, an index is created. Otherwise, IDENTITY alone does not create an index.