Insert data in to sql server data table

Question

Hi ,

I have one table customer_master and column cust_id is autoincrement 1 .when we are try to insert its working fine and inserted records like cust_id 1 and 2,3,4 but when generate error in insert command we do transaction rollback means cust_id 5 is not insert but when we are insert another record cust_id generate 6 . skip cust_id 5 . but i want to try if any error generate in insert command not increment identity and get last cust_id to next cust_id not any cust_id skip . So please give me reply as soon as possible. We are using c# and sql server 2005

Answer 1

You can use DBCC CHECKIDENT to reseed the identity column after an insert failure.

DBCC CHECKIDENT ( table_name, NORESEED ) returns the current identity value and the current maximum value of the identity column.

DBCC CHECKIDENT ( table_name, RESEED, new_reseed_value ) sets the current identity value to the new_reseed_value.

Answer 2

The reason SQL Server does this is for efficiency. If you need a sequence number without gaps you shouldn't be using identity you would need to implement your own scheme where concurrent transactions are blocked waiting for the next value just in case the initial transaction rolls back.

The second query here could be used for that purpose. But do you really need this? If it is purely for aesthetic purposes my advice is not to worry about it!