What Is Identity Data Type in SQL Server?

What Is Identity Data Type in SQL Server?

In SQL Server, the Identity data type is a special feature that allows you to automatically generate unique numeric values for a specific column in a table. It is commonly used to create primary keys or other unique identifiers for records.

How Does the Identity Data Type Work?

The Identity data type works by incrementing a value by a specified seed value and an increment value each time a new row is inserted into the table. By default, the seed value is 1 and the increment value is also 1.

For example, consider a table called Customers with an CustomerId column defined as an Identity. When you insert a new row into the Customers table without specifying a value for the CustomerId, SQL Server automatically generates a unique identifier for that column.

Syntax:

To define an Identity column in SQL Server, you can use the following syntax:

CREATE TABLE TableName
(
    ColumnName DataType IDENTITY (SeedValue, IncrementValue)
);

The IDENTITY (SeedValue, IncrementValue) keyword specifies the seed and increment values for the identity column. The seed value indicates the starting point for generating identities, while the increment value determines how much to increase the identity value by for each new row.

Note:

• The IDENTITY data type can only be used with integer-based data types such as INT, BIGINT, SMALLINT, or TINYINT.
• Each table can have only one column defined as an Identity.
• The identity values are unique only within the scope of the table, not across multiple tables.
• If you insert explicit values into an identity column, SQL Server will generate an error.

Example:

Let’s consider a practical example to better understand the usage of the Identity data type. Suppose we have a table called Employees with columns such as EmployeeId, Name, and Email. Here is how we can create this table with an identity column:

CREATE TABLE Employees
(
    EmployeeId INT IDENTITY(1, 1) PRIMARY KEY,
    Name VARCHAR(50),
    Email VARCHAR(100)
);

In this example, the EmployeeId column is defined as an identity column using the seed value of 1 and increment value of 1. This means that each new row inserted into the table will automatically generate a unique identifier starting from 1 and increasing by 1 for each subsequent row.

Note:

If you want to insert data into a table with an identity column, you can omit providing a value for that column in your INSERT statement. SQL Server will automatically generate a unique identifier for that column.

In conclusion, the Identity data type in SQL Server is a useful feature for automatically generating unique numeric values for columns. It simplifies the process of creating primary keys or other identifiers in your database tables. By using proper syntax and understanding its limitations, you can effectively leverage the power of the Identity data type in your SQL Server database designs.