|
I'm trying to find the answer to this question.
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?
thanks
|
|
|
|
|
Not important.
The point with autoincrementation is that your key field has unique values, and gaps in the sequence does not matter.
And if it matters, you should probably rethink your design.
People say nothing is impossible, but I do nothing every day.
|
|
|
|
|
Ok so you don't know. Anyone else?
|
|
|
|
|
That's pretty rude: he gave you an 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
me, me, me
|
|
|
|
|
Rude? If I didn't need the answer I wouldn't have asked the question.
|
|
|
|
|
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
me, me, me
|
|
|
|
|
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
Thanks
|
|
|
|
|
piticcotoc wrote: 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.
|
|
|
|
|
Quote: It's called free advice, and sometimes comes unsollicited.
So do STDs but that doesn't mean I need them
|
|
|
|
|
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 
|
|
|
|
|
piticcotoc wrote: I did move on.
No you didn't: you were rude which is uncalled for. You should have ignored it. Now you have just shown that you are an arrogant, ungrateful smart-ass. Good luck with getting any more help.
"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
me, me, me
|
|
|
|
|
Contrarywise; you don't need to know and yet you asked anyway.
|
|
|
|
|
Mind-reading is a great feature to have. This way you know what I want better than me... oh wait; you don't and yet you assumed otherwise anyway
|
|
|
|
|
Why don't you just try it and find out for sure instead of being rude to people trying to help. 
BEGIN TRANSACTION
INSERT something INTO table
ROLLBACK TRANSACTION
|
|
|
|
|
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.
|
|
|
|
|
Thanks guys. Exactly what I needed to find out.
|
|
|
|
|
Identity (auto-increment) is not part of the transaction, so isn't rolled back. In your example, the ID for the next call would be 102.
|
|
|
|
|
Thanks guys. Exactly what I needed to find out.
|
|
|
|
|
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
|
|
|
|
|
|
Hi,
I think You are asking about "IDENTITY" Property in SQL Server right. If you will use IDENTITY while creating table, It will Increment Automatically based on your Starting value and Increment.
For Example,
CREATE TABLE #T1 (ID INT NOT NULL IDENTITY(1,1), Name VARCHAR(12))
INSERT INTO #T1(Name) VALUES('A')
SELECT * FROM #T1
BEGIN TRAN T1
INSERT INTO #T1(Name) VALUES('B')
ROLLBACK TRAN T1
INSERT INTO #T1(Name) VALUES('C')
SELECT * FROM #T1
So Once Identity values is Incremented, In case If Transaction Roll backed also you will get next value only.
Regards,
GVPrabu.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
