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
    • 489
    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

    BEGIN TRANSACTION
    

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

    --Begin a new transaction

    BEGIN TRAN
    

    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

    BEGIN TRAN
    --Changes inside the transaction
    COMMIT
    

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

    --Various ways to commit a transaction

    COMMIT
    COMMIT TRAN
    COMMIT 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
    ROLLBACK TRAN
    ROLLBACK 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
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
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: