Implicit Transactions In SQL Seerver 2008

Primarily for compatibility with other major RDBMS systems, such as Oracle or DB2, SQL Server supports (it is off by default but can be turned on if you choose) the notion of what is called an implicit transaction. Implicit transactions do not require a BEGIN TRAN statement — instead, they are automatically started with your first statement. They then continue until you issue a COMMIT TRAN or ROLLBACK TRAN statement. The next transaction then begins with your next statement.

Theoretically, the purpose behind this is to make sure that every statement is part of a transaction.

SQL Server also wants every statement to be part of a transaction, but, by default, takes a different

Approach if there is no BEGIN TRAN, then SQL Server assumes you have a transaction of just one statement, and automatically begins and ends that transaction for you. With some other systems though, you’ll find the implied transaction approach. Those systems will assume that any one statement is only the beginning of the transaction and therefore require that you explicitly end the every transaction with a COMMIT or ROLLBACK.

By default, the IMPLICIT_TRANSACTIONS option is turned off (and the connection is in auto commit transaction mode). You can turn it on by issuing the command:

SET IMPLICIT_TRANSACTIONS ON;

After that, any of the following statements will initiate a transaction:

CREATE

ALTER TABLE

GRANT

REVOKE

SELECT

UPDATE

DELETE

INSERT

TRUNCATE TABLE

DROP

OPEN

FETCH

The transaction will continue until you COMMIT or ROLLBACK. Note that the implicit transactions option

will affect only the current connection — any other users will still have the option turned off unless they

have also executed the SET statement.

The implicit transactions option is dangerous territory, and I highly recommend that you leave this option off unless you have a very specific reason to turn it on(such as compatibility with code written in another system).

Here’s a common scenario: A user calls up and says, ‘‘I’ve been inserting data for the last half hour, and none of my changes are showing.’’ So, you go run a DBCC OPENTRAN, and discover that there’s a transaction that’s been there for a while — you can take a guess at what’s happened. The user has a transaction open, and his or her changes won’t appear until that transaction is committed. The user may have done it using an explicit BEGIN TRANS statement, but he or she may also have executed some

code that turned implicit transactions on and then didn’t turn it off. A mess follows.