Understanding Identity Columns in SQL Server 2022

In modern database design, maintaining a unique identifier for each record is essential. In SQL Server 2022, this is most commonly achieved through an identity column. An identity column automatically generates sequential numeric values each time a new row is inserted, ensuring entity integrity without requiring manual input. This feature is ideal for defining primary keys and remains a foundational concept in Transact-SQL (T-SQL).

How Identity Columns Work

When you define a column as an identity column, SQL Server assigns it two important parameters:

1. Seed – The starting value of the sequence.
2. Increment – The numeric step added to the previous value for each new row.

The identity value is automatically generated during insertion and is not updated when other columns are modified. For example, the definition IDENTITY(1,1) means that the numbering will begin at 1 and increase by 1 for each new record (1, 2, 3, 4, …).

Example: Creating a Table with an Identity Column

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

Here, ProductID serves as the identity column and primary key. When you insert data, SQL Server automatically assigns the next available integer value:

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

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

SQL Server enforces uniqueness automatically, meaning you never need to manually manage key values or worry about duplicates.

Supported Data Types

Only certain numeric types can be used for identity columns in SQL Server:

• TINYINT
• SMALLINT
• INT (most common)
• BIGINT
• DECIMAL or NUMERIC (only when scale = 0)

Each table can contain only one identity column. The value of that column is automatically generated during each insert, ensuring that entity integrity is always maintained.

Retrieving and Managing Identity Values

When inserting data, it’s often useful to know which identity value was just generated. SQL Server provides several system functions and commands for this purpose:

• SCOPE_IDENTITY() – Returns the last identity value generated in the current execution scope (e.g., within the same stored procedure or batch).
• @@IDENTITY – Returns the last identity value generated globally on the current connection (can span triggers).
• IDENT_CURRENT('TableName') – Returns the last identity value for a specific table, regardless of connection or scope.

Example:

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

SELECT SCOPE_IDENTITY() AS NewProductID;

This returns the most recent identity value assigned within the same scope—ideal for referencing newly inserted rows in subsequent operations.

Temporarily Overriding Identity Behavior

While identity columns normally generate their own values, SQL Server provides a way to manually insert specific identity values when needed—such as during data migrations or replication. Use the SET IDENTITY_INSERT command to allow manual input:

SET IDENTITY_INSERT Products ON;

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

SET IDENTITY_INSERT Products OFF;

Only one table per session can have IDENTITY_INSERT enabled at a time. Once the manual insert is complete, you must turn the setting off to return to automatic sequencing.

Identity Columns vs. SEQUENCE Objects

While identity columns remain a simple and powerful way to generate numeric keys, SEQUENCE objects—introduced in SQL Server 2012—offer greater flexibility. A sequence is a database-level object that can generate numbers across multiple tables or sessions. Use identity columns when you need per-table auto-increment behavior; use sequences when you need centralized or custom number generation.

Key Takeaways

• Identity columns in SQL Server 2022 automatically generate unique numeric values for each inserted row.
• The IDENTITY(seed, increment) property defines how numbering starts and increases.
• They simplify primary key management, ensuring entity integrity and avoiding manual key tracking.
• You can use SCOPE_IDENTITY() to retrieve newly created IDs safely.
• Use SET IDENTITY_INSERT only when migrating or restoring data that includes explicit key values.

In the next lesson, we’ll explore the ROWGUIDCOL attribute, which supports globally unique identifiers (GUIDs) as an alternative key strategy for distributed database environments.