CODEWITHSUNDEEP
sql-server-2012
VinodKumar
VinodKumar

Reputation: 33

Identity on a primary key column in SQL Server

I have created an Employee table with the following columns and constraints:

EmpId(Primary Key, Identity(1,1),not null)
EmpName
EmpJob
etc.

Then I tried to insert Employee values from an aspx page through ADO.NET, unfortunately it had generated a wrong sequence of EmpId of Employee and the sequence is 

2 3 4 5 6 7 9 17 18.

I don't know why it is behaving like that, so please help me.

Upvotes: 0

Views: 33

Answers (1)

Allan S. Hansen
Allan S. Hansen

Reputation: 4081

Identity isn't guaranteed to be without gaps in the sequence. It shouldn't be used for that because of this. Situations like cancelled/roll-backed queries and/or deleted entries will create gaps in the sequence.

Identity is used to provide a "unique" id in terms of concurrent transactions based on the current 'seed' value. But it does not guarantee no gaps (entires can be deleted, queries can be rolled back), uniqueness across multiple transactions (as the seed can change) or similar.

If you want to have an unbroken sequence of integers, you'll have to maintain it yourself after each insert/update/delete of entries. But generally speaking - such a sequence shouldn't be needed for most any situation because more often then not you would merely be interested in the ordering/relative position when using identity as ordering (for example).

Upvotes: 1

Related Questions