Articles

SQL Server MERGE Interview Questions: Duplicate Matches, Locking, and Concurrent Upserts

Practise SQL Server MERGE interview questions on duplicate source matches, missing-key races, HOLDLOCK, explicit upserts, and retry-safe state changes.

A SQL Server MERGE interview becomes interesting when the target row already exists twice in the source, two sessions both see a missing key, or a retry repeats more than a harmless assignment. Writing WHEN MATCHED and WHEN NOT MATCHED is only the start. You need to explain cardinality, isolation, and the business rule that determines the final state.

Official facts in this guide come from Microsoft Learn for SQL Server. Original exercises and design reasoning use a deliberately small account-state table. Evidence boundary: these are independent preparation cases, not candidate-reported employer questions. The T-SQL scripts are supplied for a disposable SQL Server lab; they were reviewed against documentation but were not executed against a SQL Server instance for this article.

For an adjacent exercise, Simulate Transaction Lock Scheduling lets you reason about blocking and scheduling. Its simulator is not evidence of SQL Server's actual lock acquisition plan.

Question 1: What happens when two source rows match one target?

Official fact: Microsoft's MERGE reference states that an UPDATE action cannot update the same target row more than once; multiple source rows matching it cause an error. MERGE does not automatically choose the newest source row.

Create this original lab table in a scratch database:

CREATE TABLE dbo.MergeInterviewAccount (
    AccountKey int NOT NULL PRIMARY KEY,
    Revision bigint NOT NULL,
    Balance decimal(12,2) NOT NULL
);
INSERT dbo.MergeInterviewAccount VALUES (7, 1, 10.00);

Now supply two versions of account 7:

MERGE dbo.MergeInterviewAccount AS t
USING (VALUES
    (7, CAST(2 AS bigint), CAST(20.00 AS decimal(12,2))),
    (7, CAST(3 AS bigint), CAST(30.00 AS decimal(12,2)))
) AS s(AccountKey, Revision, Balance)
ON t.AccountKey = s.AccountKey
WHEN MATCHED THEN UPDATE
    SET Revision = s.Revision, Balance = s.Balance
WHEN NOT MATCHED THEN INSERT (AccountKey, Revision, Balance)
    VALUES (s.AccountKey, s.Revision, s.Balance);

The expected failure follows from the documented multiple-match rule. Sorting this source does not turn the statement into sequential revisions. Explain the mismatch: the business request is “retain the latest state,” while the supplied relation has two proposed states for one target key.

If account 7 is absent, the problem changes. Both source rows can qualify for insertion, and the primary key prevents both from becoming target rows. That is a uniqueness collision, distinct from updating one existing row twice. Without a target uniqueness constraint, bad source cardinality can instead create duplicate target keys.

Question 2: How should you reduce the source deterministically?

First define the policy. In this exercise, higher revision wins; within an equal revision, higher EventID wins. Assume EventID is unique within each AccountKey. That ordering must be approved as a business rule, not invented merely to make SQL stop failing.

WITH Ranked AS (
    SELECT AccountKey, Revision, Balance, EventID,
           ROW_NUMBER() OVER (
               PARTITION BY AccountKey
               ORDER BY Revision DESC, EventID DESC
           ) AS rn
    FROM dbo.AccountStage
), OnePerKey AS (
    SELECT AccountKey, Revision, Balance
    FROM Ranked WHERE rn = 1
)
SELECT AccountKey, Revision, Balance FROM OnePerKey;

This is a query shape for a staging table with the stated columns, not a continuation of the earlier VALUES statement. Validate the staged relation before using it as MERGE's source.

Official fact: Microsoft's ROW_NUMBER documentation explains the importance of unique ordering values for repeatable row assignment. A timestamp alone is insufficient when two rows share it.

Design reasoning: a deterministic winner can still be semantically wrong. If equal revisions with different balances indicate corrupted input, quarantine the conflict rather than allowing EventID to conceal it. If duplicate events are identical replays, collapse them under an explicit event-identity rule.

SELECT DISTINCT is not a universal repair: rows (7,2,20) and (7,3,30) are distinct yet share the target key. Aggregating MAX(Revision) and MAX(Balance) independently can manufacture a combination that never existed. Select a whole winning row or reject the ambiguity.

Question 3: Why should ON describe identity, not update eligibility?

Suppose the target has account 7 at revision 5, and the source carries revision 4. The account exists, but the source is stale.

Official fact: the MERGE reference warns against filtering target rows in the ON condition. Keep the match on the business key. Put the revision condition on the matched action:

ON t.AccountKey = s.AccountKey
WHEN MATCHED AND s.Revision > t.Revision THEN UPDATE
    SET Revision = s.Revision, Balance = s.Balance

If you instead use ON t.AccountKey = s.AccountKey AND s.Revision > t.Revision, the stale source fails the match. An insert branch can then attempt a new row for the already-existing account. This is a classification mistake, not a missing-index problem.

Also ask whether the source is a complete snapshot or a partial batch. In an incremental feed, absence from this batch does not mean absence from the business system. A WHEN NOT MATCHED BY SOURCE THEN DELETE branch needs a completeness guarantee and the correct scope. Do not delete accounts just because today's arrival batch omitted them.

Question 4: Is one statement automatically a concurrency-safe upsert?

No. Atomic execution of a statement does not mean every competing operation observes the transaction order you intend.

