10 - INSERT, UPDATE, DELETE, and OUTPUT Patterns

64 min read

INSERT, UPDATE, DELETE, and OUTPUT Patterns

Conceptual Model

Two invariants apply to every INSERT, UPDATE, and DELETE statement in SQL Server:

  • Set-based execution. Each statement executes as a single logical operation against a set of rows (possibly empty, possibly millions), not as a loop over rows. The query optimizer produces one plan that modifies every qualifying row in one pass.
  • Implicit transactionality. Every DML statement runs inside a transaction. If no explicit BEGIN TRAN is in effect, SQL Server wraps the statement in an auto-commit transaction that commits on success or rolls back on error. Once the statement begins, it either completes entirely or leaves the table unchanged — partial effects are never visible.

These two invariants drive every pattern in the rest of this note.

DML statement comparison matrix

FeatureINSERTUPDATEDELETETRUNCATE
Logs individual rows✅✅✅❌ (page deallocations only)
Can fire triggers✅ (AFTER INSERT, INSTEAD OF INSERT)✅ (AFTER UPDATE, INSTEAD OF UPDATE)✅ (AFTER DELETE, INSTEAD OF DELETE)❌
Can be rolled back in a transaction✅✅✅✅
Supports OUTPUT clause✅✅✅❌
Resets IDENTITY seed❌❌❌✅
Requires FK absence on target❌❌❌✅ (cannot truncate FK-referenced table)
Takes schema-modification lock❌❌❌✅ (SCH-M)
Minimally logged in BULK_LOGGED/SIMPLEOnly via BULK INSERT, SELECT INTO, or INSERT ... SELECT with TABLOCK on empty heap❌❌ (always fully logged)Always (only logs page deallocations)

This matrix answers the first decision every engineer faces when touching data: which statement is the right tool? The sections below document every row of the matrix with a concrete example and its output captured from the stoxx_db database.

INSERT Patterns

INSERT adds new rows to a table. SQL Server offers six orthogonal input forms for the same statement: a literal value list, a multi-row table value constructor, a SELECT derived table, the result set of a stored procedure, DEFAULT VALUES for rows built entirely from defaults, and bulk forms (BULK INSERT, OPENROWSET(BULK ...)) that read from external files. Each form has a specific use case — choosing the right one matters far more for clarity than for raw performance at small row counts, and for raw performance once the row count grows into the thousands or millions.

INSERT ... VALUES | single row with literal values

The simplest form of INSERT provides a value for each column in a literal list. When the target contains an IDENTITY column or a column with a default, those columns must be omitted from the column list and the value list so SQL Server can compute the correct value.

Insert a single country row into bronze.dim_country.

USE [stoxx_db];
GO
 
INSERT INTO bronze.dim_country (country_name, iso_alpha2)
VALUES (N'Atlantis', 'ZZ');
 
SELECT country_name, iso_alpha2
FROM bronze.dim_country
WHERE iso_alpha2 = 'ZZ';
country_nameiso_alpha2
AtlantisZZ

One row inserted, verified by the follow-up SELECT. The INSERT returns (1 rows affected) to the client and increments any row-count metric tied to the statement.

INSERT ... VALUES with table value constructor | insert multiple rows in one statement

The Transact-SQL table value constructor lets a single INSERT statement supply many rows in one VALUES clause. Every row constructor must have the same number of values and the same column order. This is a single logical statement: it takes one table-level lock, writes one entry to the transaction log for each row, and either inserts every row or none of them.

Insert three fictional countries in one statement using the table value constructor.

USE [stoxx_db];
GO
 
INSERT INTO bronze.dim_country (country_name, iso_alpha2)
VALUES
    (N'Wakanda', 'WK'),
    (N'Genovia', 'GV'),
    (N'Narnia',  'NN');
 
SELECT country_name, iso_alpha2
FROM bronze.dim_country
WHERE iso_alpha2 IN ('WK', 'GV', 'NN')
ORDER BY iso_alpha2;
country_nameiso_alpha2
GenoviaGV
NarniaNN
WakandaWK

Three rows inserted in one round trip. The client receives (3 rows affected) — a single statement, not three. If any one of the rows had violated a constraint (for instance, a NULL in a NOT NULL column), all three would have been rejected together.

1 000-row hard limit on the table value constructor

A single table value constructor can hold at most 1 000 row expressions. Attempting 1 001 or more raises error 10738: “The number of row value expressions in the INSERT statement exceeds the maximum allowed number of 1000 row values.”

Use INSERT ... SELECT FROM (VALUES ...) for bigger constants

To insert more than 1 000 literal rows in one statement, wrap the table value constructor in a derived table and feed it to an INSERT ... SELECT. The 1 000-row cap does not apply to SELECT. This is the pattern used by script generators like SSMS → Tasks → Generate Scripts → Data only.

INSERT ... SELECT | copy rows from another query

INSERT ... SELECT is the workhorse form for set-based ingestion from another table, view, or derived table. The SELECT can carry joins, filters, aggregates, CASE expressions, and window functions — anything a regular SELECT can do. The number and types of projected columns must match the INSERT column list.

Log every SAP.DE signal id into the audit table dbo.insert_log.

USE [stoxx_db];
GO
 
INSERT INTO dbo.insert_log (target_table, new_id)
SELECT 'silver.signals_daily', id
FROM silver.signals_daily
WHERE symbol = 'SAP.DE';
 
SELECT log_id, target_table, new_id, logged_at
FROM dbo.insert_log
ORDER BY log_id DESC;
log_idtarget_tablenew_idlogged_at
4silver.signals_daily30062026-04-11 11:30:59.566
3silver.signals_daily52026-04-11 11:30:59.566
2silver.signals_daily20252026-04-11 11:30:59.566
1silver.signals_daily10062026-04-11 11:30:59.566

Every SAP.DE signal id is now recorded in dbo.insert_log with the logging timestamp and logging principal populated from defaults. This is the standard pattern for write-time logging inside an application transaction: a single INSERT ... SELECT replaces any row-by-row logging loop.

Match destination columns by position, not by name

INSERT ... SELECT matches projected columns to the destination column list by position, not by name. A mismatched order causes either a conversion error or, worse, silently loads data into the wrong column. Always spell out both column lists explicitly.

INSERT ... EXEC | load rows from a stored procedure or dynamic SQL result set

INSERT ... EXEC captures the result set returned by a stored procedure (or a dynamic SQL batch) and inserts it into a table. The destination columns must be compatible with the shape of the result set. This pattern is the canonical way to persist the output of a report procedure or a system DMV query.

Capture the result of sys.sp_databases into a temp table.

USE [stoxx_db];
GO
 
IF OBJECT_ID('tempdb..#db_list') IS NOT NULL DROP TABLE #db_list;
 
CREATE TABLE #db_list (
    database_name SYSNAME,
    database_size INT,
    remarks       VARCHAR(254) NULL
);
 
INSERT INTO #db_list (database_name, database_size, remarks)
EXEC sys.sp_databases;
 
SELECT TOP (5) database_name, database_size
FROM #db_list
ORDER BY database_size DESC;
 
DROP TABLE #db_list;
database_namedatabase_size
stoxx1720320
stoxx_db1048576
tempdb73728
msdb16960
model16384

sp_databases returns one row per online database with its size in KB. The INSERT ... EXEC form works with any procedure that returns a tabular result set — including undocumented procs and dynamic SQL built with sp_executesql.

INSERT ... EXEC cannot be nested

If procedure A is being called with INSERT ... EXEC and procedure A itself tries another INSERT ... EXEC, SQL Server raises error 8164: “An INSERT EXEC statement cannot be nested.” This limitation is the main reason sp_executesql and table-valued parameters (TVPs) exist as alternatives for result-set passing between procedures.

Use TVPs, temp tables, or sp_executesql

If one procedure needs to pass a rowset into another, materialize it in a temp table or table variable, pass structured input through a TVP, or keep the dynamic batch inside sp_executesql and capture its final result once. Those patterns avoid the nesting limit and make the hand-off explicit.

INSERT ... DEFAULT VALUES | rely entirely on defaults

