Larry Johnson-473989 (10/15/2013)
I overheard our DBA today (2013-10-17) telling a junior developer to NEVER use a TRY-CATCH block in stored procedures. He told her that it is a very inefficient way of creating a transaction and should not be used. He said it was only still available for "backward compatibility." Instead, he suggested she put a BEGIN TRANSACTION and END TRANSACTION around anything that would do an insert or update.
Wow, that's ... interesting.
Try ... catch doesn't create a transaction at all (trivial to prove) and hence begin transaction cannot be considered a credible alternative to try..catch
Try .. catch is most certainly not deprecated (ie included only for backward compatibility) and it is in fact newer than begin transcation.
END TRANSACTION is not valid T-SQL.
BEGIN TRANSACTION
DELETE FROM a
END TRANSACTION
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'TRANSACTION'.
Try.. Catch is for error handling, much like in front end languages. It does not create, roll back or commit transactions.
BEGIN TRANSACTION... ROLLBACK/COMMIT TRANSACTION is for transaction management, for making a group of data modifications execute atomically. It does not do error handling.
In short, he might as well have said 'Trains are outdated, don't use them. I recommend using a microwave oven instead"
Perhaps of interest: http://sqlinthewild.co.za/index.php/2011/05/17/on-transactions-errors-and-rollbacks/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability