Exam 70-765 topic 3 question 81 discussion

Actual exam question from Microsoft's 70-765

Question #: 81
Topic #: 3

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?

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

Show Suggested Answer

Suggested Answer: E 🗳️
When importing data into a heap by using the INSERT INTO SELECT <columns> FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.
References:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

by Slava_bcd81 at Sept. 26, 2020, 4:14 p.m.

Comments

Submit Cancel

Slava_bcd81

4 years, 8 months ago

Difficult question, I think it may be not E as -"you can enable minimal logging and optimized locking for the statement by specifying the TABLOCK hint for the target table. In addition, the recovery model of the database must be set to simple or bulk-logged." (https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table?view=sql-server-ver15) So if we have SIMPLE or BULK recovery model when obviously we will get minimum logging from TABLOCK, but the question doesn't state we have SIMPLE or BULK recovery model -if we use TABLOCK its quite strange to say that its an optimized locking as the whole table is locked, so from one side we don't apply many locks during the transaction and can save time, from the other side we block all the other transactions that are using that table -if, for example, one order is totally in one row I would choose B (ROWLOCK) as the answer

upvoted 1 times

munro1900

4 years, 4 months ago

TABLOCK acquires a shared lock on the table, not an exclusive lock. Also from the page you referenced and it's just before your quote for TABLOCK: "When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable minimal logging [...]"

upvoted 1 times

...