HiveBrain v1.2.0
Get Started
← Back to all entries
debugsqlMajor

Is this an error in SQL Server's documentation for ROLLBACK?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
thiserrorrollbacksqldocumentationforserver

Problem

This is the SQL Server's document talking about the ROLLBACK statement. On that page it states that the syntax of it is as follows:

ROLLBACK { TRAN | TRANSACTION }   
     [ transaction_name | @tran_name_variable  
     | savepoint_name | @savepoint_variable ]   
[ ; ]


Apparently the stuff inside square brackets are optional and it seems you should choose one from TRAN or TRASACTION. But in fact you can completely omit both and ROLLBACK is a perfectly valid statement. Is this an error or intentionally?

Solution

The ROLLBACK TRANSACTION documentation is correct, albeit not clear without additional context.

ROLLBACK is shorthand for the similar ROLLBACK WORK statement rather than ROLLBACK TRANSACTION. Below is an excerpt from the ROLLBACK WORK doc page:

ROLLBACK [ WORK ] [ ; ]

This statement functions identically to ROLLBACK TRANSACTION except
that ROLLBACK TRANSACTION accepts a user-defined transaction name.
With or without specifying the optional WORK keyword, this ROLLBACK
syntax is ISO-compatible.

When nesting transactions, ROLLBACK WORK always rolls back to the
outermost BEGIN TRANSACTION statement and decrements the @@TRANCOUNT
system function to 0.

Note the same applies to the similar COMMIT TRANSACTION and COMMIT WORK statements.

Context

StackExchange Database Administrators Q#289619, answer score: 32

Revisions (0)

No revisions yet.