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.
Let’s us understand this with a simple example. We are going to use the following product table in this demo.
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.
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.
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.
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
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.
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.