SQL Server BEGIN/END vs BEGIN TRANS/COMMIT/ROLLBACK

Question

I have been trying to find info on the web about the differences between these statements, and it seems to me they are identical but I can't find confirmation of that or any kind of comparison between the two.

What is the difference between doing this:

BEGIN     -- Some update, insert, set statements END 

and doing this

BEGIN TRANS     -- Some update, insert, set statements COMMIT TRANS 

?

Note that there is only the need to rollback in the case of some exception or timeout or other general failure, there would not be a conditional reason to rollback.

Answer 1

BEGIN and END deal with code blocks. They are similar to the curly braces you see in many languages:

if (somethingIsTrue) { // like BEGIN     // do something here } // like END 

In SQL, this is:

if somethingIsTrue BEGIN     -- do something here END 

BEGIN TRAN, COMMIT, and ROLLBACK begin and end transactions. They do not specify a new block of code; they only mark the transaction boundaries.

Note that you can write a BEGIN TRAN and COMMIT in separate blocks of code. For example, if you want code to be part of a transaction, but you don't want to start a new one if the code is already in a transaction, you can do something like this:

declare @TranStarted bit = 0 if @@trancount = 0 begin     set @TranStarted = 1     begin tran end  -- ... do work ...  if @TranStarted = 1 begin     commit     set @TranStarted = 0 end 

Answer 2

The regular BEGIN and END are not used for transactions. Instead, they are just for indicating that some block of code is a single unit, much like braces {} in C#/C++/Java.

If you have an IF statement or a WHILE loop that does 10 things, you need to enclose them in BEGIN/END so that SQL Server knows that that whole list of 10 statements should be executed as a part of that condition.