INSERT ... DEFAULT VALUES inserts a new row where every column takes its default value. For columns without a default but declared NULL, the inserted value is NULL. For IDENTITY columns, the next identity value is produced. The form is mainly useful for append-only header tables where the default constraints define everything the row needs.

Insert three rows into a temp table purely from defaults.

USE [stoxx_db];
GO
 
IF OBJECT_ID('tempdb..#pings') IS NOT NULL DROP TABLE #pings;
 
CREATE TABLE #pings (
    ping_id  INT IDENTITY(1,1) PRIMARY KEY,
    ping_at  DATETIME2(3) NOT NULL CONSTRAINT df_pings_at DEFAULT SYSUTCDATETIME(),
    source   VARCHAR(20)  NOT NULL CONSTRAINT df_pings_src DEFAULT 'app-1'
);
 
INSERT INTO #pings DEFAULT VALUES;
INSERT INTO #pings DEFAULT VALUES;
INSERT INTO #pings DEFAULT VALUES;
 
SELECT * FROM #pings ORDER BY ping_id;
 
DROP TABLE #pings;
ping_idping_atsource
12026-04-11 11:31:12.226app-1
22026-04-11 11:31:12.226app-1
32026-04-11 11:31:12.231app-1

Three rows produced, each with a fresh IDENTITY value, the current UTC timestamp, and the literal default string. DEFAULT VALUES is the only way to insert a row without naming any columns explicitly.

SELECT ... INTO | create a new table and insert rows in one statement

SELECT INTO is a shortcut that creates a brand-new table (permanent or temporary) from the result of a SELECT and populates it in one step. The destination must not exist already. The new table inherits column names and data types from the source projection but does not inherit indexes, constraints, defaults, or filegroup placement.

Create a temp table holding the 100 highest-beta rows from silver.signals_daily.

USE [stoxx_db];
GO
 
IF OBJECT_ID('tempdb..#top_signals') IS NOT NULL DROP TABLE #top_signals;
 
SELECT TOP (100)
    symbol,
    signal_date,
    forward_pe,
    beta
INTO #top_signals
FROM silver.signals_daily
WHERE beta IS NOT NULL
ORDER BY beta DESC;
 
SELECT COUNT(*) AS rows_loaded FROM #top_signals;
SELECT TOP (3) * FROM #top_signals ORDER BY beta DESC;
 
DROP TABLE #top_signals;
rows_loaded
100
symbol
---
NVDA
NVDA
NVDA

SELECT INTO produces a fresh temp table in one statement — there is no separate CREATE TABLE. It is convenient for ad-hoc analysis and staging but should not replace a deliberately designed target.

SELECT INTO strips constraints, defaults, and indexes

The destination of SELECT INTO is a minimally defined table. It contains no primary key, no foreign keys, no defaults, no check constraints, no indexes, and no trigger bindings. Identity is preserved only if the source projection includes an IDENTITY column directly — otherwise the new table has no identity. For anything beyond a throwaway analysis, create the target with CREATE TABLE first and then INSERT ... SELECT.

Use SELECT INTO for minimally logged heap staging

SELECT INTO into a permanent empty table (or a temp table) can be minimally logged in BULK_LOGGED or SIMPLE recovery, similar to BULK INSERT. This makes it a legitimate fast-path for multi-million-row ETL staging, provided the staging table’s lack of constraints is acceptable for the next step of the pipeline.

BULK INSERT | load a flat file into a table

BULK INSERT reads a delimited or fixed-width flat file from a filesystem path accessible to the SQL Server service account and inserts its rows into a target table. The file path is resolved from the perspective of the SQL Server process — not the client machine — which is critical on containerized or clustered installations. BULK INSERT is the lowest-ceremony way to load CSV data without deploying external tools.

Bulk-load dim_country.csv into bronze.dim_country.

USE [stoxx_db];
GO
 
BULK INSERT bronze.dim_country
FROM '/var/opt/mssql/imports/dim_country.csv'
WITH (
    FORMAT          = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '0x0d0a',
    TABLOCK
);
 
SELECT COUNT(*) AS rows_now FROM bronze.dim_country;
rows_now
428

Before the bulk load, bronze.dim_country held 216 rows (the original 212 plus the four literal inserts earlier in this section). The CSV contains 212 rows which are appended to the table without deduplication, producing 428 rows total. The equivalent statement on a Windows-hosted SQL Server uses a Windows drive letter path and identical WITH options. The CODEPAGE='65001' parameter can be added on Windows when the source file uses UTF-8 and the target column collation is also UTF-8.

Windows parallel example (syntax only):

BULK INSERT bronze.dim_country
FROM N'E:\SQLImports\dim_country.csv'
WITH (
    FORMAT          = 'CSV',
    FIRSTROW        = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR   = '0x0d0a',
    CODEPAGE        = '65001',
    TABLOCK
);

File path is resolved on the SQL Server machine

BULK INSERT does not read files from the client. It reads from the filesystem seen by the SQL Server service account. On a Linux container this means the path must exist inside the container or on a volume mounted into it. On Windows, the SQL Server service account must be able to reach the path and have NTFS read permission on the file.

Grant ADMINISTER BULK OPERATIONS to the bulk loader

Regular users cannot run BULK INSERT. The loading principal needs either the bulkadmin server role or the ADMINISTER BULK OPERATIONS server permission. Grant the minimum by creating a dedicated load login with ADMINISTER BULK OPERATIONS and no other privileges, then use that login for ETL jobs only.

OPENROWSET(BULK ...) | read a flat file as a virtual rowset inside a query

OPENROWSET(BULK ...) turns a flat file into a rowset that can participate in any SELECT, INSERT ... SELECT, UPDATE ... FROM, or DELETE ... FROM statement. It is strictly more powerful than BULK INSERT: the rows can be filtered, joined, projected, or transformed on their way into the target table. OPENROWSET(BULK ...) also supports JSON and XML via SINGLE_CLOB/SINGLE_NCLOB and, with SQL Server 2017+, the FORMAT='CSV' option.

Load only the country rows from the CSV that are not already in the target table.

USE [stoxx_db];
GO
 
INSERT INTO bronze.dim_country (country_name, iso_alpha2)
SELECT
    src.country_name,
    src.iso_alpha2 COLLATE Latin1_General_100_CI_AS_SC_UTF8 AS iso_alpha2
FROM OPENROWSET(
        BULK '/var/opt/mssql/imports/dim_country.csv',
        FORMAT          = 'CSV',
        FIRSTROW        = 2,
        FIELDTERMINATOR = ',',
        ROWTERMINATOR   = '0x0d0a'
    )
    WITH (
        country_name NVARCHAR(200),
        iso_alpha2   CHAR(2)
    ) AS src
WHERE src.iso_alpha2 COLLATE Latin1_General_100_CI_AS_SC_UTF8
      NOT IN (SELECT iso_alpha2 FROM bronze.dim_country);
 
SELECT @@ROWCOUNT AS rows_inserted;
rows_inserted
0

The anti-join filter eliminates every row in the file because every ISO code in the file already exists in bronze.dim_country (twice, after the bulk load above). In a true staging load where the target starts empty, the same query would insert exactly the new countries. This is the key architectural difference from BULK INSERT, which blindly appends every row in the file.

On a Windows-hosted SQL Server, the same query reads from a local path such as N'E:\SQLImports\dim_country.csv'. The FORMAT='CSV', FIRSTROW, field terminator, and row terminator options are identical.

OPENROWSET(BULK ...) inherits the server collation, not the database collation

Character columns returned by OPENROWSET(BULK ...) always take the server-level default collation, not the collation of the target database. On instances where the database uses a modern UTF-8 collation but the server default is still the legacy SQL_Latin1_General_CP1_CI_AS, any string comparison between the loaded rowset and a database column raises error 468 (collation conflict). The fix is an explicit COLLATE clause on each side of every string comparison and on each projected column going into the target table.

Wrap OPENROWSET BULK in a view or inline TVF with pre-applied COLLATE

