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 the database disk hit issues are identical, there should be no appreciable performance issues.

Answer 2

This is an old question, but in 2012 Aaron Bertrand wrote a detailed article Performance impact of different error handling techniques where he compared few approaches for dealing with exceptions in SQL Server and I thought it is worth mentioning it here.

He says, that the primary approaches people use to deal with exceptions are:

• Just let the engine handle it, and bubble any exception back to the caller.
• Use BEGIN TRANSACTION and ROLLBACK if @@ERROR <> 0.
• Use TRY/CATCH with ROLLBACK in the CATCH block (SQL Server 2005+).

And many take the approach that they should check if they're going to incur the violation first, since it seems cleaner to handle the duplicate yourself than to force the engine to do it.

His conclusions are:

If we think we are going to have a high rate of failure, or have no idea what our potential failure rate will be, then checking first to avoid violations in the engine is going to be tremendously worth our while. Even in the case where we have a successful insert every time, the cost of checking first is marginal and easily justified by the potential cost of handling errors later (unless your anticipated failure rate is exactly 0%).

This is the chart from the article:

CheckInsert    |  Checks `IF EXISTS` first  |  Simple `INSERT`
CheckRollback  |  Checks `IF EXISTS` first  |  Use `IF @@ERROR <> 0`
CheckTryCatch  |  Checks `IF EXISTS` first  |  Use `TRY CATCH`
JustInsert     |                            |  Simple `INSERT`
JustRollback   |                            |  Use `IF @@ERROR <> 0`
JustTryCatch   |                            |  Use `TRY CATCH`

Answer 3

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 4

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.