Official fact: Microsoft's MERGE guidance discusses concurrency complications and says HOLDLOCK can prevent unique-key violations in some insert/update scenarios. It recommends workload testing. That is not a blanket guarantee that adding one hint resolves every source, trigger, deadlock, and isolation issue.

Original two-session exercise: first demonstrate the check-then-insert hazard without attributing a deterministic failure to MERGE itself. Remove only scratch key 42 before the exercise; do not reset it while either transaction is active.

Run session A, then run session B while A is waiting:

-- Session A: deliberately unsafe check-then-insert
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
IF NOT EXISTS (
    SELECT 1 FROM dbo.MergeInterviewAccount WHERE AccountKey = 42
)
BEGIN
    WAITFOR DELAY '00:00:10';
    INSERT dbo.MergeInterviewAccount VALUES (42, 1, 100.00);
END;
COMMIT;

-- Session B: run during A's delay, with key 42 initially absent
INSERT dbo.MergeInterviewAccount VALUES (42, 2, 200.00);

The expected interleaving is A reads absence, B inserts and commits, then A attempts the same primary key. The constraint protects the table by rejecting the duplicate. It does not make A's business operation succeed. If A errors, explicitly roll back any transaction left open before another run.

The delay makes the intended ordering easy to arrange. Record whether B completed during that window; a run with different timing is a different experiment. Record database isolation options, server build, schema, and actual errors. Do not describe a planned script as a measured reproduction.

Question 5: What protection does HOLDLOCK add?

Official fact: Microsoft's table-hint documentation describes HOLDLOCK as serializable semantics for the referenced table and UPDLOCK as update locks held through transaction completion. The locking and row-versioning guide explains how key-range protection prevents qualifying phantom insertions under serializable access.

Design reasoning: protecting a missing-key decision means protecting the relevant range, not merely locking a row that does not exist. A suitable key index helps make that protection and lookup focused. The exact resources and modes depend on the execution plan, isolation configuration, and server features; verify them in the real workload.

A candidate MERGE pattern may use MERGE dbo.MergeInterviewAccount WITH (HOLDLOCK) AS t with one source row per key and ON restricted to identity. Compare it against an explicit transactional implementation. Measure contention, not just correctness in a single session.

Serializable protection can introduce waiting and deadlocks. It does not determine which source revision deserves to win, deduplicate a malformed batch, or make an external notification transactional. Keep those guarantees separate in your explanation.

Question 6: Can you write an explicit upsert without hiding the race?

This original single-key example uses replacement semantics: the submitted balance is a complete state, not an amount to add. It acquires protection before deciding whether the key is absent, and applies only newer revisions.

DECLARE @Key int = 42, @Revision bigint = 3;
DECLARE @Balance decimal(12,2) = 300.00;
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRANSACTION;
    IF EXISTS (
        SELECT 1
        FROM dbo.MergeInterviewAccount WITH (UPDLOCK, HOLDLOCK)
        WHERE AccountKey = @Key
    )
    BEGIN
        UPDATE dbo.MergeInterviewAccount
        SET Revision = @Revision, Balance = @Balance
        WHERE AccountKey = @Key AND Revision < @Revision;
    END
    ELSE
    BEGIN
        INSERT dbo.MergeInterviewAccount (AccountKey, Revision, Balance)
        VALUES (@Key, @Revision, @Balance);
    END;
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

Official fact: Microsoft's XACT_ABORT reference documents transaction-abort behavior for runtime errors and notes that THROW honors the setting. Compile errors are a different category.

Design boundary: this demonstration owns its transaction. Do not paste its unconditional rollback into a procedure that participates in an outer caller transaction without designing ownership and error handling. Equal revisions are no-ops here; the caller must separately detect conflicting payloads when that matters.

For a batch, process keys in a consistent order where feasible and keep transactions short. A retry policy should identify retryable failures, bound attempts, and rerun the whole intended transaction after rollback. Do not retry a source-cardinality error as though it were transient contention.

What would prove retry safety?

Idempotent final state is weaker than exactly-once side effects. Assigning revision 3 and balance 300 twice can leave the same row. Adding 300 twice cannot. Sending two emails or appending two audit events may also violate the contract despite an unchanged balance.

Use this original verification matrix:

Experiment	Required observation
Two source rows for an existing key	Rejected or reduced according to a declared policy
Two proposed inserts for one absent key	Uniqueness preserved; winner/error handling understood
Older revision arrives after newer	Newer target state retained
Same revision, same payload replayed	No unintended state or side-effect duplication
Same revision, conflicting payload	Conflict surfaced under the business policy
Two concurrent writers to absent key	No duplicate key rows; blocking/error path measured
Transaction chosen as deadlock victim	Whole unit rolled back and retried within bounds

Capture final rows, errors, blocking, and externally visible effects. Compare MERGE and explicit DML on the same schema and workload. A slogan that MERGE is always safe or always forbidden cannot substitute for that evidence.

Sources and Further Reading

Microsoft: MERGE — duplicate matches, ON conditions, and concurrency guidance.
Microsoft: ROW_NUMBER — ranking and deterministic ordering conditions.
Microsoft: Table hints — HOLDLOCK and UPDLOCK semantics.
Microsoft: Transaction locking and row versioning — isolation, range protection, and contention.
Microsoft: SET XACT_ABORT — runtime errors and transaction handling.