If the same file is loaded often, wrap the OPENROWSET(BULK ...) expression in an inline table-valued function that applies COLLATE DATABASE_DEFAULT to every string column. Callers then receive correctly collated rows and no longer need to repeat COLLATE on every predicate.

Parquet reading requires an external data source

SQL Server 2022 added OPENROWSET(BULK ... FORMAT='PARQUET') but only through CREATE EXTERNAL DATA SOURCE backed by Azure Blob Storage or S3-compatible storage. Local parquet file reading is not supported directly by T-SQL. For local parquet ingestion, convert to CSV first with duckdb or pandas, or stand up a MinIO/Azurite container exposing the parquet files as an S3/Blob endpoint.

Identity and SEQUENCE

Every insert into a table with an auto-generated key relies on either the IDENTITY column property or a SEQUENCE object. The two mechanisms solve the same problem — producing unique monotonic integers — but with very different scoping, atomicity, and observability guarantees. The silver.signals_daily table has an IDENTITY(1,1) primary-key-style column on id, so every query below runs against a real table.

IDENTITY | auto-increment a column on every insert

IDENTITY(seed, increment) is a column property that makes SQL Server generate the next value automatically every time a new row is inserted. The engine maintains a per-table counter (IDENT_CURRENT) that is independent of transactions: a failed or rolled-back INSERT still advances the counter, producing gaps. This is by design and cannot be disabled.

Insert one row into silver.signals_daily and capture the generated identity value via three different functions.

USE [stoxx_db];
GO
 
INSERT INTO silver.signals_daily
    (_index, symbol, signal_date, current_price, forward_pe, price_to_book,
     ev_to_ebitda, dividend_yield, market_cap, beta, fifty_two_week_change,
     sandp_52_week_change, fifty_day_average, two_hundred_day_average,
     dist_from_52_week_high, target_median_price, recommendation_mean, upside_potential)
VALUES
    ('euro_stoxx_50', 'TEST.XX', '2026-04-11', 100.0, 15.0, 2.0, 10.0, 0.03,
     1000000000, 1.0, 0.1, 0.05, 99.0, 95.0, 0.02, 110.0, 2.0, 0.1);
 
DECLARE
    @new_id_scope   INT = SCOPE_IDENTITY(),
    @new_id_at      INT = @@IDENTITY,
    @new_id_current INT = IDENT_CURRENT('silver.signals_daily');
 
SELECT
    @new_id_scope   AS scope_identity,
    @new_id_at      AS at_identity,
    @new_id_current AS ident_current;
scope_identityat_identityident_current
100099910009991000999

All three functions return 1000999, the identity value the engine generated for the new row. The differences between them only become visible when a trigger inserts into a second identity table, or when another session inserts into the same table concurrently.

SCOPE_IDENTITY() vs @@IDENTITY vs IDENT_CURRENT() | how they differ

FunctionScopeSessionUse when
SCOPE_IDENTITY()Current scope (batch, stored procedure, trigger)Current sessionDefault choice for retrieving the identity of the row you just inserted. Immune to trigger interference.
@@IDENTITYAny scope in the current session (including triggers)Current sessionAlmost never the right answer. Can return the identity value a trigger inserted into a different table.
IDENT_CURRENT('table')Any scopeAny session (global)Only when you need the most recent identity for a specific table globally, knowing it may have been generated by a concurrent session.

@@IDENTITY is unsafe when triggers are involved

If an AFTER INSERT trigger on table A writes to table B and table B has its own identity column, @@IDENTITY returns the identity from table B (the trigger’s insert), not from table A (the user’s insert). Applications that use @@IDENTITY to “get the ID of the row I just inserted” silently break the first day a trigger is added. SCOPE_IDENTITY() is scoped to the calling batch and ignores trigger inserts.

Default to SCOPE_IDENTITY() in application code

Use SCOPE_IDENTITY() as the standard pattern to retrieve the identity of a newly inserted row. Reserve @@IDENTITY for rare cases where you explicitly want trigger-inserted identities, and reserve IDENT_CURRENT() for diagnostics and monitoring scripts where cross-session visibility is desired.

SET IDENTITY_INSERT | override the auto-generated value

SET IDENTITY_INSERT <table> ON lets a session insert explicit values into an identity column. Only one table per session can have IDENTITY_INSERT turned on at any time. When the override is in effect, the explicit value list must include the identity column, and the session must have ALTER permission on the table.

Insert a legacy row into silver.signals_daily with an explicit identity value of 2000000.

USE [stoxx_db];
GO
 
SET IDENTITY_INSERT silver.signals_daily ON;
 
INSERT INTO silver.signals_daily
    (id, _index, symbol, signal_date, current_price, forward_pe, price_to_book,
     ev_to_ebitda, dividend_yield, market_cap, beta, fifty_two_week_change,
     sandp_52_week_change, fifty_day_average, two_hundred_day_average,
     dist_from_52_week_high, target_median_price, recommendation_mean, upside_potential)
VALUES
    (2000000, 'euro_stoxx_50', 'LEGACY.XX', '2020-01-01', 50.0, 12.0, 1.5, 8.0, 0.04,
     500000000, 0.8, 0.05, 0.02, 48.0, 45.0, 0.01, 55.0, 1.5, 0.05);
 
SET IDENTITY_INSERT silver.signals_daily OFF;
 
SELECT id, symbol, signal_date
FROM silver.signals_daily
WHERE id = 2000000;
idsymbolsignal_date
2000000LEGACY.XX2020-01-01

With IDENTITY_INSERT on, the explicit value 2000000 is accepted. This pattern is used to migrate rows from a legacy system while preserving their original primary keys, and to fill identity gaps after a one-time bulk delete.

Only one table per session can have IDENTITY_INSERT ON

Attempting to turn IDENTITY_INSERT on for a second table in the same session while it is still on for the first raises error 7705. Always explicitly turn it off before enabling it on another table.

Turn IDENTITY_INSERT off immediately

Bracket the override as tightly as possible: SET IDENTITY_INSERT ... ON, perform the one load that requires explicit keys, then SET IDENTITY_INSERT ... OFF in the same script block or transaction. That keeps the session from accidentally breaking later loads against a second table.

CREATE SEQUENCE + NEXT VALUE FOR | table-independent counters

A SEQUENCE is a standalone database object that produces monotonic integers independent of any table. Unlike IDENTITY, a sequence can be read by multiple tables, can be sampled without inserting a row (NEXT VALUE FOR), supports bulk allocation via sp_sequence_get_range, and can wrap at the maximum value with CYCLE. SQL Server caches sequence values per session for performance, which means gaps are possible after a server restart.

Create a sequence and use NEXT VALUE FOR directly in three INSERT statements to draw consecutive values.

USE [stoxx_db];
GO
 
IF OBJECT_ID('dbo.seq_order_no','SO') IS NOT NULL DROP SEQUENCE dbo.seq_order_no;
GO
 
CREATE SEQUENCE dbo.seq_order_no
    AS BIGINT
    START WITH 1000
    INCREMENT BY 1
    CACHE 50;
GO
 
IF OBJECT_ID('tempdb..#orders') IS NOT NULL DROP TABLE #orders;
CREATE TABLE #orders (
    order_no BIGINT NOT NULL,
    symbol   VARCHAR(20) NOT NULL,
    qty      INT NOT NULL
);
 
INSERT INTO #orders (order_no, symbol, qty)
VALUES (NEXT VALUE FOR dbo.seq_order_no, 'SAP.DE', 100);
 
INSERT INTO #orders (order_no, symbol, qty)
VALUES (NEXT VALUE FOR dbo.seq_order_no, 'SIE.DE', 200);
 
INSERT INTO #orders (order_no, symbol, qty)
VALUES (NEXT VALUE FOR dbo.seq_order_no, 'ASML.AS', 50);
 
SELECT * FROM #orders ORDER BY order_no;
 
DROP TABLE #orders;
DROP SEQUENCE dbo.seq_order_no;
order_nosymbolqty
1000SAP.DE100
1001SIE.DE200
1002ASML.AS50

