Developing SQL Databases — Question 16

You have a relational data warehouse that contains 1 TB of data.
You have a stored procedure named usp_sp1 that generated an HTML fragment. The HTML fragment contains color and font style.
You need to return the HTML fragment.
What should you do?

Answer options

• A. Use the NOLOCK option.
• B. Execute the DBCC UPDATEUSAGE statement.
• C. Use the max worker threads option.
• D. Use a table-valued parameter.
• E. Set SET ALLOW_SNAPSHOT_ISOLATION to ON.
• F. Set SET XACT_ABORT to ON.
• G. Execute the ALTER TABLE T1 SET (LOCK_ESCALATION = AUTO); statement.
• H. Use the OUTPUT parameters.

Correct answer: G

Explanation

The correct answer is G because adjusting the lock escalation settings can help manage how locks are handled during the execution of the stored procedure, potentially allowing for better performance and access to the HTML fragment. The other options focus on different aspects of SQL Server behavior and do not directly relate to the need for returning the generated HTML fragment.