What Is an IDENTITY Column?
An IDENTITY column is a column whose value is generated automatically by SQL Server whenever a new row is inserted. You never need to supply a value for it yourself — the server gives each row its own unique number in sequence. This is why IDENTITY columns are used almost everywhere as primary keys for tables like users, orders, and products.
The idea is simple — no user should have to invent their own ID. When someone signs up on a website, you don't ask them to pick a customer number. The database picks one for them. That's exactly what an IDENTITY column does behind the scenes.
The Two Settings: Seed and Increment
When you create an IDENTITY column, you set two small values:
Seed — the starting value of the very first row.
Increment — how much each new row's ID goes up by.
The default is IDENTITY(1, 1) — start at 1, add 1 every new row. You can customize both if needed. For example, IDENTITY(1000, 5) starts at 1000 and jumps by 5 each time.
Creating a Table With an IDENTITY Column
Let's build a simple Employees table where EmpId is auto-generated.
CREATE TABLE Employees (
EmpId INT IDENTITY(1, 1) PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10, 2)
);
That's it. SQL Server will now fill in EmpId automatically on every insert.
Inserting Rows Without Giving an ID
INSERT INTO Employees (EmpName, Salary) VALUES ('Anita', 45000);
INSERT INTO Employees (EmpName, Salary) VALUES ('Rohit', 60000);
INSERT INTO Employees (EmpName, Salary) VALUES ('Priya', 52000);
SELECT * FROM Employees;
Result — SQL Server filled in EmpId for us:
| EmpId | EmpName | Salary |
|---|---|---|
| 1 | Anita | 45000 |
| 2 | Rohit | 60000 |
| 3 | Priya | 52000 |
Notice we never wrote 1, 2, or 3. SQL Server auto-generated each value starting at the seed (1) and jumping by the increment (1).
What Happens When You Delete Rows?
Let's say we delete the row for Anita. What happens if we insert a new employee right after?
DELETE FROM Employees WHERE EmpId = 1;
INSERT INTO Employees (EmpName, Salary) VALUES ('Karan', 48000);
SELECT * FROM Employees;
Result:
| EmpId | EmpName | Salary |
|---|---|---|
| 2 | Rohit | 60000 |
| 3 | Priya | 52000 |
| 4 | Karan | 48000 |
EmpId = 4, not 1. SQL Server never reuses old IDs by default, even if rows are deleted. This is intentional — keeping IDs stable means old deleted records don't come back under someone else's number.
Inserting Explicit Values: IDENTITY_INSERT
Sometimes you really do need to write your own ID value — for example, while restoring a deleted record or migrating data from another system. Normally SQL Server blocks you:
-- This will FAIL by default
INSERT INTO Employees (EmpId, EmpName, Salary)
VALUES (1, 'Anita', 45000);
-- Error: Cannot insert explicit value for identity column...
To allow it, temporarily turn on IDENTITY_INSERT for that table:
SET IDENTITY_INSERT Employees ON;
INSERT INTO Employees (EmpId, EmpName, Salary)
VALUES (1, 'Anita', 45000);
SET IDENTITY_INSERT Employees OFF;
Two things to remember when using this:
1. You must list the columns. The shortcut INSERT INTO Employees VALUES (...) will not work — you have to spell out the column names including the IDENTITY column.
2. Always turn it back OFF. SQL Server only allows IDENTITY_INSERT to be ON for one table at a time in your session. Forgetting to turn it off can block work on other tables.
IDENTITY_INSERT ON at any moment in a session. Turn it OFF as soon as you are done with your explicit inserts.
Resetting the IDENTITY Counter: DBCC CHECKIDENT
What if you want the counter to start over? For example, after you've deleted every row from the table for testing, and you want the next insert to go back to 1. Deleting rows alone won't reset the counter — you need DBCC CHECKIDENT.
-- Option A: DELETE keeps the IDENTITY counter running
DELETE FROM Employees;
-- Option B: TRUNCATE resets the counter automatically
TRUNCATE TABLE Employees;
-- Option C: Manually reset the counter to 0
DBCC CHECKIDENT ('Employees', RESEED, 0);
-- Next insert will get ID = 1 (because increment is 1)
INSERT INTO Employees (EmpName, Salary) VALUES ('New Hire', 40000);
After the reseed, the next inserted row gets seed + increment → in our case 0 + 1 = 1. You can pass any number to RESEED — for example, RESEED, 1000 would make the next ID start at 1001.
Useful Tricks with IDENTITY
Check the Current IDENTITY Value
DBCC CHECKIDENT ('Employees', NORESEED);
This only shows the current value without changing anything.
Get the Last Generated IDENTITY After an INSERT
INSERT INTO Employees (EmpName, Salary) VALUES ('Meera', 55000);
SELECT SCOPE_IDENTITY() AS LastId;
SCOPE_IDENTITY() is the safe and recommended way to get the ID of the row you just inserted. Avoid @@IDENTITY because it can return values from triggers on other tables.
IDENTITY vs UNIQUEIDENTIFIER (GUID)
| Feature | IDENTITY | UNIQUEIDENTIFIER (GUID) |
|---|---|---|
| Type | Sequential number | Random 16-byte value |
| Size | 4 or 8 bytes | 16 bytes |
| Unique across tables? | No | Yes, globally unique |
| Readable? | Yes (1, 2, 3 ...) | Hard to read |
| Best for | Single-database primary keys | Distributed systems, replication |
Common Mistakes
Mistake 1: Expecting DELETE to reset IDs. It does not. Only TRUNCATE or DBCC CHECKIDENT RESEED can reset the counter.
Mistake 2: Forgetting to turn IDENTITY_INSERT OFF. You will get strange errors the next time you try to insert into another table.
Mistake 3: Leaving gaps on purpose. Gaps are normal — deleted rows, rolled-back transactions, and failed inserts all cause gaps. Don't try to "fill" them.
Mistake 4: Using @@IDENTITY instead of SCOPE_IDENTITY(). If a trigger on the same table inserts into another IDENTITY table, @@IDENTITY returns the wrong value. Always prefer SCOPE_IDENTITY().
Mistake 5: Marking the wrong column as IDENTITY. Only make an IDENTITY column if users should never set the value. If the column has real business meaning (like invoice numbers with a required format), use a sequence or custom logic instead.
The Interview Answer
"An IDENTITY column is a column whose value is auto-generated by SQL Server on insert. It takes two settings — a seed (the starting value) and an increment (how much each row goes up by). You don't supply a value for it; the server does. If you ever need to insert your own value, you turn on
SET IDENTITY_INSERT ON. To reset the counter after clearing the table, you use DBCC CHECKIDENT or TRUNCATE TABLE."
Summary
An IDENTITY column is the easiest way to let SQL Server handle primary key generation. You define it with IDENTITY(seed, increment), and from then on every insert gets the next number automatically.
You cannot normally insert your own ID — but when you genuinely need to, SET IDENTITY_INSERT Employees ON allows it, and OFF puts the table back to normal. Deleting rows does not reset the counter; use TRUNCATE TABLE or DBCC CHECKIDENT (..., RESEED, 0) when you really want to start over. And after an insert, reach for SCOPE_IDENTITY() to safely get the ID that was just generated.
Remember these rules and the typical SQL Server interview question on this topic is nothing more than a short, confident walk-through.
| Concept | Key Takeaway |
|---|---|
| IDENTITY column | Auto-generated value for new rows |
| Seed | Starting value of the first row |
| Increment | How much each new row's ID grows by |
| DELETE | Does NOT reset the counter |
| TRUNCATE TABLE | Resets the counter automatically |
| SET IDENTITY_INSERT | Lets you insert explicit IDs (temporarily) |
| DBCC CHECKIDENT | Read or reseed the current counter |
| SCOPE_IDENTITY() | Safe way to get the last inserted ID |
| Gaps are normal | Deleted or failed inserts leave gaps |
| Interview line | "Seed + increment, auto-generated, never reused" |