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 opened, 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