Module Conclusion: DML and Query Optimization in SQL Server 2025

This module moved from the single-table SELECT work of the previous module into the three statements that actually change data: INSERT, UPDATE, and DELETE. Across fourteen lessons you worked through literal values, cross-table data movement, data type conversion, subqueries inside DML, and the two topics that decide whether any of it performs well at scale: indexing and query optimization. This conclusion pulls those lessons back together so you can see how they build on each other, rather than reviewing them as fourteen separate, disconnected topics.

Lesson 1 introduced the three Data Manipulation Language statements SQL Server gives you for changing data. INSERT adds new rows, UPDATE changes existing rows in place, and DELETE removes rows entirely. Each one can work from literal values typed directly into the statement, from values pulled out of another table, or from a subquery, and each comes with its own basic safety and performance habits: index the columns your WHERE, JOIN, and ON clauses depend on, prefer set-based logic over row-by-row loops, and delete or update in smaller batches on busy production tables. That lesson also introduced CHECK, FOREIGN KEY, and DEFAULT constraints as the modern replacement for SQL Server's long-deprecated CREATE RULE and CREATE DEFAULT objects, both still present in SQL Server 2025 for backward compatibility but with no real reason to reach for in new work.

Lesson 4 returned to DELETE specifically, since it behaves differently enough from UPDATE to deserve its own treatment. It removes entire rows rather than individual column values, it can target a base table, an updatable view, or a rowset function, and an optional second FROM clause lets you qualify which rows to remove by joining to another table entirely, the same pattern used later in lesson 10 for cross-table deletes. The lesson walked through deleting specific rows, deleting through a join, deleting with a subquery, and the more involved pattern of removing duplicate rows with ROW_NUMBER() inside a common table expression. It also covered the operational side: batching large deletes to limit log growth and lock duration, previewing a WHERE condition as a SELECT before converting it to a DELETE, and wrapping a risky delete in an explicit transaction you can roll back during testing. The lesson closed by contrasting DELETE with TRUNCATE TABLE: DELETE logs every row individually, fires DELETE triggers, and leaves an IDENTITY column's counter wherever it was, while TRUNCATE TABLE logs only page deallocation, skips triggers entirely, resets IDENTITY back to its seed value, and refuses to run at all if any foreign key references the table, even one that's completely empty.

Lesson 5 covered the syntax rules behind the literal values used throughout the earlier lessons: single quotes around strings and dates, no quotes around numbers, the N prefix for Unicode text, and ISO 8601 date formatting (YYYY-MM-DD) as the one format that stays unambiguous regardless of server configuration, unlike a slash-separated date that can be silently misread as either month-first or day-first. Lesson 7 followed up with the punctuation rules those literals depend on: a reference table of which SQL Server datatypes require quoted literals and which don't, how to double an internal apostrophe so SQL Server doesn't mistake it for the end of a string, and why Microsoft recommends leaving QUOTED_IDENTIFIER on rather than switching to double-quoted strings, since several modern features, including indexed views and computed columns, depend on it staying on.

Lesson 6 sat between those two syntax lessons and addressed a different question: not how to write a literal correctly, but how much risk you're willing to accept when an UPDATE or DELETE built around one might affect more rows than you expect. The cautious approach checks a row count with SELECT COUNT(*) before running anything destructive. The aggressive approach runs the statement first and checks @@ROWCOUNT afterward, trading a safety check for speed. The balanced approach wraps the statement in an explicit transaction so you can inspect the affected row count before deciding whether to commit or roll back. Which one fits depends on the system: a one-off fix on a small table and a scripted change against a production table of millions of rows call for different amounts of caution, and none of the three is universally correct.

Lesson 8 introduced INSERT INTO ... SELECT, the tool for copying or transforming rows from a source table into a target table, whether for archiving, auditing, staging, or migration. The worked example followed Widget Sales Service recording an employee's prior salary into a SalaryHistory table before applying a raise, mixing a column copied straight from Employee with a literal GETDATE() call inside the same SELECT list. The lesson also covered the IDENTITY column wrinkle, normally left out of both column lists so SQL Server generates it automatically, but explicitly insertable with IDENTITY_INSERT turned on when migrating rows that need to keep their original IDs; SELECT ... INTO for creating a brand-new table from a query in a single step; and a first look at MERGE as the tool that prevents the duplicate-row problem an INSERT ... SELECT run twice by mistake would otherwise create silently.

Lesson 9 moved to the UPDATE equivalent, UPDATE ... FROM, which joins the table being updated to a source table so the SET clause can pull in that source table's values. The lesson's central example gave every Widget Sales Service employee a raise if their last recorded raise was over 365 days old, including employees with no raise on record at all, which required a LEFT JOIN rather than an INNER JOIN so unmatched employees weren't silently dropped from the update. That pattern carries two real hazards: a LEFT JOIN can quietly set a column to NULL for every unmatched row if you forget to guard against it, and if the source side of the join isn't actually unique on the join key, SQL Server picks one of the matching rows without telling you which one. The lesson covered three ways around that second hazard: a correlated subquery in the SET clause for ANSI-standard portability, an updatable CTE when the value you need is itself an aggregate such as a count or a running total, and MERGE, which refuses to guess and stops the statement outright with error 8672 if a single target row matches more than one source row.

Lesson 10 completed the trio with cross-table DELETE: removing rows from one table based on matching rows, or the absence of matching rows, in another. The lesson's sharpest warning concerned NOT IN against a column that can contain NULL. If even one row in the subquery's result has a NULL in that column, NOT IN silently returns no rows at all, while the logically equivalent NOT EXISTS keeps working correctly regardless of NULLs:

