SQL Server Transaction Savepoints

1. Within a transaction you can create one or more transaction savepoints
2. When you rollback to a savepoint, all of the database updates performed after that savepoint are reversed
3. Updates that happened after the transaction started but before the savepoint was declared are not affected.
4. You can create multiple savepoints within a single transaction and roll them back individually
5. it's important to note that rolling back to a savepoint also removes any savepoints that were created later. for example, if you created savepoints named "s1", "s2" and "s3" in that order, rolling back savepoint "s2" would remove savepoint "s3". Savepoint "s1" would still be active.
6. Savepoint name should be a string of up to 32 characters. If the name is longer than 32 characters the additional text is ignored. 

SAVE TRAN savepoint-name

ROLLBACK TRAN savepoint-name

SET NOCOUNT ON

BEGIN TRAN

PRINT'First Transaction: '+ CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES('Tom')

SAVE TRAN Savepoint1

PRINT'Second Transaction: '+ CONVERT(VARCHAR,@@TRANCOUNT)

INSERT INTO People VALUES('Dick')

ROLLBACK TRAN Savepoint1

PRINT'Rollback: '+ CONVERT(VARCHAR,@@TRANCOUNT)

COMMIT TRAN

PRINT'Complete: '+ CONVERT(VARCHAR,@@TRANCOUNT)

/* MESSAGES

First Transaction: 1

Second Transaction: 1

Rollback: 1

Complete: 0

Note:

1. savepoints have the limitation that they cannot be used in distributed transactions. 
2. you should note that locks created during a transaction are retained when rolling back to a savepoint. They are released only when the entire transaction is committed or rolled back.