11. IDENTITY – Auto Number Generator

In SQL Server, the IDENTITY column is used to automatically generate unique numbers for each record. It is commonly used for primary keys.

1. What is IDENTITY?

IDENTITY is a property that automatically increases the value of a column whenever a new row is inserted. It helps in generating unique IDs without manual input.

2. Syntax

IDENTITY(seed, increment)

• seed → Starting value
• increment → Step value (increase amount)

3. Example

CREATE TABLE Students (
    ID INT IDENTITY(1,1),
    Name VARCHAR(50)
);

Here, ID will start from 1 and increase by 1 automatically.

4. Insert Example

INSERT INTO Students (Name)
VALUES ('Ravi'), ('Anu');

Result:

ID	Name
1	Ravi
2	Anu

5. Key Points

• Automatically generates unique numbers
• No need to manually insert ID values
• Mostly used for primary keys
• Ensures uniqueness of records

6. Important Notes

• Identity value increases automatically
• Cannot reuse deleted values automatically
• TRUNCATE resets identity
• DELETE does not reset identity

7. Identity Reset

Example:

DBCC CHECKIDENT ('Students', RESEED, 0);

After this, the next inserted record will start from 1 again.

Conclusion

IDENTITY is a powerful feature in SQL Server that automatically generates unique numbers. It simplifies data insertion and ensures each record has a unique identifier.

Your learning journey continues 🚀