0

I have one table called customer_master that includes a column called cust_id with autoincrement set to 1.

When we try to insert records its working fine and inserted records like cust_id 1, 2, 3 and 4 are inserted, but when an error is generated in the insert command we do a transaction rollback, this means that cust_id 5 is not inserted, but when we are insert another record, cust_id generates 6. It skips cust_id 5.

I want to set it up so that if any error is generated in the insert command the identity is not incremented.

We are using c# and sql server 2005.

Improve this question

2 Answers 2

Reset to default
1

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!

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.