Identity Column in SQL: A Complete Guide to Auto-Generated Numeric Values with Examples

Introduction

The Identity Column in SQL is a powerful feature that automatically generates numeric values for a column each time a new row is inserted. Often referred to as an AutoNumber column, it simplifies the process of creating unique identifiers for records in a table. This article provides a comprehensive guide to understanding and using identity columns, including syntax, examples, and customization options.

What is an Identity Column in SQL?

An Identity Column is a numeric column in a SQL table that automatically populates with an integer value whenever a new row is inserted. Key characteristics include:

• Auto-Generation: Values are automatically generated.
• Customizable: You can set a starting value (seed) and an increment value.
• Data Types: Typically defined as int, but can also be bigint, smallint, tinyint, numeric, or decimal (with a scale of 0).

Syntax for Identity Column

The basic syntax for creating an identity column is:

IDENTITY (data_type [ , seed , increment ] ) AS column_name
    

• data_type: The data type of the column (e.g., int, bigint).
• seed: The starting value (default is 1).
• increment: The value by which the column increments (default is 1).

Example of Identity Column in SQL

1. Basic Identity Column

Here’s an example of creating a table with an identity column:

CREATE TABLE Persons (
    Personid int NOT NULL IDENTITY(1, 1),
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (Personid)
);
    

• Personid is the identity column with a starting value of 1 and an increment of 1.

2. Customizing Seed and Increment Values

You can customize the starting value and increment:

CREATE TABLE Products (
    ProductId int NOT NULL IDENTITY(100, 5),
    ProductName varchar(255) NOT NULL,
    Price decimal(10, 2)
);
    

• ProductId starts at 100 and increments by 5 for each new row.

Advanced Example: Using Identity Column with SELECT INTO

You can create a new table with an identity column using the SELECT INTO statement:

USE DeveloperIndian;
GO
SELECT  IDENTITY(int, 90000, 1) AS ProductId,  
        Name AS pd_name,  
        ProductNumber, 
        ListPrice
INTO DeveloperIndian.SpecialProduct
FROM DeveloperIndian.Product;
  
-- Display new table
SELECT * FROM DeveloperIndian.SpecialProduct;
    

• This creates a new table SpecialProduct with an identity column ProductId starting at 90000 and incrementing by 1.

Key Points to Remember

1. Uniqueness: Identity columns ensure unique values for each row.
2. No Manual Insertion: You cannot manually insert values into an identity column.
3. Indexing: Identity columns do not require indexing, but they are often used as primary keys.
4. Reseeding: You can reset the identity value using DBCC CHECKIDENT.

Best Practices for Using Identity Columns

1. Use as Primary Key: Identity columns are ideal for primary keys due to their uniqueness.
2. Avoid Gaps: Be aware that deleting rows can create gaps in identity values.
3. Reseed When Necessary: Use DBCC CHECKIDENT to reset the identity value if needed.
4. Choose Appropriate Data Type: Use bigint if you expect a large number of rows.

Conclusion

The Identity Column in SQL is a versatile and essential feature for auto-generating numeric values in database tables. Whether you’re creating a simple table or customizing seed and increment values, identity columns simplify the process of managing unique identifiers. By following best practices, you can effectively use identity columns to enhance your database design and performance.