July 6, 2010 at 12:37 am
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
July 6, 2010 at 12:58 am
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
July 6, 2010 at 4:07 am
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
July 6, 2010 at 4:31 am
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
July 6, 2010 at 6:39 am
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 Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 21, 2010 at 2:47 am
Thank you all, now I got it.. 🙂
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