IIF in SQL Server is a short way to write IF THEN logic with two outcomes. This post compares it with CASE in a small juice shop table, shows that both produce the same plan, and covers NULL, data types and the WHERE clause.
Multi-Statement Table-Valued Functions and Interleaved Execution
See how interleaved execution corrects estimates for multi-statement table-valued functions and when an inline rewrite wins.
SQL SERVER – Optimize Key Lookup by Creating Index with Include Columns
A query slowed to over 10 minutes because of a big key lookup. Here is how I optimize key lookup operations by creating an index with include columns.
Multi-Column Index Key Order: Which Column Goes First
Test index key order with equality, range, and OR predicates, then use seek predicates and logical reads to choose the lead column.
Does Sort Order in Index Column Matters for Performance? – Interview Question of the Week #199
Does the sort order in an index column matter for performance? Yes, in some specific scenarios, and here is an example from a recent Health Check question.
What is Read Ahead Read in SQL Server? – Interview Question of the Week #197
Question: What is Read Ahead Read in SQL Server?
Answer: This is SQL Server’s feature which brings data pages into the buffer cache.
Asynchronous Statistics Updates: When They Help and When They Hurt
Evaluate asynchronous statistics updates against compile latency, data-change timing, and low-priority metadata locking in SQL Server.