SQL Server SavePoints Transaction

SQL Server SavePoints Transaction

In this article, I will discuss how to use SQL Server SavePoints Transaction to do partial rollback with examples. The SAVE TRANSACTION in SQL Server is used for dividing (or) breaking a transaction into multiple units. So the user has a chance of rollbacking the transaction up to a specified point.

In other words, the SQL Server SavePoints Transaction allows us to roll back a part of the transaction instead of the entire transaction. So, if we are rolling back the transactions up to a specific savepoint, only the statements after the savepoint and before the rollback command will be rolled back.

SQL Server SavePoints Transaction

Let’s us understand this with a simple example. We are going to use the following product table in this demo.

SQL Server SavePoints Transaction

Please use the below SQL Scripts to create and populate the Product table with test data.

-- Create the Product Table
CREATE TABLE Product
(
  ProductID INT PRIMARY KEY, 
  Name VARCHAR(40), 
  Price INT,
  Quantity INT
 )
 GO

 -- Populate the Product Table with some test data
 INSERT INTO Product VALUES(101, 'Laptop', 15000, 100)
 INSERT INTO Product VALUES(102, 'Desktop', 20000, 150)
 INSERT INTO Product VALUES(103, 'Mobile', 3000, 200)
 INSERT INTO Product VALUES(104, 'Tablet', 4000, 250)
 GO

Example: In the below example we create three save point as SavePoint1, SavePoint2, and SavePoint3.

BEGIN TRANSACTION
SAVE TRANSACTION SavePoint1
DELETE FROM Product WHERE ProductID = 101
SAVE TRANSACTION SavePoint2
DELETE FROM Product WHERE ProductID = 102
SAVE TRANSACTION SavePoint3
DELETE FROM Product WHERE ProductID = 103

NOTE: As per the definition it will rollback the statements which are present after the savepoint and before the Rollback Statement.

CASE1:

ROLLBACK TRANSACTION SavePoint1

When we execute the above statement, it will rollback the statement starting from SavePoint1 and ends with SavePoint3 statement so that the user has a chance to rollback 3 records.

CASE2:

ROLLBACK TRANSACTION SavePoint2

When we execute the above Rollback transaction statement, the transaction will start from the SavePoint2 statement and ends with the SavePoint3 statement, so that user has a chance to rollback 2 records.

CASE3:

ROLLBACK TRANSACTION SavePoint3

When we execute the above Rollback Transaction statement, it will rollback the statement that starts and ends with SavePoint3 so that the user has a chance to rollback 1 record.

NOTE: The savepoint name in SQL Server can have a maximum of 32 characters. If a savepoint variable is used instead of a savepoint name and a string with more than 32 characters is assigned to it, only the first 32 characters will define the savepoint name and the remaining characters will be ignored.

Using SQL Server transaction savepoints with the same savepoint name

It is also possible in SQL server to have multiple savepoints with the same name, but in the case of a rollback to the savepoint, the transaction will be rolled back to the latest SAVE TRANSACTION using that name. 

Let us see an example for better understanding

-- First Truncate the product table
TRUNCATE TABLE Product

BEGIN TRANSACTION
SAVE TRANSACTION InsertSavePoint
INSERT INTO Product VALUES(105, 'Product-5', 15000, 100)
SAVE TRANSACTION InsertSavePoint
INSERT INTO Product VALUES(106, 'Product-6', 20000, 150)
SAVE TRANSACTION InsertSavePoint3
INSERT INTO Product VALUES(107, 'Product-7', 20000, 150)

Here we created three save points but two save points with the same name

ROLLBACK TRANSACTION InsertSavePoint

When we execute the above Rollback Transaction statement, it will rollback the statement starting from the second InsertSavePoint and hence SELECT * FROM Product will give us the below result

SQL Server SavePoints Transaction

So, in short, we can say that SAVEPOINT command in SQL Server is used to temporarily save a transaction. As a result, we can rollback to that point whenever required.

In the next article, I will discuss nested transaction in SQL Server with examples.

SUMMARY

In this article, I try to explain the SQL Server SavePoints Transaction step by step with some examples. I hope this article will help you with your need. I would like to have your feedback. Please post your feedback, question, or comments about this article.

Leave a Reply

Your email address will not be published. Required fields are marked *