The sequence produces 1000, 1001, 1002 across three inserts. Unlike IDENTITY, the same sequence could feed multiple tables simultaneously, or be sampled ahead of time with a bare SELECT NEXT VALUE FOR dbo.seq_order_no without any insert happening at all.

IDENTITY vs SEQUENCE decision

Use IDENTITY for the common case of a single-table auto-generated primary key — simpler to create, easier for tooling, and the surrounding ecosystem assumes it. Use SEQUENCE when you need cross-table uniqueness (e.g., a shared event_id across ten audit tables), when you must allocate a block of numbers before the insert happens (e.g., for a parent-then-children pattern without round trips), or when you want explicit control over caching, cycling, or minimum/maximum bounds.

UPDATE Patterns

UPDATE modifies existing rows in place. Its surface is smaller than INSERT’s but its pitfalls are larger: the T-SQL UPDATE ... FROM ... JOIN extension is non-deterministic when the join is ambiguous, UPDATE with TOP selects rows in an arbitrary order unless an outer ORDER BY controls it, and a missing WHERE clause quietly updates every row of the table.

Searched UPDATE | single-table update with a predicate

The standard UPDATE form sets one or more columns for every row matching a WHERE predicate. If WHERE is omitted, every row is updated — there is no safety net. SQL Server takes exclusive (X) locks on the affected rows and intent exclusive (IX) locks on the enclosing page and table for the duration of the transaction.

Set the upside_potential of a single row to a fixed value.

USE [stoxx_db];
GO
 
UPDATE silver.signals_daily
SET upside_potential = 0.25
WHERE symbol      = 'SAP.DE'
  AND signal_date = '2026-04-08';
 
SELECT symbol, signal_date, upside_potential
FROM silver.signals_daily
WHERE symbol      = 'SAP.DE'
  AND signal_date = '2026-04-08';
symbolsignal_dateupside_potential
SAP.DE2026-04-080.25

One row updated. The locking sequence is IX on the table → IX on the page → X on the row → release at commit. No other session can read this row during the transaction unless the database is in READ_COMMITTED_SNAPSHOT ON mode, in which case readers see the pre-update version from the version store.

UPDATE without WHERE updates every row

Running UPDATE silver.signals_daily SET upside_potential = 0.25 without a WHERE clause modifies every row in the table. There is no SQL Server safeguard against this. The only defenses are (1) opening every ad-hoc UPDATE in an explicit transaction so an accidental update can be rolled back, (2) writing the SELECT form of the predicate first and only converting it to UPDATE once the row count is confirmed, and (3) using tooling (SSMS → Tools → Options → Query Execution → SET ROWCOUNT or the IntelliSense UPDATE safeguard).

Preview, then update inside a transaction

Write the SELECT version of the predicate first, verify the row count, then convert it to UPDATE inside an explicit transaction so an accidental full-table write can be rolled back before commit. That is the minimum safe workflow for ad-hoc production updates.

UPDATE ... FROM ... JOIN | T-SQL extension for joined updates

The T-SQL UPDATE ... FROM ... JOIN extension lets an update use another table as a data source for both the predicate and the new column values. This is the most common way to propagate reference data, synchronize lookup columns, or apply derived calculations that require joining to dimensional tables.

Apply a 5% price bump to all German stocks on 2026-03-04 by joining gold.scores_daily to bronze.dim_country.

USE [stoxx_db];
GO
 
UPDATE sd
SET sd.current_price = sd.current_price * 1.05
FROM gold.scores_daily AS sd
JOIN bronze.dim_country AS dc ON dc.country_name = sd.country
WHERE dc.iso_alpha2 = 'DE'
  AND sd.score_date = '2026-03-04';
 
SELECT TOP (5)
    symbol,
    country,
    current_price
FROM gold.scores_daily
WHERE country    = 'Germany'
  AND score_date = '2026-03-04'
ORDER BY symbol;
symbolcountrycurrent_price
ADS.DEGermany148.89000000000001
ALV.DEGermany376.94999999999999
BAS.DEGermany48.457500000000003
BAYN.DEGermany39.270000000000003
BMW.DEGermany86.772000000000006

Sixteen German scores were updated in one statement — the join to bronze.dim_country provided the ISO code filter without a hard-coded country list. This is the canonical pattern for applying a lookup-driven transformation to a fact table.

Non-deterministic UPDATE with multi-match joins

If the join produces multiple source rows for the same target row, SQL Server picks one of them arbitrarily and uses its columns for the update. The picked row is not guaranteed to be stable across runs or plans. Microsoft’s own best-practice guidance flags this as undefined behavior. Detecting the condition up front requires a SELECT COUNT(*) OVER (PARTITION BY <target_key>) check in the source query or a GROUP BY rewrite.

Force determinism with a CTE that pre-aggregates the source

When the source might produce multiple rows per target, wrap it in a CTE that aggregates to at most one row per target key (GROUP BY, ROW_NUMBER() = 1, or MAX(...)). Then join the UPDATE to the CTE. The update becomes deterministic and the error mode shifts from “silent wrong answer” to “compile-time visible intent”.

UPDATE with CTE | scope-limited updates through a named subquery

A CTE (common table expression) can be used as a derived source that drives an UPDATE. The most common use case is computing a set of rows with ranking or aggregation before applying the modification.

Update only the most recent signal row for SAP.DE and SIE.DE.

USE [stoxx_db];
GO
 
WITH latest AS (
    SELECT symbol, MAX(signal_date) AS latest_date
    FROM silver.signals_daily
    GROUP BY symbol
)
UPDATE sd
SET sd.upside_potential = 0.99
FROM silver.signals_daily AS sd
JOIN latest AS l
    ON l.symbol      = sd.symbol
   AND l.latest_date = sd.signal_date
WHERE sd.symbol IN ('SAP.DE', 'SIE.DE');
 
SELECT symbol, signal_date, upside_potential
FROM silver.signals_daily
WHERE symbol IN ('SAP.DE', 'SIE.DE')
  AND upside_potential = 0.99;
symbolsignal_dateupside_potential
SAP.DE2026-04-080.98999999999999999
SIE.DE2026-04-080.98999999999999999

Exactly two rows updated — the latest date per symbol for SAP.DE and SIE.DE. Float representation shows 0.99 as 0.98999999999999999; use decimal(p,s) instead of float for columns where exact equality matters. The WHERE sd.symbol IN (...) clause pushes the filter down before the join, so the CTE is effectively evaluated only for the two relevant symbols.

UPDATE TOP (n) | bounded update without a predictable order

UPDATE TOP (n) modifies at most n rows. In isolation, TOP (n) without an ORDER BY is non-deterministic: SQL Server is free to pick any n rows satisfying the WHERE predicate. This is a common footgun for ETL scripts that assume a time-ordered selection.

Update at most two rows matching the predicate and return the count.

USE [stoxx_db];
GO
 
UPDATE TOP (2) silver.signals_daily
SET recommendation_mean = 1.0
WHERE symbol = 'SAP.DE';
 
SELECT COUNT(*) AS rows_affected
FROM silver.signals_daily
WHERE symbol              = 'SAP.DE'
  AND recommendation_mean = 1.0;
rows_affected
2

Two rows were updated, but the identity of those two rows depends on the plan. For deterministic bounded updates, use a derived table with ORDER BY as the source of a join.

UPDATE TOP (n) selection is non-deterministic

UPDATE TOP (n) without an accompanying subquery with ORDER BY picks rows in an unspecified order. Identical runs on the same data can update different rows if the plan changes, so this form should never be used for time-ordered batch processing, leader elections, or any logic that requires stable selection.

Deterministic bounded updates with a subquery + ORDER BY

The supported pattern is UPDATE sd SET ... FROM silver.signals_daily sd INNER JOIN (SELECT TOP (100) id FROM silver.signals_daily WHERE symbol = 'SAP.DE' ORDER BY signal_date DESC) t ON t.id = sd.id;. The inner ORDER BY produces a stable selection of the 100 most recent rows, and the outer UPDATE modifies only those.

UPDATE with correlated subquery in SET | compute new values from aggregates

