You develop a Microsoft SQL Server 2012 database that contains a heap named
OrdersHistoncal.
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?
A.
HOLDLOCK
B.
ROWLOCK
C.
XLOCK
D.
UPDLOCK
E.
TABLOCK
Explanation:
Reference:
http://technet.microsoft.com/en-us/library/ms189857.aspx
Reference:
http://msdn.microsoft.com/en-us/library/ms187373.aspx
“E” is the correct…
When importing data into a heap by using the INSERT INTO SELECT FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.
https://technet.microsoft.com/en-us/library/ms187373(v=sql.105).aspx
HOLDLOCK: Hold object lock till transaction commits
ROWLOCK: Lock ROW while performing DML operation (insert, update, delete)
XLOCK: Hold specific transaction lock
UPDLOCK: Update existing lock being held on the object
TABLOCK: Lock TABLE while performing DML operation.