CODEWITHSUNDEEP
sqlsql-server-2008
Jeevan Bhatt
Jeevan Bhatt

Reputation: 6101

Why Transaction does not work on Identity column?

Why transaction does not rollback Identity column. I have a table named "DetailReports" which has one Identity column. There are some SPS which manage data of DetailReports. Transactions are there based on condition now issue is that when we call rollback of transaction then we are not getting rollback of identity column. If its the nature of transaction then my question is WHY ?

Upvotes: 2

Views: 589

Answers (1)

RC_Cleland
RC_Cleland

Reputation: 2294

It does not have anything to do with your code or your program's code. It is how the system works. Take this analogy: You are standing in a queue at the bank and have take a number. You get a phone call and decide you will come back to the bank later. You place the number into the trash can on your way out. From the time you got your number and when you left many people may have joined the queue. The new queue members pulled consecutive number which are all higher than your number. It does not matter you threw your number away because the number ONLY indicates "who came next"

An identity number does not mean anything more than identifing a row in sequence just like the queue number from the bank. Do not worry about missing identity number because they happen.

If you have many missing identity number ask yourself "Why are there so many transaction rollbacks?", asuming you do not delete from the table of course.

Upvotes: 2

Related Questions