Transaction in Sql Server

In this article, I am sharing the concept of transaction in SQL Server and how it is implemented.
Transactions run as a group of commands or queries as single execution unit. It ensures either all the commands or queries will be executed or none of them.

Once transaction is in open state, it needs to be either committed or rolled back using following commands

• Begin Tran: To start a transaction.
• Commit Tran: To commit all the executed statements.
• Rollback Tran: To rollback all the executed statements.

Let's create a table and see how transaction works with some examples

create table TableA( empid varchar(10), empname varchar(10))

Let's insert some rows in the table within the transaction and rollback the transaction

begin tran
    insert into TableA
    select 'EMP001', 'Sandeep' union all
    select 'EMP002', 'Abhay'   
rollback
select * from TableA

OUTPUT

Look at the output, we inserted 2 rows but there is no row in the table as we have rolled back the transaction.

Now lets run the same statements with commit.

begin tran
    insert into TableA
    select 'EMP001', 'Sandeep' union all
    select 'EMP002', 'Abhay'   
commit
select * from TableA

OUTPUT

Look at the output now, we run the same statements with commit and this time the rows are inserted

In the next article, we will see how how nested transaction works.