advanced-patterns

# Advanced T-SQL Patterns

Advanced techniques for complex SQL Server scenarios.

## Quick Reference

### Pattern Selection Guide

| Task | Pattern |
|------|---------|
| Hierarchical data | Recursive CTE |
| Top N per group | ROW_NUMBER + CTE |
| Correlated subquery alternative | CROSS/OUTER APPLY |
| Upsert (insert or update) | MERGE |
| Capture modified rows | OUTPUT clause |
| Historical data tracking | Temporal tables |
| High-throughput OLTP | In-Memory OLTP |
| Multiple aggregation levels | ROLLUP/CUBE/GROUPING SETS |

## Common Table Expressions (CTEs)

### Basic CTE
```sql
WITH RecentOrders AS (
    SELECT CustomerID, OrderDate, Amount
    FROM Orders
    WHERE OrderDate >= DATEADD(month, -3, GETDATE())
)
SELECT c.CustomerName, r.Amount
FROM Customers c
JOIN RecentOrders r ON c.CustomerID = r.CustomerID;
```

### Multiple CTEs
```sql
WITH
Sales AS (
    SELECT ProductID, SUM(Amount) AS TotalSales FROM Orders GROUP BY ProductID
),
Inventory AS (
    SELECT ProductID, SUM(Quantity) AS TotalInventory FROM Stock GROUP BY ProductID
)
SELECT p.ProductName, s.TotalSales, i.TotalInventory
FROM Products p
LEFT JOIN Sales s ON p.ProductID = s.ProductID
LEFT JOIN Inventory i ON p.ProductID = i.ProductID;
```

### Recursive CTE (Hierarchies)
```sql
WITH OrgChart AS (
    -- Anchor: Top-level (no manager)
    SELECT EmployeeID, Name, ManagerID, 0 AS Level,
           CAST(Name AS VARCHAR(1000)) AS Path
    FROM Employees
    WHERE ManagerID IS NULL

    UNION ALL

    -- Recursive: Subordinates
    SELECT e.EmployeeID, e.Name, e.ManagerID, oc.Level + 1,
           CAST(oc.Path + ' > ' + e.Name AS VARCHAR(1000))
    FROM Employees e
    JOIN OrgChart oc ON e.ManagerID = oc.EmployeeID
)
SELECT * FROM OrgChart
OPTION (MAXRECURSION 100);  -- Default is 100, max is 32767
```

### CTE for Deleting Duplicates
```sql
WITH Duplicates AS (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY CreatedDate DESC
           ) AS RowNum