none
Transaction in SQL Server

    Question

  • Hello Guys,

    What is the standardized approach for using transaction in SQL Server?

    I have a stored procedure that has multiple steps and i want those to be enclosed within transaction.

    I googled the approaches and i found out the following types:-

    1. This is the first way we can use transactions:-

    CREATE PROCEDURE spNewStoredProcedure1

    @Parameter1 INT

    BEGIN

    BEGIN TRANSACTION

    /* Step 1 */

    IF @@ERROR <> 0
          BEGIN
              RETURN
          END

    /* Step 2 */

    IF @@ERROR <> 0

    BEGIN

    RETURN

    END

    COMMIT TRANSACTION

    END

    2. This is the second way we can use transactions:-

    CREATE PROCEDURE spNewStoredProcedure1

    @Parameter1 INT

    BEGIN

    BEGIN TRANSACTION

    BEGIN TRY

    /* Step 1 */

    /* Step 2 */

    COMMIT TRANSACTION

    END TRY
        BEGIN CATCH

            /* Rollback the transaction */
            ROLLBACK TRANSACTION

        END CATCH

    END

    Which is the better way of using the transactions?

    Is try and catch native to SQL Server or is it part of CLR integration.

    Please tell me why one is better than the other.
    Is there a performance benefit?
    Please elaborate you answer.

    Thanks in advance


    Wednesday, July 24, 2013 7:59 PM

Answers

  • Hi,

    TRY CATCH is native to SQL Server in the sense that its part of the standard TSQL Language. There is no performance benefit of one way over the other.

    Transactions will work with same performance with or without TRY CATCH.

    The TRY CATCH block offers you the additional functionality of catching an error and optionally doing something with it, before you roll back the transaction. If you dont plan to use this option of TRY CATCH, you can use approach # 1. It also depends on your conform level. I used to program with object oriented languages before using SQL, so i am comfortable using TRY CATCH and i always prefer to log the errors i have caught into a table, before I ROLLBACK.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Wednesday, July 24, 2013 9:35 PM

All replies

  • Hi,

    TRY CATCH is native to SQL Server in the sense that its part of the standard TSQL Language. There is no performance benefit of one way over the other.

    Transactions will work with same performance with or without TRY CATCH.

    The TRY CATCH block offers you the additional functionality of catching an error and optionally doing something with it, before you roll back the transaction. If you dont plan to use this option of TRY CATCH, you can use approach # 1. It also depends on your conform level. I used to program with object oriented languages before using SQL, so i am comfortable using TRY CATCH and i always prefer to log the errors i have caught into a table, before I ROLLBACK.


    Sanil Mhatre | Database Developer | MCTS | If you find my reply useful in any way, please vote it as helpful. If it has helped answer your question, please mark it as Answer. http://sqlwithsanil.com

    Wednesday, July 24, 2013 9:35 PM
  • In the first example, will the transactions be rolled back if you return or will you just have more and more "open" transactions?

    Looks like the example number two is the best. With COMMIT and ROLLBACK in place, always on of them to "clear up" the started transaction. If the assumption regarding number one is correct I also guess that this will some time give performance problems. I would stuck with example number two!

    TRY and CATCH is now supported fully in t-sql not only CLR (.net languages), it has been supported on the SQL server since SQL Server 2005.


    Best regards Arild Røksund, Norwegian developer (SQL & .net), Omega AS (please Vote as helpful if helpful)

    Wednesday, July 24, 2013 9:36 PM
  • If you are talking about Transaction which either commits whole or completely rollback,You should use BEGIN TRAN with ROLLBACK or COMMIT.But be sure if you are inserting millions or records with BEGIN TRAN and COMMIT it will take time more than if statement was not kept between begin tran and commit.So if you want complete ATOMICITY  use BEGIN TRAN. 

    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

    Thursday, July 25, 2013 4:45 AM
    Moderator
  • TRY...CATCH construct offers a structure approch to exception handling than traditional @@ERROR. I prefer the TRY...CATCH construct in most of the scenaios.

    You can read Erlands excellent article on exception handling to get a good grip on this:

    http://sommarskog.se/error_handling_2005.html (SQL Server 2005 and later)

    http://www.sommarskog.se/error-handling-I.html (SQL Server 2000)


    Krishnakumar S

    Thursday, July 25, 2013 5:03 AM