I Assume that you have a basic idea of Transactions. If not go through What are Transactions in SQL Server first. So we can do following three statements while working with Transactions in SQL.
- Begin Transaction
- Rollback Transaction
- Commit Transaction
With the help of Rollback command we can rollback the changes. Means if anything goes wrong with any of the T-SQL statement in the transaction, all changes are aborted/ reversed. What if we want to rollback till a specific point in the transaction not the entire transaction. So SAVEPOINT is a command which helps us doing this. Let’s see how.
SAVEPOINT
It is a command which creates a point in a transaction to which you can rollback your transaction. You can have any no. of savepoints in your transaction. Try creating it.
Syntax : SAVE TRAN savepoint-name
NOTE: you can write Tran or Transaction - both are same.
Now lets take an example.
1. Create a table tbl_demo with 2 fields
(
id int,
name varchar(10)
)
2. Inserting 1 record in table
Select * from tbl_demo
3. Now Create a transaction with 3 savepoints and lets try to rollback not the entire transaction but to a particular savepoint.
Insert into tbl_demo values(2, ‘Sudha’);
SAVE TRANSACTION A;
Insert into tbl_demo values(3, ‘Arvind’);
SAVE TRANSACTION B;
Insert into tbl_demo values(4, ‘Neo’);
Execute above transaction, and check the Table- tbl_demo
4. Now execute the command Rollback to rollback the transaction. By executing it whole transaction will reversed and your table will left with only 1 record.
5. Now execute above transaction once again, and let’s try to use Savepoint command to rollback to a particular point in transaction. We have three savepoints so we can roll back to any one of them, let us rollback to Savepoint B, means till ID 3.
Execute above Query and then see Table tbl_demo by using select statement
You can see that records till Savepoint is remained and after Savepoint B the records are reversed. So ID 4 is undone.
Now You can commit your transaction.
Release a Savepoint
Tip:
There is no need to Release a savepoint in SQL Server. They are automatically get released when the transaction get committed or rolled back.
Hope this article helps you in clearing your concept regarding savepoints in Transactions