I'm fairly new in using MicroSoft SQLServer so I need to clarify certain points regarding transactions. Oracle has an exception in transaction handling and this is given in their training modules: if any DDL command is part of an explicit transaction (NOT in autocommit mode), all the active (uncommitted) statements from the beginning of the transaction or from the start of the last savepoint until the statement just before the current DDL command are automatically COMMITED upon the execution of such DDL command. Does this also happen in SQLServer? Are there other exceptions which do automatic commit in explicit transactions? I understand that transactions bear the ACID properties so I wish to know if SQLServer does have similar exceptions as Oracle. Such exception affects our system design and programming. Thanks a lot for your help and I do hope I hear from you as soon as possible!!

-- Anonymous, January 21, 2000

Answers

Chelo,

In Microsoft SQL Server, by default, each statement is a transaction unto itself (that is, each statement is an autocommit transaction).

If you designate an explicit transaction by means of the BEGIN TRANSACTION statement, you may either complete the whole transaction by means of the COMMIT TRANSACTION statement, or cancel the whole transaction by using the ROLLBACK TRANSACTION statement. These explicit transaction may be nested.

You can read more in the SQL Server Books Online article, "Controlling Transactions".

Hope this helps,

Eric

-- Anonymous, January 21, 2000