LearnAI ToolsCareerPractice BuildsPlayContact
Lesson 1418 min read

Identity Columns

Learn how IDENTITY(seed, increment) auto-generates primary key values in SQL Server, and how to retrieve the last inserted ID with SCOPE_IDENTITY().

Introduction

Up to now, every example that needed a primary key value — StudentID, CourseID, EnrollmentID — required you to supply it manually. In real applications, that's rarely practical: you don't want every piece of code that inserts a row to first figure out the next unused ID. SQL Server's IDENTITY property solves this by having the engine generate the value automatically.

IDENTITY Syntax

Adding IDENTITY to an INT (or BIGINT, SMALLINT) column tells SQL Server to generate its value automatically on every INSERT, starting from a seed and increasing by a step you control.

USE SchoolDB;
GO


CREATE TABLE Departments (
    DepartmentID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
    DepartmentName NVARCHAR(100) NOT NULL
);


INSERT INTO Departments (DepartmentName) VALUES ('Computer Science');
INSERT INTO Departments (DepartmentName) VALUES ('Mathematics');
INSERT INTO Departments (DepartmentName) VALUES ('Physics');


SELECT * FROM Departments;
Result

Click Run to see what this code prints.

Notice that DepartmentID was never mentioned in any INSERT statement — SQL Server generated 1, 2, and 3 automatically, in order, because the column is marked IDENTITY.

Seed and Increment

IDENTITY(seed, increment) takes two numbers: the seed is the starting value, and the increment is how much each subsequent value increases by. IDENTITY(1, 1) — the overwhelmingly common choice — starts at 1 and increases by 1 each time.

CREATE TABLE TicketNumbers (
    TicketID INT NOT NULL IDENTITY(1000, 5) PRIMARY KEY,
    Description NVARCHAR(200) NOT NULL
);


INSERT INTO TicketNumbers (Description) VALUES ('First support ticket');
INSERT INTO TicketNumbers (Description) VALUES ('Second support ticket');


SELECT * FROM TicketNumbers;
Result

Click Run to see what this code prints.

Here, TicketID started at 1000 and jumped by 5 each insert — useful in scenarios like generating spaced-out reference numbers.

SCOPE_IDENTITY()

After inserting a row, you frequently need to know the identity value that was just generated — for example, to use a new DepartmentID immediately in a related INSERT. SCOPE_IDENTITY() returns the most recently generated identity value from the current session and scope.

INSERT INTO Departments (DepartmentName) VALUES ('Chemistry');


DECLARE @newDeptId INT = SCOPE_IDENTITY();
SELECT @newDeptId AS NewDepartmentID;
Result

Click Run to see what this code prints.

IDENT_CURRENT vs. SCOPE_IDENTITY

SQL Server actually offers three related functions, and picking the wrong one is a classic source of subtle bugs in multi-user systems.

FunctionScopeSession
SCOPE_IDENTITY()Current scope only (same stored procedure/batch)Current session
@@IDENTITYAny scope, including identities generated by triggersCurrent session
IDENT_CURRENT('TableName')Any scopeAny session — the true last value for that table overall
Which One to Use

SCOPE_IDENTITY() is almost always the right choice. @@IDENTITY can return the wrong value if a trigger on the table also inserts into another IDENTITY table, and IDENT_CURRENT() ignores scope and session entirely, so it can return a value inserted by a completely different user at the same moment.

Resetting Identity Values

If you need to reset an IDENTITY column's counter — for example, after deleting test data — DBCC CHECKIDENT lets you reseed it manually.

-- Reset Departments' identity counter so the next insert starts at 1 again:
DBCC CHECKIDENT ('Departments', RESEED, 0);
Messages

Click Run to see what this code prints.

Common Mistakes

Avoid These Mistakes
  • Manually specifying a value for an IDENTITY column in an INSERT without enabling IDENTITY_INSERT first, which raises an error.
  • Using @@IDENTITY when SCOPE_IDENTITY() is what you actually need, especially on tables with triggers.
  • Assuming identity values are always perfectly sequential with no gaps — failed inserts and rolled-back transactions can consume a value that's never reused.
  • Forgetting that TRUNCATE TABLE resets an IDENTITY column back to its seed, which DELETE does not do.

Best Practices

  • Use IDENTITY(1, 1) as your default for primary key columns unless you have a specific reason to change the seed or increment.
  • Always retrieve a freshly generated key with SCOPE_IDENTITY(), not @@IDENTITY, unless you specifically need trigger-generated values.
  • Don't rely on IDENTITY values being gap-free — design your application logic around uniqueness and ordering, not perfect sequential continuity.
  • Reserve DBCC CHECKIDENT for deliberate maintenance tasks, and double-check the target table before running it.

Frequently Asked Questions

Yes, but only after explicitly enabling it with SET IDENTITY_INSERT TableName ON;, running your INSERT with an explicit value, and then setting it back OFF. This is typically reserved for data migrations, not everyday application code.

Gaps are normal and expected. Failed inserts, rolled-back transactions, and deleted rows all consume an identity value that is never reissued. Don't design logic that assumes IDENTITY values are perfectly consecutive.

Yes. SCOPE_IDENTITY() is scoped to the current session and the current batch/procedure, so it correctly returns only the value your own connection just generated, regardless of what other users are doing simultaneously.

No, a table may have at most one IDENTITY column, and it's almost always paired with PRIMARY KEY.

Key Takeaways

  • IDENTITY(seed, increment) makes SQL Server auto-generate sequential values for a column, typically a primary key.
  • SCOPE_IDENTITY() is the safe, standard way to retrieve the identity value your own INSERT just generated.
  • @@IDENTITY and IDENT_CURRENT() exist but carry pitfalls around triggers and cross-session values respectively.
  • IDENTITY values can have gaps; don't rely on them being perfectly sequential.

Summary

You've now covered SQL Server fundamentals end to end: installation, SSMS, databases, T-SQL basics, data types, tables, constraints, and identity columns for auto-generated keys. With a solid schema finally in place, it's time to start putting data into it — the next lesson covers the INSERT statement in depth.

Next Lesson →

INSERT Statement