begin transaction and commit transaction in MS SQL

Question

If I Enclose a query between Begin Transaction and commit transaction in MS SQL, what will happen if i abort or stop the execution of the query. Will all the changes that had been done during executing ROLLBACKED.?

Answer 1

Your transaction can remain open until you call something like ROLLBACK TRANSACTION or COMMIT TRANSACTION, or until SQL takes some action on it.

More info:

• SQL Server and connection loss in the middle of a transaction
• What happens to an uncommitted transaction when the connection is closed?
• What happens if you don't commit transaction in a database (say SQL Server)

I actually like to take advantage of this when testing large updates or corrections. You can have something like this:

-- BEGIN TRANSACTION
-- ROLLBACK TRANSACTION
-- COMMIT TRANSACTION

/*
    A bunch of SQL code here
*/

Then you can highlight/run the BEGIN TRANSACTION, then run the whole script. If you're happy with the results, you can highlight/run the COMMIT TRANSACTION. If not, run the ROLLBACK TRANSACTION. Since those lines are commented out, they don't affect your overall transaction unless you explicitly highlight and run them.

Answer 2

It depends on your code. As long as your transaction exist, all changes will be pending a rollback or a commit.

You might want to look at this.

Answer 3

No. Transaction will still be active - you didn't rollback after all, did you? :) Run this example and see what happens. If you break during transaction, you'll see value 2 is in a table, but you have to rollback or commit.

select 1 as x into #xxx 
GO


begin transaction

    insert into #xxx(x) select 2
    -- ctrl+break before time runs out.
    waitfor delay '00:00:10'

commit transaction


-- now try this:
select * from #xxx
rollback transaction
select * from #xxx