I have written a script to do a little experiment:
use MyDatabase;
declare @variable int;
set @variable = 5;
begin tran test
set @variable = 6;
rollback tran test;
select @variable;
As you can see, @variable
is declare
d at the start of the script, initialized with 5 and then inside a transaction
it is set to 6. After the transaction
is rolled back, we display @variable
, which still has a value of 6, despite the fact that its value change was inside a transaction
which was rolled back since then. I would have expected the selection to yield a result of 5. What is the cause of this behavior?
Because transactions are used to maintain the real data, the data in tables (not variables, not table variables). That's about all there is to it.
This is useful for scenarios where you may want to rollback but return some individual value from within the transaction as an indication of why you rolled back (perhaps throwing or just retuning a variable status code).