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

These 2 statements are entirely different.

BEGIN..END mark a block of code, eg in an if statement

IF @something = 1
BEGIN
  -- Do something when @something is equal to 1
END

BEGIN TRANS..COMMIT TRANS wrap the enclosing block in a transaction, and depending on server settings will rollback the transaction if an error occurs.

Answer 2

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 TRANS and END TRANS begin and end a transaction. They DO NOT specify a new block of code; they only mark the transaction boundaries.

Answer 3

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.