🔢 SQL `IDENTITY` – Auto-Increment Columns in SQL Tables

The IDENTITY property in SQL is used to auto-generate numeric values for a column, often used for primary keys. It simplifies record insertion by eliminating the need to manually provide a unique ID.

This is especially useful in scenarios where you want each row to have a unique, sequential value—like auto-generated customer IDs, invoice numbers, or employee records.

📘 What is the SQL `IDENTITY` Property?

In SQL Server and other RDBMS that support it, IDENTITY is a property you assign to a numeric column to make it auto-increment. It automatically generates a new value for each inserted row.

IDENTITY(seed, increment)

Seed – The starting value.
Increment – The value to add for each new row.

🧾 SQL `IDENTITY` Syntax (SQL Server)

CREATE TABLE employees (
  employee_id INT IDENTITY(1,1) PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
);

In this example:

employee_id starts at 1 and increases by 1 for each new row.
You do not insert a value into employee_id manually.

✅ Example: Inserting Rows with `IDENTITY`

INSERT INTO employees (first_name, last_name)
VALUES ('John', 'Doe'),
       ('Jane', 'Smith'),
       ('Sam', 'Taylor');

Resulting table:

employee_id	first_name	last_name
1	John	Doe
2	Jane	Smith
3	Sam	Taylor

You don’t insert values into employee_id. SQL handles it automatically.

🔄 How to Get Last Inserted ID

In SQL Server, use:

SELECT SCOPE_IDENTITY();  -- Returns last identity value inserted in the current session

Other alternatives:

@@IDENTITY – May return values from triggers (less safe)
IDENT_CURRENT('table_name') – Returns last identity value across any session

⚙️ Controlling Identity Values

Reseeding Identity

DBCC CHECKIDENT ('employees', RESEED, 100);

This command resets the next identity value to 101.

Inserting Explicit Values (Rarely Recommended)

SET IDENTITY_INSERT employees ON;

INSERT INTO employees (employee_id, first_name, last_name)
VALUES (10, 'Alan', 'Walker');

SET IDENTITY_INSERT employees OFF;

⚠️ Use IDENTITY_INSERT with caution — it’s typically reserved for special use cases like data migration.

💡 Use Cases for `IDENTITY`

Auto-generating primary keys
Creating order numbers, ticket IDs, or customer IDs
Avoiding duplication without relying on external logic
Ensuring sequential data integrity

🧠 Best Practices

Tip	Why It’s Useful
Use `IDENTITY` for surrogate keys	Keeps your key logic simple
Always combine with `PRIMARY KEY`	Enforces uniqueness and performance
Don’t insert custom values unless needed	Avoid conflicts or sequence gaps
Consider reseeding with care	Only if necessary — may break existing references

📝 Summary

The SQL IDENTITY property auto-generates numeric values for a column
Commonly used for primary keys or unique row identifiers
Syntax: IDENTITY(seed, increment)
Use SCOPE_IDENTITY() to retrieve the most recent value inserted
Avoid manual inserts into identity columns unless explicitly needed

Identity Column

🔢 SQL IDENTITY – Auto-Increment Columns in SQL Tables

📘 What is the SQL IDENTITY Property?

🧾 SQL IDENTITY Syntax (SQL Server)

✅ Example: Inserting Rows with IDENTITY