TOP (100) Problem
The Top N Sort operator uses different implementations depending on the number of rows requested. For up to 100 rows it uses replacement selection and will never spill to tempdb.
Why SQL Server will choose a Hash Match Flow Distinct operator when a row goal is present. Other options for grouping, and when they are chosen.
SQL Server does not always optimize to return the full result set. Various query features can introduce a "row goal", which aims to produce the first few rows quickly.
Reading the contents of a local temporary table created in a different SQL Server session.
SQL Server optimizations for UPDATE statements that do not change data values. Covers logging, dirty pages, and avoiding the update completely.
How SQL Server row mode execution plans execute in detail.
MERGE Bug
A SQL Server bug with MERGE statements on table variables with an OUTPUT clause.
Exploring the internal operation of the SQL Server cost-based optimizer with DBCC RULEON and DBCC RULEOFF.
Using an undocumented DMV to explore the rules used by the SQL Server query optimizer to generate a good execution plan.
How the SQL Server query optimizer matches and applies internal rules to refine an execution plan.
The rules the SQL Server cost-based query optimizer uses to build an execution plan.
SQL Server execution plans for window functions including the Segment Spool plan shape. How you can get a window function plan without writing the OVER clause.
The SQL Server common subexpression spool plan shape, and how it is used to implement queries using the OVER clause.
How the SQL Server Segment and Sequence Project operators are used to implement window functions like ROW_NUMBER, RANK, and DENSE_RANK.
Why you will sometimes see a Top operator directly above a Segment operator in SQL Server execution plans.