70-465 Question #26: Real Exam Question with Answer & Explanation

Question

You have a database named DB1. You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each table, but the value must increase from one invocation of the stored procedure to the next. Occasionally, the identifying value must be reset to its initial value. You need to design a mechanism to hold the identifying values for the stored procedure to use. What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

Options

• ACreate a sequence object that holds the next value in the sequence.
• BCreate a sequence object that holds the next value in the sequence.
• CCreate a fourth table that holds the next value in the sequence.
• DCreate an identity column in each of the three tables.

Explanation

The scenario requires a shared, incrementing identifier across three tables that can also be reset - a sequence object is purpose-built for exactly this use case.

Common mistakes.

• B. Choice B is identical in wording to Choice A and is a duplicate option; A is selected as the canonical best answer, making B redundant rather than a distinct solution.
• C. A fourth table could store the next value, but it introduces concurrency risks (e.g., dirty reads, update anomalies) and requires additional locking logic that a native SEQUENCE object handles automatically and more efficiently.
• D. An IDENTITY column is scoped to a single table and auto-increments independently per table, so the three tables would generate different, unsynchronized values; additionally, IDENTITY values cannot be easily reset without truncating the table or using DBCC CHECKIDENT, making it unsuitable for a shared, resettable identifier.

Concept tested. SQL Server SEQUENCE object for shared incrementing values

Reference. https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql

No community discussion yet for this question.