What is a savepoint in a transaction?
Quick Answer
A savepoint marks a point inside a transaction you can partially roll back to without abandoning the whole transaction: SAVE TRANSACTION MyPoint; then later ROLLBACK TRANSACTION MyPoint; undoes only the work done since the savepoint. The outer transaction stays open and can still commit.
A savepoint lets you undo part of a transaction. You call SAVE TRANSACTION with a name, and later ROLLBACK TRANSACTION name reverts only what happened after that point, while the overall transaction stays alive and can still commit. It's handy when one step in a longer operation is best-effort — you attempt it, and if it fails you roll back to the savepoint and carry on. The gotcha is that a named rollback doesn't change @@TRANCOUNT, and with XACT_ABORT ON a serious error still takes down the whole transaction regardless.
- Useful when part of a batch is optional — try an operation, and if it fails, roll back just that part and continue.
ROLLBACK TRANSACTION MyPointdoes not reduce@@TRANCOUNT; a fullROLLBACK(no name) always goes to the start and sets it to 0.- Savepoints don't survive a distributed transaction and interact awkwardly with
XACT_ABORT ON(a severe error still kills the whole transaction). - SQL Server's “nested transactions” are mostly an illusion — only the outermost
COMMITcommits; savepoints are the real tool for partial rollback.