SQL Server: Why INSERT OUTPUT Does Not Prove a Commit

INSERT OUTPUT can return a new row before the transaction commits. An application can receive an identifier and still end up with no saved record. Treating the first result row as success creates a gap between what the screen says and what the database contains.

A Returned Row Is Not a Commit Receipt

Think of a receipt sitting beside an open parcel. The paperwork exists, but dispatch is not finished. An OUTPUT result describes a data change; it does not promise that the surrounding transaction will keep it.

The distinction matters when an application displays a confirmation, sends an email, or starts another job. Those actions can happen too early if the application reacts to the first returned identifier. Wait for the transaction outcome before treating the operation as successful.

Watch INSERT OUTPUT Survive a Rollback

Run this example in a separate query window without an existing transaction. It creates a temporary table, returns the inserted row, and then rolls back. The table exists before the transaction begins, so we can count its rows afterward.

SET NOCOUNT ON;
SET XACT_ABORT ON;
CREATE TABLE #OutputReceipt
(
    ReceiptId int IDENTITY(1,1) PRIMARY KEY,
    RequestName varchar(40) NOT NULL
);
BEGIN TRANSACTION;
INSERT #OutputReceipt (RequestName)
OUTPUT inserted.ReceiptId, inserted.RequestName
VALUES ('Desk reservation');
ROLLBACK TRANSACTION;
SELECT COUNT(*) AS RowsAfterRollback
FROM #OutputReceipt;
DROP TABLE #OutputReceipt;

I tested this on SQL Server 2025. The first result contains ReceiptId 1 and Desk reservation. The second result contains RowsAfterRollback 0. Both results are correct: the row was returned before the transaction was undone.

Nothing is wrong with OUTPUT here. It is useful for collecting generated values without another lookup. The mistake is giving that result a stronger meaning than it has.

Errors Make the Distinction More Important

A data-changing statement with OUTPUT can return rows even when an error causes the statement to roll back. Discard those results when the command reports an error. An application must process command completion and errors, rather than stopping after reading its first result set.

The example above uses an explicit rollback to make the timing easy to see. It does not simulate a network timeout or depend on a particular constraint error. Its lesson is narrower: a returned row does not establish a committed transaction.

Return the Result After the Transaction You Own Commits

Capture the values with OUTPUT INTO and return them after COMMIT. This standalone example owns its transaction and rejects an existing one. That check matters because an inner COMMIT does not commit an outer transaction.

SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @@TRANCOUNT <> 0
    THROW 50001, 'Run this example without an existing transaction.', 1;
CREATE TABLE #SavedRequest
(
    RequestId int IDENTITY(1,1) PRIMARY KEY,
    RequestName varchar(40) NOT NULL
);
CREATE TABLE #PendingResult
(
    RequestId int NOT NULL,
    RequestName varchar(40) NOT NULL
);
BEGIN TRY
    BEGIN TRANSACTION;
    INSERT #SavedRequest (RequestName)
    OUTPUT inserted.RequestId, inserted.RequestName
        INTO #PendingResult (RequestId, RequestName)
    VALUES ('Desk reservation');
    COMMIT TRANSACTION;
    SELECT RequestId, RequestName
    FROM #PendingResult;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;
    THROW;
END CATCH;
SELECT COUNT(*) AS CommittedRows
FROM #SavedRequest;
DROP TABLE #PendingResult;
DROP TABLE #SavedRequest;

The successful run returns RequestId 1 and CommittedRows 1. On an error, the catch block rolls back any active transaction and rethrows the error. The success result is only selected after the commit succeeds.

This is a teaching example using temporary tables, not a complete order-processing procedure. Keep the transaction boundary explicit when adapting it. If the caller owns the transaction, the caller also decides when the operation has committed.

What This Pattern Does Not Guarantee

A connection can fail after the database commits but before the application receives the result. Returning values after COMMIT reduces premature success messages; it does not remove every uncertain outcome. Retrying that write needs its own design.

For that separate problem, see why retrying an INSERT can create duplicate rows. For the clause itself, my OUTPUT clause examples explain how inserted and deleted values are returned. Keep those mechanics separate from the question of whether the transaction committed.

When reviewing a data-entry workflow, find the exact point where it tells the user that the change succeeded. Check whether that decision follows command completion and the final transaction outcome. A returned identifier is useful evidence, but it is not the whole answer.

INSERT OUTPUT is not a commit receipt, it is a result that still needs a transaction outcome.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.