SQLServer try catch performance

Question

Has anyone found any performance boost/tradoff of using BEGIN TRY..END TRY in sql server 2008, vs the old IF @@ERROR <> 0? Just curious to know if there are performance penalties or not.

Answer 1

Since SQL 2005, you should try to use the TRY CATCH way to handle the exceptions or logging the errors. It is considered as best practice. There should have no major performance hit by using it.

BEGIN TRY
    BEGIN TRANSACTION
    -- SQL 
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK
    SELECT
        ERROR_MESSAGE(),
        ERROR_NUMBER() -- etc
END CATCH

Answer 2

Since the database disk hit issues are identical, there should be no appreciable performance issues.

Answer 3

Forget performance, it's a damn sight safer, better and more predicatable.

However, using @@ERROR usually requires a GOTO and/or lots of IF statements to manage it correctly so I'd guess there may be a tiny boost in TRY..CATCH.

Answer 4

THe performance of TRY ... CATCH is most likely a bit more when there are no errors. On the other hand, it will be faster in many cases where there is an error.

But, you should not code strictly for performance anyway. And, the overhead, if any, is going to be rather small, so I would not trade off the safer syntax for performance in this instance.

In addition, the Try ... Catch makes code a bit easier to maintain, especially if your SQL dev team has not been around SQL Server very long, which unfortunately, happens a bit too often. I imagine this will be more the case a few years from now.

Answer 5

• Speed Test: Try / Catch Block

• [Do try...catch blocks hurt runtime performance?]

  [2] hxxp://www.programmersheaven.com/user/pheaven/blog/175-Do-trycatch-blocks-hurt-runtime-performance/