What is Commit,Rollback and SavePoint in SQL Server

How to use Commit, Rollback and SavePoint commands in SQL Server
The Commit, Rollback and SavePoint are the Transactional Controlling Language commands in the SQL Server. They are used to mange the Transactions in the Database. In general, these commands are use to manage the changes made by the DML Commands like Insert update and Delete.

Commit : The Commit command is used to save any Transaction permanently into the database. Once you Commit any Transaction that cannot be Rollback.

The Transaction is nothing but any operation that we perform on a Table. The transaction which begin with keyword "Tran" or "Transaction" and ends with either Commit or Rollback.

Eg :
Begin Tran T1
Update Tbl_Emp Set Job_Title='Market Analyst' Where Emp_Id=123
Commit Tran T1

Rollback : Rollback command  is used to undo the changes made by any operation but only before a Commit is done. We can't Rollback data which has been Committed in the database with the Commit keyword.
Eg :
Suppose we have a Table "Tbl_Emp" with records as follows..
Emp_Id  Job_Title
    143     Engineer
    123     Market Analyst

Now lets make an update to Table and Rollback to Original state
Begin Tran T1
Update Tbl_Emp Set Job_Title="Market Research Analyst" Where Emp_Id=123
Rollback Tran T1

Ouput : 
Emp_Id  Job_Title
    143     Engineer
    123     Market Analyst

Savepoint : 
Savepoint command is used to temporarily save a transaction so that you can rollback to that point whenever necessary. You can you use multiple save points in a Transaction.

Syntax : Save Transaction SavePoint_Name

Example-I :
In the following Transaction, I am Inserting 3 records into a Table then Saving the Trasaction ( SavePoint 1) and then Updating those 3 records and again Saving the Transaction( SavePoint 2).
Next I am Truncating the Table and Rollback to Save Point then Deleting a Record then Committing the Transaction.

Begin Tran T1
Truncate Table [Emp_Test]
Select * From  [dbo].[Emp_Test]
  Insert Into [Emp_Test] Values ( 113,'Raju')
  Insert Into [Emp_Test] Values ( 114,'Ravi')
  Insert Into [Emp_Test] Values ( 115,'Rani')
 Save Tran SP1;
  Update [Emp_Test]  Set Ename='Sai' Where Eid=113
  Update [Emp_Test]  Set Ename='Shiva' Where Eid=114
  Update [Emp_Test]  Set Ename='Sloka' Where Eid=115
 Save Tran SP2;
  Truncate Table [Emp_Test]
 Rollback Tran SP1;
  Delete From [Emp_Test] Where EId =113
   Commit Tran T1 ;
Select * From  [dbo].[Emp_Test]
GO

Output : 
EId Ename
114 Ravi
115 Rani

Notes : 
Here if you observe, we inserted 3 records before SavePoint 1(Save Tran SP1).
But when you Rollback to that Savepoint , we got only 2 records, because we deleted a record after Rollback then Committed the Transaction. So, once you Committed a Transaction after any operation that cannot be rolled back.

Example-II :
Here we Rollback to SavePoint 2 ( Save Tran SP2)

Begin Tran T1
Truncate Table [Emp_Test]
Select * From  [dbo].[Emp_Test]
  Insert Into [Emp_Test] Values ( 113,'Raju')
  Insert Into [Emp_Test] Values ( 114,'Ravi')
  Insert Into [Emp_Test] Values ( 115,'Rani')
 Save Tran SP1;
  Update [Emp_Test]  Set Ename='Sai' Where Eid=113
  Update [Emp_Test]  Set Ename='Shiva' Where Eid=114
  Update [Emp_Test]  Set Ename='Sloka' Where Eid=115
 Save Tran SP2;
  Truncate Table [Emp_Test]
 Rollback Tran SP2;
  Delete From [Emp_Test] Where EId =113
   Commit Tran T1 ;
Select * From  [dbo].[Emp_Test]
GO

Output : 
EId Ename
114 Shiva
115 Sloka

Notes :

Here, if you observe, we updated 3 records before SavePoint 2 (Save Tran SP2).
But when you Rollback to that Savepoint , we got only 2 records, because we deleted a record after Rollback then Committed the Transaction. So, once you Committed a Transaction after any operation that cannot be rolled back.

Example-III :
Here if we try to Rollback to SavePoint 1 and then SavePoint 2 then we will receive an error saying "Cannot roll back SP2. No transaction or savepoint of that name was found" , because SavePoint 2 will be erased from the Memory when you Rollback to prior SavePoint of that (SavePoint 1).

Begin Tran T1
Truncate Table [Emp_Test]
Select * From  [dbo].[Emp_Test]
  Insert Into [Emp_Test] Values ( 113,'Raju')
  Insert Into [Emp_Test] Values ( 114,'Ravi')
  Insert Into [Emp_Test] Values ( 115,'Rani')
 Save Tran SP1;
  Update [Emp_Test]  Set Ename='Sai' Where Eid=113
  Update [Emp_Test]  Set Ename='Shiva' Where Eid=114
  Update [Emp_Test]  Set Ename='Sloka' Where Eid=115
 Save Tran SP2;
  Truncate Table [Emp_Test]
 Rollback Tran SP1;
  Delete From [Emp_Test] Where EId =113
Rollback Tran SP2;
   Commit Tran T1 ;
Select * From  [dbo].[Emp_Test]
GO

Output : 
EId Ename
114 Ravi
115 Rani

Msg 6401, Level 16, State 1, Line 26
Cannot roll back SP2. No transaction or savepoint of that name was found.

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------