A correlated subquery inside the SET clause computes a new value for each row from an aggregate or another table. This form is more portable than UPDATE ... FROM ... JOIN — it works on standards-compliant databases too — but is often slower because the engine may materialize the subquery per row.

Replace the target_median_price of each SAP.DE row with the average target price of all rows in the same index.

USE [stoxx_db];
GO
 
UPDATE silver.signals_daily
SET target_median_price = (
    SELECT AVG(sd2.target_median_price)
    FROM silver.signals_daily sd2
    WHERE sd2._index = silver.signals_daily._index
)
WHERE symbol = 'SAP.DE';
 
SELECT symbol, signal_date, target_median_price
FROM silver.signals_daily
WHERE symbol = 'SAP.DE'
ORDER BY signal_date;
symbolsignal_datetarget_median_price
SAP.DE2026-03-04301.04887365326641
SAP.DE2026-03-07301.04887365326641
SAP.DE2026-03-12301.04887365326641
SAP.DE2026-04-08301.04887365326641

All four SAP.DE rows now hold the same value — the euro_stoxx_50 index average — computed once per row but typically factored out by the optimizer into a scalar aggregate subtree. The same operation could be written with UPDATE ... FROM ... JOIN against a CTE of pre-aggregated averages for better readability at higher volumes.

UPDATE ... SET @var = column = expression | update a row and capture old/new value in one statement

SQL Server supports a composite assignment syntax: SET @variable = column = expression. This assigns the expression to the column (the update) and to the variable (the capture) in one step, allowing a single statement to both modify a row and remember its final value. Use this only when modifying one row — with multi-row updates, the variable holds the last-assigned row’s value, which is non-deterministic.

Update a single row, capturing the old and new price into two variables for logging.

USE [stoxx_db];
GO
 
DECLARE @old_price FLOAT, @new_price FLOAT;
 
UPDATE silver.signals_daily
SET
    @old_price = current_price,
    @new_price = current_price = current_price * 1.10
WHERE symbol      = 'SAP.DE'
  AND signal_date = '2026-04-08';
 
SELECT @old_price AS old_price, @new_price AS new_price;
old_pricenew_price
145.22159.74200000000002

The first assignment @old_price = current_price captures the pre-update value. The chained @new_price = current_price = current_price * 1.10 writes the new value to the column and simultaneously captures it into @new_price. For multi-row updates, prefer the OUTPUT clause (see below) — it captures every affected row’s before/after values deterministically.

DELETE Patterns

DELETE removes rows from a table. Like UPDATE, a missing WHERE clause removes every row — but unlike UPDATE, there is a faster alternative (TRUNCATE TABLE) when every row should go. The choice between DELETE and TRUNCATE is driven by recoverability requirements, trigger firing behavior, foreign key presence, and identity seed behavior, all documented in the decision matrix at the end of this section.

Searched DELETE | remove rows matching a predicate

The standard DELETE form removes every row matching a WHERE predicate. The rowcount is returned to the client as @@ROWCOUNT and via the (N rows affected) message. Deleted rows are exclusively locked for the duration of the transaction and written to the transaction log — DELETE is always fully logged regardless of recovery model.

Delete one specific row.

USE [stoxx_db];
GO
 
DELETE FROM silver.signals_daily
WHERE symbol      = 'SAP.DE'
  AND signal_date = '2026-03-04';
 
SELECT @@ROWCOUNT AS deleted_rows;
 
SELECT COUNT(*) AS remaining_sap_de
FROM silver.signals_daily
WHERE symbol = 'SAP.DE';
deleted_rows
1
remaining_sap_de
---:
3

One row deleted, three SAP.DE rows remain. @@ROWCOUNT reflects the actual number of rows the last statement modified.

DELETE ... FROM ... JOIN | delete rows by joining to another table

T-SQL extends DELETE with a FROM clause that can join additional tables. The target table appears twice: once as the target of DELETE and once as a (usually aliased) row source in the FROM clause. This form is the most compact way to delete rows based on a condition expressed against a related dimension.

Delete every silver.signals_daily row whose date is not a trading day in bronze.trading_calendar.

USE [stoxx_db];
GO
 
DELETE sd
FROM silver.signals_daily AS sd
WHERE NOT EXISTS (
    SELECT 1
    FROM bronze.trading_calendar AS tc
    WHERE tc.date           = sd.signal_date
      AND tc.is_trading_day = 1
);
 
SELECT @@ROWCOUNT AS deleted_non_trading;
deleted_non_trading
152

152 rows removed because their signal_date did not match any trading-day entry in bronze.trading_calendar. In a real ETL pipeline, this pattern is used to enforce referential integrity against a date dimension when the source data layer is not constrained.

DELETE TOP (n) | bounded delete without a guaranteed order

DELETE TOP (n) removes at most n rows matching the predicate. Like UPDATE TOP, the selection is non-deterministic without a subquery containing ORDER BY. The primary legitimate use of DELETE TOP is as the delete step of a batched loop.

Delete at most 10 signal rows whose symbol starts with a digit.

USE [stoxx_db];
GO
 
DELETE TOP (10) FROM silver.signals_daily
WHERE symbol LIKE '0%';
 
SELECT @@ROWCOUNT AS deleted_rows;
deleted_rows
3

Three rows match the symbol LIKE '0%' predicate after the earlier anti-semi-join delete, so the TOP (10) bound is never reached. When the predicate matches more than n rows, TOP (n) picks any n of them — the specific ones chosen depend on the physical plan.

Batched DELETE | remove many rows without blocking the log

When a DELETE needs to remove millions of rows from a busy table, a single DELETE statement holds row locks for the entire operation, fills up the transaction log, and can escalate to a table lock that blocks every other session. The canonical fix is a batched loop that deletes a small chunk at a time, commits each iteration, and stops when no rows remain. This keeps the log footprint small, gives the log backup process time to truncate between batches, and lets blocked sessions get a turn between chunks.

Delete all low-market-cap signal rows in chunks of 25, tracking total deleted rows via a captured @@ROWCOUNT.

USE [stoxx_db];
GO
 
DECLARE
    @iter          INT = 0,
    @chunk         INT = 25,
    @total_deleted INT = 0,
    @last_rows     INT;
 
WHILE 1 = 1
BEGIN
    DELETE TOP (@chunk) FROM silver.signals_daily
    WHERE market_cap < 50000000000
       OR market_cap IS NULL;
 
    SET @last_rows = @@ROWCOUNT;
 
    IF @last_rows = 0 BREAK;
 
    SET @total_deleted += @last_rows;
    SET @iter          += 1;
 
    IF @iter > 20 BREAK;  -- safety valve
END
 
SELECT @iter AS batches, @total_deleted AS total_deleted;
batchestotal_deleted
247

Two batches of 25 and 22 rows remove 47 rows in total. At production scale, the @chunk value is typically between 1 000 and 10 000, and each batch is committed with COMMIT; BEGIN TRAN; (or the loop runs without an outer transaction at all). The combination of small chunks and frequent commits lets log backups keep up with the log growth.

@@ROWCOUNT is reset by every statement

@@ROWCOUNT reflects the row count of the most recently executed statement, including IF, SELECT, SET, and implicit statements inside control flow. Reading @@ROWCOUNT after any such statement returns a number unrelated to the DML you care about. The fix is to capture @@ROWCOUNT into a local variable on the line immediately after the DML statement and reference the variable everywhere else.

Pattern: DECLARE @rc INT; DELETE ...; SET @rc = @@ROWCOUNT;

Every batched or conditional DML pattern should capture @@ROWCOUNT into a dedicated variable on the next line. Treat @@ROWCOUNT as a volatile register that is overwritten on every statement.

TRUNCATE TABLE | remove every row at maximum speed

TRUNCATE TABLE removes every row from a table by deallocating its pages. It is faster than DELETE without a WHERE clause, uses minimal transaction log space, and resets the identity counter to its seed value. It does not fire DELETE triggers, does not work on tables referenced by a foreign key, and does not work on tables participating in indexed views, system-versioned temporal tables, or replication.

Truncate a temp table populated from silver.signals_daily and confirm the row count drops to zero.

USE [stoxx_db];
GO
 
