Benchmarked NONE / ROW / PAGE data compression on SQL Server 2025 across five data shapes. There is no single compression ratio — ROW reclaims fixed-width waste, PAGE adds dictionary compression for repetitive data, and neither touches high-entropy data. Plus the counterintuitive bits: compression that speeds up inserts, the warm-vs-cold read tradeoff, and what random-key inserts actually do to a compressed table.
A database that once used a memory-optimized table, migrated back to disk, and was left with a MEMORY_OPTIMIZED_DATA filegroup it can't get rid of — and an XTP background thread burning CPU with zero in-memory tables. Reproduced on SQL Server 2019 and 2025: why it happens, every removal trick that fails (with the exact error numbers), and the only fix that actually works.
A VARCHAR column with a _UTF8 collation stores all of Unicode in UTF-8 — and roughly halves an ASCII column versus NVARCHAR. Benchmarked on SQL Server 2025 across English, Lithuanian and CJK data: where it wins, where it makes things 1.5× bigger, and the implicit-conversion trap that turns an index seek into a 1000-read scan.
Benchmarked on SQL Server 2025 with the query plans as receipts. Table variables win for tiny sets and lose badly for large ones — and the reason isn't 'temp tables are on disk'. It's statistics and parallelism, and I can show you both.
IIF doesn't exist at execution time — the plan shows it compiled to the exact same CASE expression, and the benchmark confirms the tie. The whole question is style. Here's the proof, so your team can stop arguing.
COALESCE compiles to a CASE expression; ISNULL stays an intrinsic — and measures about twice as fast per row. But the reasons to choose one over the other are semantic, and I have repro snippets for each.
Per-row benchmarks of + against CONCAT on SQL Server 2025 — a tie at two strings, CONCAT increasingly ahead from four on, and a NULL-handling difference that matters more than either.
Benchmarked grouped string concatenation on SQL Server 2025. STRING_AGG is flat and predictable; the XML PATH variant that beats it is the one that silently corrupts your data, and the correct XML variant swings from 3× slower to 3× faster depending on group shape.
I benchmarked six cleanup strategies on SQL Server 2025, with temp-table caching verified by performance counters. Short answer: the DROP is doing less than you think — and one innocent-looking habit costs 15× more.
The methodology behind the variable-assignment benchmark: duration-based timing loops, forced recompiles, interleaved passes, min-of-trials, and a no-op baseline — with the actual harness code.
I benchmarked seven ways of declaring and assigning T-SQL variables on SQL Server 2025 — almost 3,000 timed runs. The keyword barely matters; the statement count does, and mostly at compile time.