SAVEPOINT creates a rollback checkpoint within a transaction and allows partial rollback, whereas a Nested Transaction only increases @@TRANCOUNT and does not provide independent commit or rollback capability. A rollback in a nested transaction rolls back the entire transaction, while a rollback to a SAVEPOINT affects only the work performed after that checkpoint.
|
Feature |
SAVEPOINT (SAVE
TRANSACTION) |
Nested Transaction |
|
Purpose |
Create a rollback
checkpoint |
Create logical
transaction levels |
|
Syntax |
SAVE
TRANSACTION SP1 |
BEGIN TRAN
inside another BEGIN TRAN |
|
Creates New
Transaction? |
❌ No |
❌ No (logical only) |
|
Increases @@TRANCOUNT? |
❌
No |
✅
Yes |
|
Supports Partial
Rollback? |
✅ Yes |
❌ No |
|
Can Rollback Specific Work? |
✅
Yes |
❌
No |
|
Can Rollback Inner
Transaction Only? |
❌ No |
❌ No |
|
Transaction Log Entry |
Savepoint
marker |
New BEGIN TRAN
record count |
|
Commit Inner Unit
Separately? |
❌ No |
❌ No |
|
Releases Locks? |
❌
No |
❌
No |
|
Ends Transaction? |
❌ No |
❌ No |
|
Commonly Used In Production? |
✅
Very Common |
⚠️
Less Common |
|
Main Use Case |
Error recovery |
Modular code structure |
|
Survives Until Commit? |
✅
Yes |
✅
Yes |
|
Works With TRY/CATCH? |
✅ Excellent |
⚠️ Limited benefit |
