IDENTITY in SQL

🆔 The IDENTITY property in SQL is used to automatically generate unique numeric values for a column whenever a new row is inserted. It is most commonly used in PRIMARY KEY columns to eliminate the need for manually assigning unique IDs.

📖 What is IDENTITY?

The IDENTITY property is primarily associated with SQL Server. It automatically generates sequential numeric values based on a specified starting value (seed) and increment. Each new row receives the next available value.

Information

IDENTITY is the SQL Server equivalent of MySQL's AUTO_INCREMENT. Other database systems provide similar features using different syntax.

đŸŽ¯ Why Use IDENTITY?

The IDENTITY property simplifies database design and ensures that every row receives a unique identifier automatically.

  • 📌 Automatically generate unique IDs.
  • 📌 Eliminate manual key management.
  • 📌 Reduce duplicate key errors.
  • 📌 Simplify data insertion.
  • 📌 Improve relational database design.

📝 Basic Syntax

IDENTITY Syntax

CREATE TABLE table_name
(
    id INT IDENTITY(seed, increment) PRIMARY KEY,
    column_name data_type
);

🔍 Understanding Seed and Increment

The IDENTITY property accepts two values:

ParameterDescriptionExample
SeedThe first value generated.1
IncrementThe value added for each new row.1

💡 Create a Table with IDENTITY

Create a Students table where StudentID is generated automatically.

Students Table

CREATE TABLE Students
(
    StudentID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Department VARCHAR(100)
);

➕ Insert Records

Since the StudentID is generated automatically, it is omitted from the INSERT statement.

Insert Data

INSERT INTO Students (Name, Department)
VALUES
('Alice', 'Computer Science'),
('Bob', 'Mathematics'),
('Charlie', 'Physics');

📊 Result

StudentIDNameDepartment
1AliceComputer Science
2BobMathematics
3CharliePhysics

đŸ”ĸ Custom Seed and Increment

You can specify custom starting and increment values.

Custom IDENTITY Values

CREATE TABLE Employees
(
    EmployeeID INT IDENTITY(1000,5) PRIMARY KEY,
    EmployeeName VARCHAR(100)
);

Generated values will be:

InsertGenerated EmployeeID
First1000
Second1005
Third1010
Fourth1015

⚠ī¸ Inserting Explicit Identity Values

By default, SQL Server does not allow explicit values to be inserted into an IDENTITY column. To do so temporarily, enable IDENTITY_INSERT.

Insert Explicit Identity Value

SET IDENTITY_INSERT Students ON;

INSERT INTO Students
(StudentID, Name, Department)
VALUES
(100, 'David', 'Physics');

SET IDENTITY_INSERT Students OFF;

Warning

Only one table in a session can have IDENTITY_INSERT enabled at a time.

⚖ī¸ IDENTITY vs AUTO_INCREMENT

FeatureIDENTITYAUTO_INCREMENT
DatabaseSQL ServerMySQL
Automatic Numbering✅ Yes✅ Yes
Custom Start Value✅ Yes✅ Yes
Custom Increment✅ YesLimited to table-level configuration

🗄ī¸ Equivalent Features in Other Databases

Database SystemEquivalent Feature
SQL Server IDENTITY(seed, increment)
MySQL AUTO_INCREMENT
PostgreSQL GENERATED ... AS IDENTITY or SERIAL.
Oracle GENERATED ... AS IDENTITY.
SQLite INTEGER PRIMARY KEY with optional AUTOINCREMENT.

đŸ’ŧ Real-World Example

A hospital management system automatically assigns a unique patient ID each time a new patient registers, ensuring every patient record can be identified without manual numbering.

Patients Table

CREATE TABLE Patients
(
    PatientID INT IDENTITY(1,1) PRIMARY KEY,
    PatientName VARCHAR(100),
    AdmissionDate DATE
);

⚠ī¸ Common Mistakes

  • ❌ Assuming identity values are always gap-free.
  • ❌ Manually inserting identity values without enabling IDENTITY_INSERT.
  • ❌ Using identity values as meaningful business numbers.
  • ❌ Expecting identity values to reset automatically after deleting rows.

Warning

Identity values may contain gaps because of rollbacks, failed inserts, deletions, or server restarts. They should be treated as unique identifiers, not guaranteed consecutive numbers.

⚠ī¸ Best Practices

Best Practice

Use IDENTITY for surrogate primary keys, allow the database to generate values automatically, avoid attaching business meaning to generated IDs, use foreign keys to relate tables, and only enable IDENTITY_INSERT when absolutely necessary.

🚀 Key Points to Remember

  • 📌 IDENTITY automatically generates numeric values.
  • 📌 It is primarily a SQL Server feature.
  • 📌 The syntax is IDENTITY(seed, increment).
  • 📌 It is commonly used with PRIMARY KEY columns.
  • 📌 Identity values are unique but may not be consecutive.
  • 📌 Similar functionality exists in other database systems under different names.
>>"Automatic identity columns simplify database design by letting the database manage unique identifiers for every new record."

Summary

✅ The IDENTITY property is a convenient SQL Server feature for automatically generating unique numeric values. It reduces manual effort, prevents duplicate identifiers, and is widely used for primary keys in relational database applications.