Provisioning SQL Databases — Question 56

You develop a Microsoft SQL Server 2012 database that contains a heap named OrdersHistorical.
You write the following Transact-SQL query:

INSERT INTO OrdersHistorical -

SELECT * FROM CompletedOrders -
You need to optimize transaction logging and locking for the statement.
Which table hint should you use?

Answer options

• A. HOLDLOCK
• B. ROWLOCK
• C. XLOCK
• D. UPDLOCK
• E. TABLOCK

Correct answer: E

Explanation

Using the TABLOCK hint allows the entire table to be locked during the insert operation, which minimizes logging and locking overhead. Other options like ROWLOCK or HOLDLOCK would introduce more locking granularity and potential contention, which is counterproductive for optimizing transaction logging in this scenario.