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.