nerdexam
Microsoft

70-465 · Question #20

Drag and Drop Question You plan to deploy SQL Server 2014. You are designing two stored procedures named SP1 and SP2 that have the following requirements: - Prevent data read by SP1 from being modifie

The correct answer is repeatable read; read committed. SP1 requires 'repeatable read' because this isolation level prevents other transactions from modifying data that has been read within the current transaction (preventing non-repeatable reads), while still allowing concurrent reads - it is the minimum level that satisfies this req

Submitted by krish.m· Mar 5, 2026Design and implement database objects - specifically recommending appropriate transaction isolation levels to balance data consistency requirements with maximum concurrency in SQL Server 2014 (typically aligned with Microsoft 70-465 or 70-462 certification objectives).

Question

Drag and Drop Question You plan to deploy SQL Server 2014. You are designing two stored procedures named SP1 and SP2 that have the following requirements: - Prevent data read by SP1 from being modified by other active processes. - Prevent SP2 from performing dirty reads. You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency. Which isolation levels should you recommend? To answer, drag the appropriate isolation level to the correct stored procedure in the answer area. Answer:

Exhibit

70-465 question #20 exhibit

Answer Area

Drag items

read committedread uncommittedrepeatable readserializable

Correct arrangement

  • repeatable read
  • read committed

Explanation

SP1 requires 'repeatable read' because this isolation level prevents other transactions from modifying data that has been read within the current transaction (preventing non-repeatable reads), while still allowing concurrent reads - it is the minimum level that satisfies this requirement with maximum concurrency. SP2 only needs to prevent dirty reads (reading uncommitted data from other transactions), and 'read committed' is the lowest isolation level that achieves this, maximizing concurrency by only holding shared locks for the duration of the read operation rather than the entire transaction.

Topics

#SQL Server Isolation Levels#Concurrency Control#Stored Procedures#Transaction Management

Community Discussion

No community discussion yet for this question.

Sign up to join the discussion
Full 70-465 Practice