Identity Column in SQL Server

In SQL Server, an identity column is a column that automatically generates a unique numeric value for each row inserted into a table. It is commonly used as a primary key or a surrogate key for uniquely identifying records within a table.

Here's an example of creating a table with an identity column in SQL Server:

CREATE TABLE MyTable
(
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(50),
    Age INT
);

In this example, the column "ID" is defined as an identity column using the IDENTITY keyword. The IDENTITY(1,1) specifies that the column will start with a seed value of 1 and increment by 1 for each new row inserted. The PRIMARY KEY constraint is used to enforce uniqueness on the identity column, ensuring that each value is unique within the table.

When inserting data into the table, you don't need to provide a value for the identity column. SQL Server will automatically generate a unique value for each inserted row. Here's an example of inserting data into the table:

INSERT INTO MyTable (Name, Age)
VALUES ('John Doe', 30), ('Jane Smith', 25);

In this example, the identity column "ID" will be populated automatically by SQL Server, and the resulting values might be something like:

You can also retrieve the generated identity values using the SCOPE_IDENTITY() function immediately after an insert operation to obtain the last identity value generated within the current session.

Note that the identity column can only be defined on integer or decimal numeric data types. It provides a convenient way to generate unique values without explicitly specifying them during data insertion.