SQL Server OUTPUT: Capture Changes Without a Second Guess

Updating rows and then selecting them again asks two different questions: which rows did this statement change, and what do matching rows contain now? Those answers can diverge under concurrent activity. OUTPUT attaches a result to the modifying statement itself, making it useful for generated keys, change previews, and precise application responses.

Capture a stable relationship

The example uses temporary tables and changes two inventory rows. It captures the key, old quantity, and new quantity from the UPDATE. The final ORDER BY makes presentation deterministic; it does not depend on the physical order in which SQL Server performed the updates.

IF @@TRANCOUNT <> 0
    THROW 50000, 'This example owns its transaction.', 1;
SET XACT_ABORT ON;
CREATE TABLE #Stock (ItemId int PRIMARY KEY, Qty int NOT NULL);
INSERT #Stock VALUES (1, 12), (2, 20);
CREATE TABLE #Changed (ItemId int, OldQty int, NewQty int);
BEGIN TRY
    BEGIN TRAN;
    UPDATE #Stock
    SET Qty = Qty - 2
    OUTPUT inserted.ItemId, deleted.Qty, inserted.Qty
        INTO #Changed(ItemId, OldQty, NewQty)
    WHERE ItemId IN (1, 2) AND Qty >= 2;
    COMMIT;
    SELECT ItemId, OldQty, NewQty FROM #Changed ORDER BY ItemId;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
    THROW;
END CATCH;

Keep the key in the captured result. Returning only quantities forces the client to guess which input each result belongs to. For multirow inserts, retain a unique client correlation value in the inserted data and return that alongside the generated identity. Pairing the first returned identity with the first input row is not a valid mapping strategy.

Capture only the columns needed by the caller. OUTPUT inserted.* looks convenient but binds an API to table shape and can copy large values unnecessarily. An explicit column list makes type changes visible and reduces accidental exposure of fields that were never intended for that response.

Separate captured output from committed success

The transaction boundary remains decisive. OUTPUT is not a receipt that the business transaction has committed. A later statement can fail, a transaction can roll back, or the connection can disappear while commit is being acknowledged. Applications must consume the complete command outcome and handle errors before treating returned data as successful work.

The example writes output into a temporary table, commits, and only then returns the captured rows. If the UPDATE or commit fails, the CATCH rolls back and throws rather than selecting a success result. The guard rejects an outer transaction because otherwise this procedure could report success before its caller makes the final commit decision.

That pattern addresses local response sequencing, not the lost-response problem. If commit succeeds but the client never receives the result, a retry needs a durable operation identifier to discover the previous outcome. A temporary capture table disappears with the session and cannot answer that later question. Store correlation and outcome permanently when the API requires retry-safe writes.

Understand trigger and audit boundaries

The inserted values exposed by OUTPUT represent the modifying statement before AFTER triggers run. If a trigger subsequently normalizes a value, the captured value can differ from the final stored value. Decide which contract the client needs. When final stored values are required, capture keys and perform an appropriately isolated final read inside a clearly defined transaction design.

A direct OUTPUT result also has restrictions when enabled triggers exist for the action. OUTPUT INTO is often the relevant form, but its destination has its own limitations involving triggers and constraints. Check the real schema rather than testing only on an empty demonstration table.

For auditing, a result sent to the caller is not a durable audit trail. A transactional audit table records committed changes if it is written in the same transaction; a rollback removes that audit write too. Recording failed attempts requires a separate design. Choose deliberately whether the requirement concerns successful state changes, attempted operations, or both.

Finally, measure the capture cost for large modifications. Returning millions of before-and-after rows can turn a compact update into a substantial memory, log, and network operation. A narrow key-and-status response may serve the API better. Test successful updates, a forced failure, trigger-modified values, and multirow key correlation before making OUTPUT part of a public write contract.

Technical references: Microsoft Learn: OUTPUT · Microsoft Learn: TRY CATCH.