T-SQL Advanced Queries: CTEs, Window Functions, and Performance

Introduction

[Explain why mastering advanced T-SQL constructs increases maintainability and performance for reporting & transactional systems.]

Prerequisites

SQL Server 2019+ environment
Access to sample database (e.g., AdventureWorks)

Core Patterns Overview

Pattern	Use Case	Performance Consideration
CTE	Recursive / readable segmentation	Inline optimization; watch recursion depth
Window Functions	Analytics (ranking, aggregation)	Indexing on partition/order columns
APPLY	Row-by-row function/table evaluation	Can replace cursor logic
Table Variables vs Temp Tables	Session-scoped intermediate data	Temp tables better for large sets

Step-by-Step Guide

Step 1: CTE for Hierarchical Data

WITH OrgCTE AS (
  SELECT Id, ParentId, Name, 0 AS Level FROM Departments WHERE ParentId IS NULL
  UNION ALL
  SELECT d.Id, d.ParentId, d.Name, o.Level + 1
  FROM Departments d
  JOIN OrgCTE o ON d.ParentId = o.Id
)
SELECT * FROM OrgCTE ORDER BY Level;

Step 2: Window Functions for Running Totals

SELECT CustomerId, OrderDate, Amount,
       SUM(Amount) OVER (PARTITION BY CustomerId ORDER BY OrderDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Orders;

Step 3: APPLY for Inline Logic

SELECT o.Id, ca.Calculated
FROM Orders o
CROSS APPLY (
  SELECT SUM(Amount) AS Calculated FROM OrderLines WHERE OrderId = o.Id
) ca;

Step 4: Performance Diagnostics

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run queries and review logical reads / CPU time

Best Practices

Favor window functions over self-joins for analytics
Use proper indexing strategy aligned to PARTITION BY / ORDER BY
Avoid scalar UDF hotspots (consider inline TVFs)

Common Issues & Troubleshooting

Issue: Slow window function queries
Solution: Add covering index on partition+order columns

Issue: Recursive CTE runaway
Solution: Add MAXRECURSION safeguard or termination condition

Key Takeaways

Advanced patterns reduce imperative cursor complexity.
Window functions enable efficient analytic aggregations.
Systematic diagnostics guide indexing improvements.

Next Steps

Explore batch mode on rowstore scenarios
Evaluate Query Store insights for regressions

Additional Resources

Which T-SQL feature saved you the most time recently?