Transactions in Oracle and SQL Server

In Oracle, a transaction is started automatically whenever an INSERT, UPDATE, or DELETE operation is performed. An application must issue a COMMIT to save all changes to data. If a COMMIT is not performed, all changes are rolled back or undone automatically.

You can start transactions in Microsoft® SQL Server™ as explicit, autocommit, or implicit transactions.

Explicit transactions

Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.

Autocommit transactions

This is the default mode for SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.

Implicit transactions

As in Oracle, an implicit transaction is started whenever an INSERT, UPDATE, DELETE, or other data manipulating function is performed. To allow implicit transactions, use the SET IMPLICIT_TRANSACTIONS ON statement.

The SQL Server SAVE TRANSACTION statement functions in the same way as the Oracle SAVEPOINT command. It sets a savepoint in the transaction, allowing partial rollbacks.

If you are issuing a script through SQL Server Query Analyzer or other query tools, you can either include the explicit BEGIN TRANSACTION statement shown previously, or start the script with the SET IMPLICIT_TRANSACTIONS ON statement. The BEGIN TRANSACTION approach is more flexible, while the implicit approach is more compatible with Oracle.

See Also