advanced-patterns

A recursive CTE in T-SQL traverses hierarchical data by repeatedly joining a base result set to itself. You define an anchor query for the root level, then add a recursive member that references the CTE to navigate child rows until all levels are resolved.

The MERGE statement performs an upsert in SQL Server by comparing a source table to a target table. It synchronizes the two by conditionally executing INSERT, UPDATE, or DELETE operations in a single atomic statement based on whether rows match.

APPLY operators in SQL Server evaluate a table-valued function or subquery for each row of an outer table. The CROSS APPLY returns only rows that produce results, while OUTER APPLY retains all outer rows even if the inner query yields nothing.

Temporal tables are needed when you require automatic historical data tracking in SQL Server. They maintain a history of all row changes over time, allowing you to query point-in-time data states without manually archiving previous versions.

In-Memory OLTP improves high-throughput query performance in SQL Server by storing tables entirely in memory. It eliminates disk I/O bottlenecks and uses native compilation to significantly reduce latency for heavy transactional workloads.

ROLLUP generates subtotal rows hierarchically, CUBE computes all possible subtotal combinations across grouping columns, and GROUPING SETS allows you to specify exact subtotal groupings. All three produce multiple levels of aggregated data in a single query.