IF OBJECT_ID('tempdb..#scratch') IS NOT NULL DROP TABLE #scratch;
 
SELECT symbol, signal_date, current_price
INTO #scratch
FROM silver.signals_daily
WHERE symbol = 'ASML.AS';
 
SELECT COUNT(*) AS before_truncate FROM #scratch;
 
TRUNCATE TABLE #scratch;
 
SELECT COUNT(*) AS after_truncate FROM #scratch;
 
DROP TABLE #scratch;
before_truncate
3
after_truncate
---:
0

Three rows in, zero rows out. TRUNCATE releases the pages immediately for tables smaller than 128 extents, or deferred to a background process for larger tables.

TRUNCATE TABLE cannot fire DELETE triggers

TRUNCATE removes rows by deallocating pages without touching individual rows, so there is no row-level event for trigger binding to observe. Any audit trail or cascade implemented via AFTER DELETE or INSTEAD OF DELETE triggers will silently miss truncations. If full audit coverage is required, replace TRUNCATE with a logged DELETE + trigger, or add the TRUNCATE_TABLE event to a database-level DDL trigger.

Use DELETE when trigger side effects matter

If audit triggers, custom cascades, or row-level delete logic are part of the contract, keep a logged DELETE even for whole-table removal. TRUNCATE is only the safe choice when page-deallocation semantics are acceptable and no row-level side effect is required.

DELETE vs TRUNCATE TABLE | choose by semantics

Use DELETE when row-level behavior matters: WHERE filters, OUTPUT, row triggers, indexed views, system-versioned tables, and FK-referenced tables all require it. Use TRUNCATE TABLE when you need to empty the whole table and can accept identity reseed, ALTER permission, and the inability to target rows selectively.

DELETE is the safer default when audit, referential integrity, or partial removal matters. TRUNCATE TABLE is the faster whole-table path only when no row-level trigger or OUTPUT dependency exists and resetting the identity seed is acceptable.

Keep the logged DELETE when the choice is uncertain. It costs more logging, but it preserves the widest set of semantics.

The OUTPUT Clause

The OUTPUT clause returns information about every row affected by an INSERT, UPDATE, DELETE, or MERGE statement. It exposes two virtual pseudo-tables — INSERTED and DELETED — that mirror the behavior of the same-named pseudo-tables inside triggers. OUTPUT is the cleanest way to capture before/after values for an audit trail, to return identity values for freshly inserted rows, to build queue-like dequeue operations, and to compose DML statements into higher-level workflows.

Two production tables back the examples in this section: dbo.audit_price_changes (a row-change audit trail with old/new values and the principal who made the change) and dbo.archive_signals_daily (a row archive on the FG_Archive filegroup for soft-deleted signal rows). Both tables are defined without triggers, without foreign keys, and without CHECK constraints because OUTPUT INTO cannot target tables that carry any of those.

INSERTED and DELETED pseudo-tables | which rows are visible from which statement

StatementINSERTED.* visibleDELETED.* visible
INSERT✅ new row values❌ (no pre-image — raises error if referenced)
UPDATE✅ new row values✅ pre-image values
DELETE❌✅ row values as they existed before the delete
MERGE✅ for INSERT/UPDATE actions✅ for UPDATE/DELETE actions

Rows returned by OUTPUT are not ordered. SQL Server does not guarantee that the order of rows in the OUTPUT result matches any particular sequence — not primary key order, not insertion order, not the order of the underlying scan. Any consumer that depends on ordering must either sort the captured rows after the fact or use the OUTPUT INTO form to capture them and then SELECT ... ORDER BY.

UPDATE ... OUTPUT | return before/after values directly to the client

The simplest use of OUTPUT on an UPDATE returns the pre-image and post-image of every affected row as a result set. The client sees the result set immediately, and the calling application can use it for confirmation messages, diffing, or forwarding to downstream systems.

Apply a 10% price bump to every ASML.AS row and return a before/after delta for each affected row.

USE [stoxx_db];
GO
 
UPDATE silver.signals_daily
SET current_price = current_price * 1.10
OUTPUT
    INSERTED.symbol,
    INSERTED.signal_date,
    DELETED.current_price  AS old_price,
    INSERTED.current_price AS new_price
WHERE symbol = 'ASML.AS';
symbolsignal_dateold_pricenew_price
ASML.AS2026-03-121191.21310.3200000000002
ASML.AS2026-03-041199.81319.78
ASML.AS2026-04-081113.81225.1800000000001

Three rows were updated and three rows were returned to the client — one result set per affected row, in a single round trip. No separate SELECT is needed after the update to verify the change. The row order in the output is not guaranteed to match the signal_date order; if the caller needs it ordered, either sort client-side or use the OUTPUT INTO pattern below.

OUTPUT ... INTO audit table | capture affected rows into a persistent audit trail

OUTPUT ... INTO sends the captured rows into a persistent table that serves as an audit trail. The dbo.audit_price_changes table is designed specifically to be a valid OUTPUT INTO target: it has an IDENTITY primary key, default changed_at and changed_by columns, no triggers, no foreign keys, and no check constraints. The OUTPUT clause populates the symbol, signal_date, old_price, and new_price columns while the table defaults fill in the audit metadata automatically.

Apply a 5% price cut to one MC.PA row and log the change to dbo.audit_price_changes.

USE [stoxx_db];
GO
 
UPDATE silver.signals_daily
SET current_price = current_price * 0.95
OUTPUT
    INSERTED.symbol,
    INSERTED.signal_date,
    DELETED.current_price,
    INSERTED.current_price
INTO dbo.audit_price_changes (symbol, signal_date, old_price, new_price)
WHERE symbol      = 'MC.PA'
  AND signal_date = '2026-04-08';
 
SELECT TOP (5)
    audit_id, symbol, signal_date, old_price, new_price, changed_at, changed_by
FROM dbo.audit_price_changes
ORDER BY audit_id DESC;
audit_idsymbolsignal_dateold_pricenew_pricechanged_atchanged_by
1MC.PA2026-04-08466.85000000000002443.507499999999992026-04-11 11:38:32.748sa

One row updated, one row logged. The audit table now has the full before/after trail for the price change, plus the UTC timestamp and the principal that made the change — all populated atomically inside the same UPDATE statement. This pattern replaces AFTER UPDATE triggers for audit workloads: it is explicit, visible in the source code, and does not add hidden runtime behavior.

Target of OUTPUT INTO has tight restrictions

The destination table of OUTPUT INTO cannot have enabled triggers, cannot participate on either side of a foreign key, and cannot have CHECK constraints or enabled rules. dbo.audit_price_changes was deliberately created without any of these so it can serve as an OUTPUT INTO target.

Design audit tables for OUTPUT INTO

Build the sink as a narrow append-only table with defaults and an identity key, but without triggers, foreign keys, or CHECK constraints. Enforce richer relationships downstream, not on the immediate OUTPUT INTO landing table.

DELETE ... OUTPUT | capture removed rows before they disappear

DELETE with OUTPUT DELETED.* returns every row that was just removed. This is the canonical pattern for “destructive read” queue-pop operations, where a consumer claims a message by deleting it from the queue table and immediately processing the returned row. The client sees the deleted row as the result set of the DELETE statement and can forward it to a downstream system.

Delete one MC.PA row and return its full payload in the same round trip.

USE [stoxx_db];
GO
 
DELETE FROM silver.signals_daily
OUTPUT
    DELETED.id,
    DELETED.symbol,
    DELETED.signal_date,
    DELETED.current_price
WHERE symbol      = 'MC.PA'
  AND signal_date = '2026-03-04';
idsymbolsignal_datecurrent_price
2MC.PA2026-03-04507.39999999999998

The deleted row is returned as a result set in the same round trip as the DELETE. In a queue scenario, the consumer would execute this as its single “claim and process” step — guaranteeing exactly-once semantics as long as the consumer handles its own idempotency.

DELETE TOP (1) ... WITH (READPAST) OUTPUT DELETED.* is the canonical queue pop

