SQL-Identity

In SQL Server, an Auto Increment or Identity column is a column in a table that automatically generates a unique value for each new row inserted into the table. This is typically used for primary key columns, where each row needs a unique identifier.

When to Use Identity Column:

• When you need a unique identifier for each row (commonly used for primary keys).
• When you don’t want to manually generate unique values and prefer automatic incrementation.
• When you want to ensure each inserted row gets a unique numeric identifier.

Key Concepts

• Identity - The column is defined with the IDENTITY property, which automatically generates numeric values when a new row is inserted.
• Auto Increment - A term commonly used for similar functionality in other databases, like MySQL. In SQL Server, this is achieved through the IDENTITY property.

Syntax for Identity Column

CREATE TABLE TableName (
    ColumnName INT IDENTITY(1,1) PRIMARY KEY,
    OtherColumn VARCHAR(100)
);

• The first 1 specifies the seed (the starting value).
• The second 1 specifies the increment (the value to add for each new row).

Example of Using an Identity Column

CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    EmployeeName VARCHAR(100),
    Department VARCHAR(50)
);

-- Inserting data
INSERT INTO Employees (EmployeeName, Department)
VALUES ('John Doe', 'HR'),
       ('Jane Smith', 'IT'),
       ('Emily Johnson', 'Finance');

-- Result: 
-- EmployeeID values will be automatically generated starting from 1.

After inserting the data, the EmployeeID values would automatically be:

• John Doe → EmployeeID = 1
• Jane Smith → EmployeeID = 2
• Emily Johnson → EmployeeID = 3

Customizing Identity Column

CREATE TABLE Products (
    ProductID INT IDENTITY(1000,5) PRIMARY KEY,  -- Starts at 1000 and increments by 5
    ProductName VARCHAR(100)
);

This will create an identity column where the first value is 1000 and each subsequent value increases by 5 (1000, 1005, 1010, etc.).