SQL in a Nutshell

Name

SAVEPOINT

Synopsis

This command creates a savepoint in the current transaction. Transactions can be divided into logical breakpoints using the SAVEPOINT command. Multiple savepoints may be specified within a single transaction. The main benefit of the SAVEPOINT command is that transactions may be partially rolled back to a unique savepoint marker using the ROLLBACK command.

Vendor	Command
SQL Server	Supported, with variations
MySQL	Not supported
Oracle	Supported
PostgreSQL	Not supported

SQL99 Syntax and Description

SAVEPOINT savepoint_name

Some vendors allow duplicate savepoint names within a transaction, but this is not recommended. Substitute savepoint identifiers (in the format :X) also may be included to enable DBMS to track the savepoint with an integer rather than a name. Not all vendors support this approach, and it is not recommended as the best practice.

Note that SQL99 supports the statement RELEASE SAVEPOINT savepoint_name, enabling an existing savepoint to be eliminated. However, this statement is not supported by any of the vendors covered in this book.

Microsoft SQL Server Syntax and Variations

SAVE TRAN[SACTION] {savepoint_name | @savepoint_variable}

Microsoft SQL Server does not support the SAVEPOINT command. Instead, it uses the SAVE command. Rather than declaring the literal name of the savepoint, you can reference a variable containing the name of the savepoint.

When the ROLLBACK TRAN savepoint_name command is executed, SQL Server rolls the transaction ...