Difference between BEGIN | END and BEGIN TRANS | COMMIT TRANS

Question

Difference between BEGIN | END and BEGIN TRANS | COMMIT TRANS

HardCoder

Ten Centuries

Points: 1045
More actions
July 6, 2010 at 12:37 am

#234645

Hi all,
Can anyone explore me these two SQL statements -
First
BEGIN
-- 1. DO SOME UPDATE HERE
-- 2. DO SOME UPDATE HERE
END
Second
BEGIN TRANS
-- 1. DO SOME UPDATE HERE
-- 2. DO SOME UPDATE HERE
COMMIT TRANS
If both SQL statements run what will be scope of the transactions. I mean if there are any different between them according to the resource usage.. lock etc..
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105712 More actions · Answer 1

BEGIN / END delimits a block of code, without controlling a transaction. If not already inside a transaction, each statement will execute in an autonomous transaction. Typically BEGIN/END is used with branching/looping instructions (IF/WHILE).

BEGIN TRANSACTION / COMMIT TRANSACTION denotes the beginning of a transaction: each statement inside this block is executed in the same transaction and cannot be committed or rolled back individually.

Hope this helps

Gianluca

-- Gianluca Sartori

HardCoder Ten Centuries Points: 1045 More actions · Answer 2

Gianluca Sartori (7/6/2010)
BEGIN / END delimits a block of code, without controlling a transaction. If not already inside a transaction, each statement will execute in an autonomous transaction. Typically BEGIN/END is used with branching/looping instructions (IF/WHILE).
BEGIN TRANSACTION / COMMIT TRANSACTION denotes the beginning of a transaction: each statement inside this block is executed in the same transaction and cannot be committed or rolled back individually.
Hope this helps
Gianluca

Thanks for the answer, yes so i assume if i have only one update stament for both the cases then there is no difference.

I mean BEGIN|END bloack also commit the changes before leaving the END.

A little more elaboration will difinetely help me understand this..

What I am trying to figure out is that, why should not we use BEGIN|END Block when we have only one update statement ??

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com

spaghettidba SSC Guru Points: 105712 More actions · Answer 3

BEGIN/END is used for delimiting blocks in control of flow language. Example:

IF GETDATE() > '2010-06-01'

BEGIN

UPDATE someTable SET someColumn = 'someValue'

UPDATE anotherTable SET anotherColumn = 'anotherValue'

END

No transaction is issued. Each instruction runs atomically.

BEGIN TRANSACTION starts a transaction, COMMIT TRANSACTION commits changes.

BEGIN TRANSACTION

UPDATE someTable SET someColumn = 'someValue'

UPDATE anotherTable SET anotherColumn = 'anotherValue'

COMMIT

Both statements run inside the same transaction.

If you had just one update statement to run, on the transaction side nothing would have changed.

The main thing to remember is that BEGIN/END delimits blocks of code, not transactions.

Hope this is a bit clearer

-- Gianluca Sartori

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 4

HardCoder (7/6/2010)
Gianluca Sartori (7/6/2010)
BEGIN / END delimits a block of code, without controlling a transaction. If not already inside a transaction, each statement will execute in an autonomous transaction. Typically BEGIN/END is used with branching/looping instructions (IF/WHILE).
BEGIN TRANSACTION / COMMIT TRANSACTION denotes the beginning of a transaction: each statement inside this block is executed in the same transaction and cannot be committed or rolled back individually.
Hope this helps
Gianluca
Thanks for the answer, yes so i assume if i have only one update stament for both the cases then there is no difference.
I mean BEGIN|END bloack also commit the changes before leaving the END.
A little more elaboration will difinetely help me understand this..
What I am trying to figure out is that, why should not we use BEGIN|END Block when we have only one update statement ??

If you're only looking at an update statement, I'd use BEGIN TRAN/COMMIT/ROLLBACK TRAN with a TRY/CATCH block. That's the best approach. Gianluca has laid out all the reasons why.

----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software

HardCoder Ten Centuries Points: 1045 More actions · Answer 5

Thank you all, now I got it.. 🙂

Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com