For queue-like workloads, combine DELETE TOP (1) (one message at a time), the READPAST table hint (skip rows currently locked by other consumers), and OUTPUT DELETED.* (return the claimed message). This pattern supports multiple concurrent consumers without deadlocks and without needing an application-level queue manager.

Composable DML | chain a DML statement’s OUTPUT into another INSERT

The composable DML form wraps a DML statement with an OUTPUT clause in parentheses and uses it as a rowset source for an outer INSERT statement. This moves rows atomically from one table to another: the inner DELETE (or UPDATE or MERGE) produces the rows, and the outer INSERT persists them elsewhere.

Move every remaining MC.PA row from silver.signals_daily into dbo.archive_signals_daily in one composable DML statement.

USE [stoxx_db];
GO
 
INSERT INTO dbo.archive_signals_daily (signal_id, _index, symbol, signal_date, current_price)
SELECT
    src.id,
    src._index,
    src.symbol,
    src.signal_date,
    src.current_price
FROM (
    DELETE FROM silver.signals_daily
    OUTPUT
        DELETED.id,
        DELETED._index,
        DELETED.symbol,
        DELETED.signal_date,
        DELETED.current_price
    WHERE symbol = 'MC.PA'
) AS src;
 
SELECT archive_id, signal_id, symbol, signal_date, current_price, archived_at, archived_by
FROM dbo.archive_signals_daily
ORDER BY archive_id;
archive_idsignal_idsymbolsignal_datecurrent_pricearchived_atarchived_by
12022MC.PA2026-03-12494.399999999999982026-04-11 11:38:43.355sa
23003MC.PA2026-04-08443.507499999999992026-04-11 11:38:43.355sa

Two rows were deleted from the source and inserted into the archive in one statement. The DELETE and INSERT are atomic: either both succeed or both are rolled back. The archive table now holds the historical MC.PA data on the FG_Archive filegroup (lower-cost storage), while the silver.signals_daily table no longer contains any MC.PA rows. This is the canonical soft-delete / tiering pattern for ETL pipelines.

Composable DML still has a narrow operating envelope. The target of the outer INSERT cannot be a view or remote table, cannot have triggers, cannot participate in foreign key relationships, and cannot participate in replication. The inner DML statement cannot be nested further (no composable DML inside composable DML), cannot contain a WITH clause, cannot target remote tables or partitioned views, and cannot be a cursor-based UPDATE/DELETE. In practice this keeps composable DML in the dedicated staging/archive-table category.

OUTPUT rows are returned even if the statement fails

Per Microsoft: “An UPDATE, INSERT, or DELETE statement that has an OUTPUT clause will return rows to the client even if the statement encounters errors and is rolled back.” A client that reads the OUTPUT result set and uses it for business logic can act on rows that were never actually persisted. Always check for errors (or use XACT_ABORT ON + TRY/CATCH) before trusting OUTPUT results, and never treat OUTPUT as the sole commit signal.

Treat OUTPUT rows as provisional until commit

Consume OUTPUT inside the same transaction, check for errors, and only let downstream logic act after the statement or transaction commits successfully. XACT_ABORT ON plus TRY/CATCH is the safe envelope for any workflow that depends on OUTPUT.

MERGE and OUTPUT $action | pointer

MERGE statements can use OUTPUT with a special $action column that returns 'INSERT', 'UPDATE', or 'DELETE' for each affected row, identifying which merge branch produced the row. See 11-merge-and-upsert for the full treatment.

MERGE has known concurrency issues

Even with HOLDLOCK on the target, MERGE is susceptible to race conditions under concurrent inserts that can produce primary-key violations or silently skip intended actions. Microsoft KB articles document several well-known bugs in MERGE plan choice that were fixed over multiple cumulative updates. A common alternative is to run two separate statements inside one transaction: UPDATE target SET ... FROM target JOIN staging ON key to apply the changes to matching rows, then INSERT INTO target SELECT ... FROM staging WHERE NOT EXISTS (SELECT 1 FROM target t WHERE t.key = staging.key) to add the new rows. This pattern produces more predictable query plans, avoids the known MERGE concurrency bugs, and is easier to read and tune. See 11-merge-and-upsert for the full trade-off analysis.

Prefer separate UPDATE and INSERT steps

Run UPDATE ... FROM for the matching rows and INSERT ... WHERE NOT EXISTS for the missing rows inside one explicit transaction. The plan shape is easier to reason about, the concurrency behavior is more predictable, and the code is usually simpler to tune than MERGE.

Transactions, Errors, and Halloween Protection

All SQL Server DML runs inside a transaction, but the behavior of that transaction under errors depends on three settings: XACT_ABORT, SET IMPLICIT_TRANSACTIONS, and the presence of TRY/CATCH blocks. Incorrect combinations leave partial changes committed, dangling open transactions, or swallow errors that should have surfaced.

Explicit transactions | BEGIN TRAN, COMMIT, ROLLBACK

An explicit transaction is opened with BEGIN TRAN, closed with COMMIT TRAN, and aborted with ROLLBACK TRAN. Every DML statement between the BEGIN and the COMMIT is part of the same transaction, and either all of them commit or none of them do. Explicit transactions are the right default for any multi-statement operation that must be atomic.

Atomically update two symbols in one transaction and commit both at once.

USE [stoxx_db];
GO
 
BEGIN TRAN;
 
UPDATE silver.signals_daily
SET current_price = current_price * 1.02
WHERE symbol = 'ALV.DE' AND signal_date = '2026-04-08';
 
UPDATE silver.signals_daily
SET current_price = current_price * 1.02
WHERE symbol = 'SIE.DE' AND signal_date = '2026-04-08';
 
COMMIT;

Both updates are part of the same transaction. There is no state in which only the ALV.DE update is persisted but the SIE.DE update is not. If the second statement raised an error, the whole transaction would be in a DOOMED state (assuming XACT_ABORT ON) and a subsequent ROLLBACK would undo both.

TRY/CATCH + XACT_ABORT | standard error-handling envelope

SQL Server’s structured error handling consists of three pieces: SET XACT_ABORT ON forces the whole transaction to roll back on any run-time error (instead of silently continuing), the TRY/CATCH block catches the error so the client sees a controlled response, and XACT_STATE() reports whether the transaction is still active, doomed, or already rolled back.

Wrap two updates in a TRY/CATCH block with XACT_ABORT ON; the second update fails and both updates are rolled back atomically.

USE [stoxx_db];
GO
 
SET XACT_ABORT ON;
 
BEGIN TRAN;
 
BEGIN TRY
    UPDATE silver.signals_daily
    SET current_price = current_price * 1.05
    WHERE symbol = 'NVDA';
 
    UPDATE silver.signals_daily
    SET current_price = current_price / 0
    WHERE symbol = 'SIE.DE';
 
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;
 
    SELECT
        ERROR_NUMBER()  AS err_no,
        ERROR_MESSAGE() AS err_msg,
        XACT_STATE()    AS xact_state;
END CATCH;
err_noerr_msgxact_state
8134Divide by zero error encountered.0

The first update affected NVDA rows, then the second update hit the divide-by-zero error and the whole transaction was rolled back by XACT_ABORT. After the CATCH block executes, XACT_STATE() = 0 confirms the transaction is no longer open. A follow-up SELECT MAX(current_price) FROM silver.signals_daily WHERE symbol = 'NVDA' shows the pre-error price, proving the rollback restored the NVDA rows that had already been updated earlier in the transaction.

SET XACT_ABORT OFF is the default and it is unsafe for DML

With XACT_ABORT OFF, many runtime errors (divide by zero, arithmetic overflow, deadlock victim, some constraint violations) leave the transaction active. The next statement continues to execute as if nothing happened, and a COMMIT at the end persists an inconsistent state. The connection-level default depends on the driver: ODBC and SQLClient usually default to ON, OLE DB often defaults to OFF. Set it explicitly at the top of any batch that contains DML.

Always begin DML batches with SET XACT_ABORT ON; SET NOCOUNT ON;

SET XACT_ABORT ON makes transaction behavior predictable. SET NOCOUNT ON suppresses the “(N rows affected)” messages so the client protocol is not polluted with row-count metadata. Together they form the standard prolog for any production stored procedure or DML batch.

