O'Reilly logo

SQL in a Nutshell by Kevin Kline

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required