Let's say you have a MSSQL database which has a table with an auto-incremented field. If I have a Sql transaction sent to the server (through code) that does an insert into that table and the transaction fails (and does a rollback) will the current ID be lost or not? When does it generate the new ID? At transaction start or finish?
Ex: table1 has column ID as auto-increment. the current ID is 100. If I send a transaction with an insert does is reserve the ID 101 and loose it if the transaction fails with rollback, meaning next ID would be 102? Or will it take the ID 101 only if the transaction commits successfully?
Maybe we can chalk this down to a lost in translation but what Jorgen said is correct.
Identity columns allow you to assign a unqiue value to each row. It doesn't get reset to the previous value if you rollback the transaction.
If you require no gaps in your keys then you will need to think of a different approach.
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
Now you're just being a twat. He gave you an answer. You should have just moved on. What's the incentive for anyone else to help you in the future if they think you'll just be rude if you don't like the answer.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair. nils illegitimus carborundum
I did move on. Until someone gave an answer to my question which was all i needed to know. Why do some people think that a question must always be answered with with an assumption that everything you do is wrong and you should change your thinking according to theirs? Did any of you even bothered to ask why I needed the answer to that question? (except the 2 straight to the point answers - thanks again). No you didn't. You just assumed it is something vital to what I do and you advised me to change my logic.
So to sum it up and end this hopefully:
- i got the answer I needed which is great - i was looking for an answer and not a solution - the mayans were wrong about the world end (along with whoever misunderstood what they were trying to say) - check above fact and see if you find any analogy with this now turning into a flame thread
Why do some people think that a question must always be answered with with an assumption that everything you do is wrong
..because we never use the value of an identity field; it does not (EVER) hold information. Asking what the number is indicates that you might indeed have a design-problem, and that might lead to problems in the long run.
It's called free advice, and sometimes comes unsollicited.
Same goes for taxes, but the fact they have a single thing in common doesn't make them comparable.
This is "normal behaviour" when asking an expert for advice. Ask a builder how to build on quicksand. You can expect an answer, and a warning. You can ask the doctor to remove your lungs - and get an answer and a warning. Same goes here.
Even when being paid for the job, I will not shut up whn someone is making a mistake that could cost them dearly. I´m not that much of an ass yet
I do know, and I did tell you to rethink your design.
If you had used Oracle it would have been much worse, Oracle cache the sequences, so if you restart the server you can get a gap of twenty numbers. and if you have a cluster server they won't even be in order. (<for the nitpicks> I know it's a setting, but if you want higher performance you don't change it</for the nitpicks>)
People say nothing is impossible, but I do nothing every day.
The new IDENTITY value is generated when a row insert is initiated and will update the table's current identity value even when the transaction is rolled back. In simple terms, it would behave as if no rollback happened.
While Shameel and Russel gave you answers it is not the solution, if you are relying on the identity filed to give you sequential information (or any information) your design is WRONG. An ID field has only 1 function, to identify the record.
Never underestimate the power of human stupidity RAH