Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • SQL Server Transactions

    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 0
    • 470
    Comment on it

    A Transaction groups a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully finish.If all of these tasks execute successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of the T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.This blog illustrates how to use Transactions in Microsoft SQL Server.

    How to begin a transaction?

    You can begin a transaction with the BEGIN TRANSACTION statement.The BEGIN TRANSACTION statement notifies SQL Server to treat all of the following actions as a single transaction.

    --Begin a new transaction


    You can also abbreviate the word TRANSACTION to TRAN as below:

    --Begin a new transaction


    Is it possible to name a transaction ?

    Yes it's possible to name a transaction , a usefull feature in case we have opened multiple transactions within the same script

    --Begin a new transaction with a name

    BEGIN TRAN NamedTransaction

    You can also use a variable to set the name of a transaction, as below :

    DECLARE @TransactionName VARCHAR(50)
    SET @TransactionName = 'NamedTransaction'
    BEGIN TRAN @TransactionName 

    How to end a transaction?

    When you've started a transaction you must always explicitly end it either by using COMMIT or ROLLBACK

    To commit a transaction you use the COMMIT statement, as shown below:

    --Start a new transaction

    --Changes inside the transaction

    There are multiple wats to commit a transaction as shown below:

    --Various ways to commit a transaction

    COMMIT TRAN NamedTransaction

    If you want to revert the changes you can use the ROLLBACK statement.ROLLBACK statement rolls back an explicit or implicit transaction to the beginning of the transaction.

    --Various ways to rollback a transaction

    ROLLBACK TRAN NamedTransaction

    Can we nest transactions ?

    Yes we can nest transactions as below:

    BEGIN TRAN OuterTran
        BEGIN TRAN InnerTran
          -----Inner transaction statemnets
        COMMIT TRAN InnerTran
    COMMIT TRAN OuterTran

    I hope after reading this article you will be able to work with transactions in SQL Server.

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: