You are a database developer on an instance of SQL Server 2008. You are developing a Transact-SQL script that will be used by developers. The script uses multiple DML statements, but does not use transactions. You want to ensure that each DML statement is treated as a separate transaction, but not automatically committed when executed. You must also ensure that all DML statements are explicitly committed or all are rolled back.
You want to accomplish this with minimum effort. What should you do?
A. Add the SET IMPLICIT_TRANSACTIONS ON statement.
B. Add the SET IMPLICIT_TRANSACTIONS OFF statement.
C. Set the transaction isolation level to READ COMMITTED.
D. Include a custom error-handler for each DML statement.
Correct Answer: A
Explanation/Reference:
Transactions can be specified explicitly, implicitly, or automatically. In autocommit mode, which is the default, each Transact-SQL statement is treated as a separate transaction, and each statement is automatically committed when it successfully executes. In this scenario if you set the IMPLICIT_TRANSACTIONS option to ON, transactions will be implicitly created.
The IMPLICIT_TRANSACTIONS option controls how transactions are handled. When ON, SET IMPLICIT_TRANSACTIONS sets the connection into implicit transaction mode. When OFF, it returns the connection to autocommit transaction mode.
When a connection is in implicit transaction mode and the connection is not currently in a transaction, executing any of the following statements starts a transaction:
• ALTER TABLE
• FETCH
• REVOKE
• BEGIN TRANSACTION
• GRANT
• SELECT
• CREATE
• INSERT
• TRUNCATE TABLE
• DELETE
• OPEN
• UPDATE
• DROP
If the connection is already in an open transaction, the statements do not start a new transaction (except BEGIN TRANSACTION statement).
Transactions that are automatically opened as the result of this setting being ON must be explicitly committed or rolled back by the user at the end of the transaction. Otherwise, the transaction and all of the data changes it contains are rolled back when the user disconnects. After a transaction is committed, executing one of the statements above starts a new transaction.
You should not add the SET IMPLICIT_TRANSACTIONS OFF statement. This is the default setting, and all Transact-SQL would use autocommit mode. Each DML statement would be considered a separate transaction and automatically committed if it executed successfully.
You should not set the transaction isolation level to READ COMMITTED. The transaction isolation level controls how a transaction behaves when there are other concurrent transactions. However, in this scenario, transactions are not used. Therefore, this would not be applicable.
You should not include a custom error-handler for each DML statement because this would require more effort than necessary. You can check the value returned by the @@ERROR function for each DML statement and include blocks of code to perform the desired actions. However, in this scenario, you want to have minimal development effort.
