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:
http://technet.microsoft.com/en-us/library/ms189857.aspx
http://msdn.microsoft.com/en-us/library/ms187373.aspx
?
0
0
‘a’ – holdlock is wrong because it lock whole table until operation is done {transaction is commited};
‘b’ – rowlock – you use it to update/delete some rows {not many}, for only insert & select its useless and it has bad performance;
‘c’ – xlock – as far as I know its exclusive lock – also locks everyting;
‘d’ – updlock – you use it to insert new, update or delete statements;
‘e’ – correct {for me 99%}. You should use ‘tablock’ hint to improve performance while insert/select statements. It is share lock.
0
0
Thanks, good explanation.
0
0
E
0
0
E is the good one.
Take a look to this link.
https://drillchina.wordpress.com/2012/12/18/use-tablock-to-boost-your-insert-into-select-performance/
0
0