Developing SQL Databases — Question 52

You are designing a stored procedure for a database named DB1.
The following requirements must be met during the entire execution of the stored procedure:
✑ The stored procedure must only read changes that are persisted to the database. statements within the stored procedure should only show changes to the data that are made by the stored procedure.
✑ SELECT
You need to configure the transaction isolation level for the stored procedure.
Which Transact-SQL statement or statements should you run?

Answer options

• A. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT ON
• B. SET TRANSACTION ISOLATION LEVEL READ COMMITED ALTER DATABASE DB1 SET READ_COMMITED_SNAPSHOT OFF
• C. SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• D. SET TRANSACTION ISOLATION LEVEL READ UNCOMMITED ALTER DATABASE SET READ_COMMITED_SNAPSHOT OFF

Correct answer: B

Explanation

The correct answer is B because it sets the transaction isolation level to READ COMMITTED, ensuring that the stored procedure only reads committed changes. Option A is incorrect as it uses READ UNCOMMITTED and enables snapshot isolation, which does not meet the requirement. Option C sets the isolation level to SERIALIZABLE, which is stricter than necessary. Option D also uses READ UNCOMMITTED, failing to fulfill the requirement of reading only committed changes.