Halloween Protection | why SQL Server adds a spool to some UPDATE plans

The Halloween problem occurs when an UPDATE statement modifies a column that is used in its own search predicate or join. Naïvely executed, the update would re-read rows it already updated and modify them again, producing endless work or wrong results. SQL Server detects this pattern and inserts a blocking spool (typically an eager spool) into the plan to materialize all affected rows before any updates are applied. This is Halloween Protection, and it is the reason many seemingly simple updates show a spool operator in their plan.

The term dates to 1976, when IBM researchers discovered the problem while testing System R on Halloween. The name stuck. The SQL Server optimizer flags the condition as Halloween Protection Required in its plan output.

Performance Patterns

Minimally logged operations | drop transaction log volume on bulk loads

Some DML patterns can be minimally logged instead of fully logged, recording only extent-level allocation pages instead of per-row log records. Minimal logging can drop log volume by 10× or more on large loads. It requires all of the following conditions:

  • The database must be in BULK_LOGGED or SIMPLE recovery model (not FULL).
  • The target table must be a heap (no clustered index) — or be partitioned with specific partition-switch patterns.
  • The load statement must use one of the minimally logged forms: BULK INSERT, INSERT ... SELECT ... WITH (TABLOCK), SELECT INTO, or bcp from a client tool.
  • TABLOCK (or BULK_UPDATE lock) must be in effect on the target so the optimizer can use the minimally logged code path.
  • If the target already contains rows, the target must either be empty or the INSERT must target a new partition/filegroup.

In FULL recovery (the default on stoxx_db), even BULK INSERT with TABLOCK is fully logged — the recovery model override is mandatory. Switching a production database to BULK_LOGGED is acceptable during maintenance windows but must be coupled with a log backup immediately after the bulk operation to preserve the log backup chain.

Batching large DML | split a huge operation into manageable chunks

A single DELETE or UPDATE that affects millions of rows holds locks for the entire duration, balloons the transaction log, and can escalate to a full table lock. Size the batch to the workload, not to the maximum row count the engine can tolerate.

  • 100 to 1 000 rows fits highly contended OLTP targets.
  • 1 000 to 10 000 rows fits standard cleanup jobs and background maintenance.
  • 10 000 to 100 000 rows fits warehouse staging tables and low-contention targets.
  • Above 100 000 rows is usually unnecessary and increases retry cost and log pressure.

The canonical remediation is still the same batched pattern used earlier in this note: loop with a bounded TOP (n) DML statement, commit after each chunk, and stop when @@ROWCOUNT reports zero. Apply the same structure to batched UPDATE and batched INSERT ... SELECT.

Row-by-row anti-pattern | “RBAR”

Processing DML row-at-a-time via a cursor or a WHILE loop over a key column is the most common performance anti-pattern in T-SQL. It multiplies per-row overhead (logging, locking, stats updates, trigger invocations) by the number of rows, turning a 200 ms set-based operation into a 20 minute cursor loop. The fix is always to rewrite the logic as a set-based INSERT ... SELECT, UPDATE ... FROM ... JOIN, or MERGE. The only legitimate row-at-a-time use cases are genuinely sequential algorithms (running totals before window functions existed, recursive graph traversal where a CTE is insufficient, external API calls that must happen per row) — and even then, batching into chunks of 100+ rows per iteration is usually preferable to true RBAR.

Anti-Patterns

UPDATE without WHERE | updates every row silently

Missing a WHERE clause produces a statement that compiles, executes without warning, and touches every row in the table. On a large table, this can be unrecoverable without a restore from backup. Guard against it by opening DML in an explicit transaction (BEGIN TRAN) and only committing after a row-count sanity check.

DELETE without WHERE when TRUNCATE would do

When every row should be removed, DELETE without WHERE is slower than TRUNCATE, generates a log record per row, and leaves empty pages allocated on a heap unless a TABLOCK hint is used. Prefer TRUNCATE TABLE when FK, trigger, and identity-reset semantics permit it.

SELECT INTO as a production table creation mechanism

SELECT INTO is fast but creates a table without primary key, foreign keys, check constraints, defaults, indexes, or triggers. Using it to materialize a production table guarantees the next developer has to add everything back by hand, usually after an incident. Reserve SELECT INTO for throwaway analysis and temp-table staging.

UPDATE with non-deterministic FROM ... JOIN

When the source table joined to the update target has multiple matching rows per target row, SQL Server arbitrarily picks one. The picked row is stable on a particular plan but changes when statistics are updated, when new rows are added to the source, or when the plan is evicted from cache. Pre-aggregate the source with a CTE that produces at most one row per target key.

@@IDENTITY in application code

@@IDENTITY returns the most recent identity value from any scope in the current session, including trigger-inserted rows in a completely unrelated table. Application code that calls SELECT @@IDENTITY after an INSERT to learn the new row’s key breaks silently the first day an AFTER INSERT trigger with its own identity column is added. Use SCOPE_IDENTITY() unconditionally.

NEWID() as a clustered primary key | random inserts at end of table

A uniqueidentifier column populated with NEWID() produces random values, which means every insert lands at an arbitrary position in a clustered B-tree. The resulting page splits, fragmentation, and write amplification can reduce insert throughput by an order of magnitude on busy systems. Use NEWSEQUENTIALID() instead when the clustered key must be a GUID — it produces monotonically increasing values within the current boot session, eliminating the random-insert fragmentation without giving up the uniqueness guarantee. Better yet, use an INT or BIGINT IDENTITY for the clustered key and relegate the GUID to a non-clustered unique index if the application surface needs it.

Cursor loops for bulk DML

Cursor-based FETCH ... DML loops run the DML statement one row at a time, multiplying every per-statement cost by the row count. For any DML that can be expressed set-based (which is nearly all of it), the cursor form is 10× to 1000× slower. Replace cursors with set-based INSERT ... SELECT, UPDATE ... FROM ... JOIN, DELETE ... FROM ... JOIN, or MERGE.

OUTPUT as the only success signal

OUTPUT rows are emitted to the client stream even when the statement fails and rolls back. Treating a non-empty OUTPUT result as proof of commit is unsafe. Pair OUTPUT with XACT_ABORT ON + TRY/CATCH and only act on captured rows after a successful COMMIT.

Decision Guide

Use these grouped rules instead of a lookup table. The note already demonstrates each pattern in the sections above.

Inserts

  • INSERT ... VALUES (...) for one literal row.
  • INSERT ... VALUES (...), (...), ... for 2 to 1 000 literal rows.
  • INSERT ... SELECT ... FROM (VALUES (...)) AS t(...) for larger constant sets.
  • INSERT ... SELECT for set-based copies from another table or view.
  • INSERT ... EXEC for stored-procedure result sets.
  • SELECT ... INTO for throwaway staging only.
  • CREATE TABLE ... + INSERT ... SELECT for production schema.
  • BULK INSERT or OPENROWSET(BULK ...) for file loads.

Updates

  • UPDATE ... WHERE <key> for one row by key.
  • UPDATE ... FROM ... JOIN for lookup-driven changes.
  • UPDATE with a CTE or correlated subquery in SET for aggregate-driven changes.
  • UPDATE joined to SELECT TOP (N) ... ORDER BY for deterministic bounded updates.

Deletes

  • DELETE ... WHERE <key> for one row by key.
  • TRUNCATE TABLE when every row should go and the table is eligible.
  • DELETE without WHERE when triggers, foreign keys, or other constraints block TRUNCATE.
  • Batched DELETE TOP (N) loops for very large removals.

Capture and move rows

  • UPDATE ... OUTPUT DELETED.col, INSERTED.col to capture before/after values.
  • UPDATE ... OUTPUT ... INTO dbo.audit_* to build an audit trail without a trigger.
  • DELETE TOP (1) WITH (READPAST) ... OUTPUT DELETED.* for queue-style destructive reads.
  • Composable DML to move rows between tables atomically.
  • MERGE only when the upsert tradeoffs are acceptable; see 11-merge-and-upsert.

Graph View

Backlinks