What are identity columns and sequences?

Identity Columns and Sequences

Both are used to generate numeric values automatically, usually for primary keys.

1. Identity Column

An identity column automatically generates a number whenever a new row is inserted.

Syntax

IDENTITY(seed, increment)

• Seed — starting value

• Increment — amount added for each new row

Example

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100)
);

INSERT INTO Employees (EmployeeName)
VALUES ('Ravi'), ('Priya');

Result:

IDENTITY(1,1) starts at 1 and increases by 1.

Getting the generated identity

SELECT SCOPE_IDENTITY();

This returns the last identity value generated in the current scope.

2. Sequence

A sequence is a separate database object that generates numbers.

Unlike identity, it is not directly tied to one table.

Create a sequence

CREATE SEQUENCE EmployeeSequence
    AS INT
    START WITH 1
    INCREMENT BY 1;

Use the sequence

INSERT INTO Employees (EmployeeId, EmployeeName)
VALUES (NEXT VALUE FOR EmployeeSequence, 'Ravi');

Get the next value directly:

SELECT NEXT VALUE FOR EmployeeSequence;

The same sequence can be used by multiple tables.

Main Differences

Restart examples

Identity:

DBCC CHECKIDENT ('Employees', RESEED, 0);

Sequence:

ALTER SEQUENCE EmployeeSequence
RESTART WITH 1;

Important point

Neither identity nor sequence guarantees gap-free numbers. Gaps may occur because of:

• Rolled-back transactions

• Failed inserts

• Deleted records

• SQL Server restart or value caching

Therefore, do not use them when legally gap-free numbering is required.

Interview answer

An identity is a property of a particular table column that generates values during inserts. A sequence is an independent database object that generates values on request and can be shared across multiple tables.