Make archiving old rows atomic with DELETE OUTPUT, preserved source identifiers, bounded batches, reconciliation, and a job time limit.
Delete the Wrong Row: A Cheap Answer That Passes the Test
A cheap answer to the multiple table delete puzzle reads only 6 pages and passes the check. It can still delete the wrong row. This post shows the answer, asks you to find the row it removes by mistake, and fixes it with a delete that costs 5 reads.
Fewest Logical Reads: The Answer to the Delete Qualified Rows Puzzle
The fewest logical reads for the multiple table delete puzzle come from reading each table as few times as the rules allow. A string of student IDs gets to 4 reads. A set based delete needs 5 and is the one to use. The starting script needed 19.
SQL Agent Job Backslash Puzzle: Why Both Rows Were Deleted
A SQL Agent job backslash at the end of a comment line can turn a one row delete into a delete of the whole table. This puzzle sets up the job, explains the cause and tests the effect in T-SQL. It ends with a safe way to write the step.
Delete Qualified Rows From Multiple Tables: A SQL Puzzle
Delete qualified rows from multiple tables with as few logical reads as you can. This puzzle gives a setup script for three small tables, the two rules for the delete and a starting script that costs 19 logical reads. Your task is to beat that number and stay correct.
Deleting Millions of Rows in Batches Without Filling the Log
Plan deleting millions of rows with bounded commits, an indexed cutoff, log reuse checks, and a clear choice of retention method.
Deleting From a Heap: Why Empty Pages Stay Behind
The rows are gone but the table is the same size and scans are just as slow. Here is why, and two ways to fix it.