Exam 70-461 topic 1 question 244 discussion

Actual exam question from Microsoft's 70-461

Question #: 244
Topic #: 1

You develop a Microsoft SQL Server database that contains two tables named OrdersHistorical and CompletedOrders.
Both tables have the same schema that has the following definition:

You write the following Transact-SQL statement:

You need to ensure that data that falls into the date range of the SELECT statement cannot be inserted into CompletedOrders while the INSERT statement is running. You also need to ensure that other operations are unaffected.
Which table hint should you use?

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

Show Suggested Answer

Suggested Answer: E 🗳️
UPDLOCK s -
pecifies that update locks are to be taken and held until the transaction completes. UPDLOCK takes update locks for read operations only at the row-level or page-level.
Incorrect Answres:
Not A: ROWLOCK specifies that row locks are taken when page or table locks are ordinarily taken.
B: HOLDLOCK is equivalent to SERIALIZABLE. HOLDLOCK applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement that it is used in.
Note: SERIALIZABLE is equivalent to HOLDLOCK. Makes shared locks more restrictive by holding them until a transaction is completed, instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether the transaction has been completed or not
D: TABLOCK specifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level.
When importing data into a heap by using the INSERT INTO <target_table> SELECT <columns> FROM <source_table> statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table
Reference:
https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

by designsfactory at April 4, 2025, 11:19 a.m.

Comments

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!

Exam 70-461 All Questions

View all questions & answers for the 70-461 exam

Exam 70-461 topic 1 question 244 discussion

Comments

SY0-701