Savepoints and nested-transaction behavior

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Undo part of a transaction without assuming nested commits are independent. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Changes (ID int NOT NULL);
BEGIN TRANSACTION;
INSERT INTO #Changes VALUES (1);
SAVE TRANSACTION AfterFirst;
INSERT INTO #Changes VALUES (2);
ROLLBACK TRANSACTION AfterFirst;
SELECT ID FROM #Changes ORDER BY ID;
COMMIT TRANSACTION;
DROP TABLE #Changes;

Read it step by step

1. The first insert occurs before savepoint AfterFirst.
2. Rollback to that savepoint undoes only the second insert.
3. COMMIT finishes the outer transaction; a nested COMMIT alone would only reduce @@TRANCOUNT, not make the work independently durable.

Check the result

The SELECT runs before the final COMMIT and shows only ID 1.

Test an inner COMMIT

CREATE TABLE #Nested (ID int NOT NULL);
BEGIN TRANSACTION;
INSERT INTO #Nested VALUES (1);
BEGIN TRANSACTION;
INSERT INTO #Nested VALUES (2);
COMMIT TRANSACTION;
DECLARE @CountAfterInnerCommit int = @@TRANCOUNT;
DECLARE @RowsAfterInnerCommit int = (SELECT COUNT(*) FROM #Nested);
ROLLBACK TRANSACTION;
SELECT @CountAfterInnerCommit AS CountAfterInnerCommit,
       @RowsAfterInnerCommit AS RowsAfterInnerCommit,
       @@TRANCOUNT AS CountAfterOuterRollback,
       COUNT(*) AS RowsAfterOuterRollback
FROM #Nested;
DROP TABLE #Nested;

The inner COMMIT reduces @@TRANCOUNT from 2 to 1, so both rows are still part of the outer transaction. The full ROLLBACK then removes both rows and returns @@TRANCOUNT to 0. The temp table was created before the transaction, so it remains available for the final count.

Practice

Insert IDs 3 and 4 into a temp table, put a savepoint after 3, roll back to it, then show survivors.

CREATE TABLE #Changes (ID int NOT NULL);
BEGIN TRANSACTION;
INSERT INTO #Changes VALUES (3);
SAVE TRANSACTION AfterThree;
INSERT INTO #Changes VALUES (4);
ROLLBACK TRANSACTION AfterThree;
SELECT ID FROM #Changes ORDER BY ID;
COMMIT TRANSACTION;
DROP TABLE #Changes;