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.

The **first** place to read up on SQL Server syntax is Books Online, the documentation that comes with SQL Server. — Philip Kelley, Apr 28 '10 at 14:39
you have not marked an accepted answer, but I sure hope you understand the difference, they are quite different. Without understanding this basic point you are missing one of the most fundamental points of SQL. — KM., May 18 '10 at 18:45

Paul Williams · Accepted Answer · 2016-02-10T19:10:32.473

36

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

edited Feb 10 '16 at 19:10

answered Apr 28 '10 at 14:32

Paul Williams

16,585
5
47
82

1

I think saying they are "similar to the 'if' construct" may be a bit misleading. I like @Mike Mooney's simile "like braces {} in C#/C++/Java" – John MacIntyre Apr 28 '10 at 14:51
so if an issue happens within begin end statement, nothing will be rolled back but if it happens within begin transaction..then the transaction will be rolled back. right? – Baahubali Jun 11 '19 at 06:35
@Baahubali The answer depends on the setting of [XACT_ABORT](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-xact-abort-transact-sql?view=sql-server-2017). If `XACT_ABORT` is on, then yes, the transaction will be rolled back. If not, then no, the transaction is likely to be left open-- assuming some kind of severe error does not leave it in an indeterminate state. Check [XACT_STATE](https://learn.microsoft.com/en-us/sql/t-sql/functions/xact-state-transact-sql?view=sql-server-2017) to see if the transaction is usable. But I recommend turning on `XACT_ABORT`. – Paul Williams Jun 11 '19 at 22:30
But my understanding is that begin end does not start a transaction? In my scenario, xact_abort is off, if within begin end there are a batch of insert statements and one of them fails, I guess others will continue ? – Baahubali Jun 12 '19 at 00:02
@Baahubali if `xact_abort` is off, then the execution will continue if possible after an error. (This is a simplification.) `begin` and `end` are not related to transactions. `begin tran`, `commit`, and `rollback` mark the start and end of transactions. – Paul Williams Jun 12 '19 at 20:29

score 5 · Answer 2 · answered Apr 28 '10 at 14:33

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.

score 4 · Answer 3 · answered Apr 28 '10 at 14:31

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.

Sithlord · Answer 4 · 2010-12-19T15:17:10.653

It should be mentioned, that there is a Begin; in PostgreSQL, that also initiates a transaction block, which at first confused me.

http://www.postgresql.org/docs/9.0/static/sql-begin.html

"BEGIN initiates a transaction block, that is, all statements after a BEGIN command will be executed in a single transaction until an explicit COMMIT or ROLLBACK is given. By default (without BEGIN), PostgreSQL executes transactions in "autocommit" mode, that is, each statement is executed in its own transaction and a commit is implicitly performed at the end of the statement (if execution was successful, otherwise a rollback is done)."

score 1 · Answer 5 · answered Jun 28 '13 at 11:04

1

I have not seen END TRANS :)

i think we use END only for BEGIN keyword not for BEGIN trans we use commit or rollback for BEGIN trans

answered Jun 28 '13 at 11:04

Md. Parvez Alam

4,326
5
48
108

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

5 Answers5

Linked