Click here to Skip to main content
15,610,690 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I had a strange experience with SQL Server's Identity column property.
I have a table with an identity column. This morning I noticed that SQL server skipped a value in that column.
Now the values are like

I am not seeing 104 here.

I have regenerated this issue by creating an exception while inserting data. whenever an exception occurs, SQL considers that as an insertion and skips that value in the next insertion attempt.

How I can solve this? I want this identity column value be sequential.

Thanks for your time

you have to add exception handling around insert operation.
if exception occured you have to reseed the identity value
dbcc checkident (mytable, reseed, 30)
with the last identity value
You will get more resource in here

Reseed identity value[^]

Retrieve identity value[^]
Share this answer
Hiren solanki 22-Dec-10 9:25am    
That's a good answer.
Sebastian T Xavier 22-Dec-10 9:54am    
Thank you
Validate all of the values necessary for insertion before attempting the insert statement itself and exit the process gracefully instead of only doing it on an insert exception.
As well, you may be able to wrap the process in a transaction. I'm not totally sure, but a transaction rollback may allow for the auto identity value to reset itself in that case.
Share this answer
Sebastian T Xavier 22-Dec-10 10:15am    
That option too I am studying.. Thanks
Corporal Agarn 22-Dec-10 14:27pm    
I too would have suggested this but not so well put.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900