-- A NULL anywhere in the subquery result silently zeroes out every row
DELETE FROM Employee
WHERE EmployeeID NOT IN (SELECT EmployeeID FROM Resignations);

-- Unaffected by NULLs, and the recommended pattern
DELETE FROM Employee AS E
WHERE NOT EXISTS (
    SELECT 1 FROM Resignations AS R WHERE R.EmployeeID = E.EmployeeID
);

The same caution applies to IN and EXISTS, and to leaving a column reference unqualified inside a correlated subquery, since SQL Server resolves an unqualified name against whichever table's column matches first rather than raising an error, producing a query that runs without complaint and deletes the wrong rows. The lesson also covered deletable CTEs, MERGE as a synchronization tool that can both insert and delete to keep two tables aligned, and the OUTPUT clause for archiving exactly what a DELETE removed into a separate table in the same statement.

Lesson 11 covered converting between SQL Server data types: CAST and CONVERT for straightforward conversions, and the TRY_CAST, TRY_CONVERT, and TRY_PARSE family for conversions that might fail on messy real-world data, returning NULL instead of raising an error so a single bad row doesn't abort an entire batch. It also introduced SQL Server 2025's native JSON and VECTOR data types, JSON_OBJECT's colon-based construction syntax, VECTOR_DISTANCE for similarity search, and the REGEXP_LIKE family of native regular expression functions for validating a column's shape before attempting a conversion against it, demonstrated by quarantining rows whose department code didn't actually look like a three-digit numeric string before running CAST(DepartmentCode AS int) against them.

Lesson 12 looked at subqueries specifically inside INSERT, UPDATE, and DELETE statements: scalar subqueries that must return exactly one value or SQL Server raises error 512, correlated subqueries that reference the outer statement's current row, derived tables, and WHERE EXISTS as a guard against inserting a duplicate row. It also corrected an older, now-outdated piece of guidance claiming that EXISTS subqueries should generally be rewritten as joins for performance. Under SQL Server 2025's default compatibility level 170, Intelligent Query Processing automatically un-nests a correlated EXISTS subquery into a semi-join during optimization, so a hand-rewritten join usually buys you nothing the optimizer wasn't already doing on its own; it's a change worth knowing about, since plenty of still-circulating guidance predates it.

Lesson 13 turned from writing correct DML to understanding how SQL Server actually executes it. An execution plan, viewed as an Estimated or Actual plan in SSMS, or through the Estimated Plan and Enable Actual Plan buttons in the SQL Server (mssql) extension for Visual Studio Code, shows whether a query used an index seek, an index scan, or a Key Lookup back to the base table for columns the index didn't cover. For a view across your whole workload rather than one query at a time, the missing-index DMVs and Query Store's cached-plan history both surface which indexes the optimizer wanted but didn't have, and sys.dm_db_index_usage_stats shows whether the indexes you already built are actually being read, or just adding write overhead to every INSERT, UPDATE, and DELETE without ever being used. The lesson closed with three SQL Server 2025-specific indexing capabilities: Optional Parameter Plan Optimization for queries with an optional, NULL-checked filter; dedicated CREATE JSON INDEX and CREATE VECTOR INDEX statements for the native data types introduced in lesson 11, with vector indexes still requiring preview features to be enabled in the box product; and the Database Engine Tuning Advisor for workload-wide recommendations the single-query DMVs can't make, such as a new clustered index or a partitioning scheme.

Lesson 14 closed the module with six concrete rules for writing DML and queries that an index can actually help. Keep predicates SARGable by never wrapping an indexed column in a function or a calculation on the comparison side:

-- Forces a scan: the index can't be used once the column is wrapped
WHERE YEAR(OrderDate) = 2025

-- Seekable: the column is left bare
WHERE OrderDate >= '2025-01-01' AND OrderDate < '2026-01-01'

Watch for the Key Lookup tipping point, where reading even a handful of unindexed columns can push the optimizer to abandon a non-clustered index entirely once enough rows qualify. Respect a composite index's left-based key order, since an inequality on one key column blocks a seek on every column after it in that same index. Match a literal's or parameter's exact data type to its target column, so SQL Server doesn't implicitly convert the column itself, row by row, and quietly turn a seek into a scan. Refactor a cross-column OR condition into two UNION ALL branches so each one can seek independently instead of forcing a scan or an index union. And avoid unnecessary index maintenance on writes that don't actually need to happen, a category that now includes SQL Server 2025's Optimized Locking feature: it reduces how long row locks are held during a scan, but it is off by default and has to be deliberately enabled through Accelerated Database Recovery and Read Committed Snapshot Isolation first, not something you get automatically just by moving to SQL Server 2025.

Put together, these fourteen lessons cover ground a working SQL Server developer returns to constantly. Get the data into the right shape with INSERT, UPDATE, and DELETE. Write the literals, data type conversions, and subqueries those statements depend on correctly, with an eye on traps like the NOT IN/NULL interaction that fail silently rather than with an error. And once the logic is right, check whether SQL Server is actually executing it efficiently, using an execution plan, the missing-index DMVs, or Query Store, and fix the query, the index, or both when it isn't. None of these skills stands alone: a technically correct DELETE that scans an entire multi-million-row table because its WHERE clause isn't SARGable is still a problem, even though nothing about it is syntactically wrong. The next module moves from writing individual statements to the broader structures, cursors and views, that wrap around them.

Before moving on to the next module, click the Quiz link below to check your knowledge of the material covered in this module with a short, multiple-choice quiz.
Inserting Data - Quiz