Microsoft SQL Server 2008 R2 Unleashed- P107:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1004 CHAPTER 31 Transaction Management and the Transaction Log begin a multistatement transaction that remains in effect until an explicit ROLLBACK or COMMIT statement is issued. Microsoft refers to this transation mode as IMPLICIT_ TRANSACTIONS. To enable implicit transactions for a connection in SQL Server 2008 you need to enable the IMPLICIT_TRANSACTIONS session setting using the following command SET IMPLICIT_TRANSACTIONS ON After this option is turned on transactions are implicitly started if they are not already in progress whenever any of the following commands are executed ALTER TABLE CREATE DELETE DROP FETCH GRANT INSERT OPEN REVOKE SELECT TRUNCATE TABLE UPDATE Note that neither the ALTER VIEW nor ALTER PROCEDURE statement starts an implicit transaction. You must explicitly complete implicit transactions by issuing a COMMIT or ROLLBACK a new transaction is started again on the execution of any of the preceding commands. If you plan to use implicit transactions the main issue to be aware of is that locks are held until you explicitly commit the transaction. This can cause problems with concurrency and the system s capability to truncate the transaction log. Even when using implicit transactions you can still issue the BEGIN TRAN statement and create transaction nesting. In the following example IMPLICIT_TRANSACTIONS ON is turned on to see the effect this has on the value of @@trancount. SQL Statements @@trancount Value SET IMPLICIT_TRANSACTIONS ON 0 go 0 INSERT INTO tablel 1 UPDATE table2 1 COMMIT 0 Download from Defining Transactions 1005 go SELECT FROM tablel 1 BEGIN TRAN 2 DELETE FROM tablel 2 COMMIT 1 go DROP TABLE tablel 1 COMMIT 0 31 As you can see in this example if a BEGIN TRAN is issued while a transaction is still active transaction nesting occurs and a second COMMIT is required to finish the transaction. The main difference between this example and the preceding one is that here a BEGIN TRAN is not required to start the .
