CODEWITHSUNDEEP
sql-servertransactions
asmgx
asmgx

Reputation: 8044

Do Begin & End in SQL Server work as Transaction

In SQL Scripting in SQL Server

If I have a block of Begin ... End

BEGIN

UPDATE Table1
SET Column1 = 1

UPDATE Table2
SET Column2 = 2

UPDATE Table3
SET Column3 = 3

END

in this case does it mean that all 3 update statement should pass together ?

I mean if First & Second update were successful but the 3rd failed, does that mean all 3 updates will rollback? or Table1 & Table2 are updated only and no update to Table3

In another words

Is the script above equivalent to 

UPDATE Table1
SET Column1 = 1

UPDATE Table2
SET Column2 = 2

UPDATE Table3
SET Column3 = 3

or it is equivalent to 

BEGIN TRANSACTION;  

UPDATE Table1
SET Column1 = 1

UPDATE Table2
SET Column2 = 2

UPDATE Table3
SET Column3 =  3

COMMIT;

Upvotes: 0

Views: 1567

Answers (1)

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

BEGIN and END define a statement block, it is for control of flow. BEGIN TRANSACTION / COMMIT TRANSACTION / ROLLBACK TRANSACTION are for a database transaction. If a database is in a consistent state before a transaction is started then the database will be in a consistent state after the transaction completes. If there is a failure before commit transaction then all the changes made to the database will be rolled back to the point when the transaction began.

Upvotes: 1

Related Questions