Commit and Rollback Commands in SQL Server

In this article I describe Commit and Rollback Commands in SQL Server.

Introduction

 
In this article, I will describe Commit and Rollback commands in SQL Server. Rollback and Commit are transaction statements that are called Data Control Language for SQL and are used to ensure the integrity of data in databases. In my previous article, I describe Grant and Revoke DCL commands; for that visit, Grant and Revoke Command in SQL SERVER.
 
First of all we create a table named emp on which we enforce the Rollback and Commit commands.
 
Creation of table
 
Use the following command to the create table:
  1. create table emp(empid int constraint PRIMARYKEY primary key, empName varchar(15))  
Insertion of data
 
Use the following command for the insertion of data.
  1. insert into emp  
  2. select 11,'d'union all  
  3. select 12,'ee'union all  
  4. select 13,'p'union all  
  5. select 14,'a'union all  
  6. select 15,'k'  
Output
 
Use the following command to see the output.
  1. select * from emp
commit-and-rollback-in-sql-server.jpg
 

Commit in SQL Server

 
Commit is used for permanent changes. When we use Commit in any query then the change made by that query will be permanent and visible. We can't Rollback after the Commit.
 
Syntax
 
begin tran tranName
Command for operation
commit tran tranName
 
Here tranName is the name of the transaction and the command for operation is the SQL statement that is used for the operation like making a change or inserting data etc.
 
Example
  1. begin tran d  
  2. update emp set empName ='D' where empid=11  
  3. commit tran d  
Here d is the name of the transactions and we update empName d to D in the table emp on the basis of empId. The change made by this command will be permanent and we could not Rollback after the commit command.
 
Output
 
Commit in SQL Server
 
Commit in SQL Server
 

Rollback in SQL Server

 
Rollback is used to undo the changes made by any command but only before a commit is done. We can't Rollback data which has been committed in the database with the help of the commit keyword.
 
Syntax
 
begin tran tranName
Command for operation
Rollback tran tranName
 
Here tranName is the name of the transaction and the command for the operation is the SQL statement that is used for performing operations like to make any change or insert data etc.
 
Example
 
We want that, if data entered by user has an empId less than 10 then the command is rolled back and a message is shown to the user "An id less than 10 is not valid; query is rolled back".
  1. begin tran t  
  2. declare @id int;  
  3. set @id=1;  
  4. insert into emp values(@id,'d')  
  5. if(@id<10)  
  6. begin  
  7. print'An id less than 10 is not valid; query is rolled back';  
  8. rollback tran t;  
  9. end   
  10. else  
  11. begin  
  12. print 'data is inserted'  
  13. end  
Here d is the name of transactions. When we provide empId less than 10 then we get.
 
Output
 
Rollback in SQL Server 
 
Rollback in SQL Server 
 
When we provide empId 16 which is greater than 10 then:
  1. begin tran t  
  2. declare @id int;  
  3. set @id=16;  
  4. insert into emp values(@id,'d')  
  5. if(@id<10)  
  6. begin  
  7. print'Less than 10 id is not valid,query is rollbacked';  
  8. rollback tran t;  
  9. end  
  10. else  
  11. begin  
  12. print 'data is inserted'  
  13. end  
Output
 
commit-and-rollback-in-sql-server-2012-.jpg
 
commit-and-rollback-in-sql-server-2012--.jpg
 

Summary

 
In this article, I described Commit and Rollback Commands in SQL Server. I hope this article has helped you to understand this topic. Please share if you know more about this. Your feedback and constructive contributions are welcome.