advanced-patterns

What this skill does

# 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
    FROM Users
)
DELETE FROM Duplicates WHERE RowNum > 1;
```

## APPLY Operator

### CROSS APPLY (Inner Join Behavior)
```sql
-- Top 3 orders per customer
SELECT c.CustomerID, c.Name, o.OrderID, o.Amount
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderID, Amount
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;
```

### OUTER APPLY (Left Join Behavior)
```sql
-- Last order per customer (including customers with no orders)
SELECT c.CustomerID, c.Name, o.LastOrderDate, o.LastOrderAmount
FROM Customers c
OUTER APPLY (
    SELECT TOP 1 OrderDate AS LastOrderDate, Amount AS LastOrderAmount
    FROM Orders
    WHERE CustomerID = c.CustomerID
    ORDER BY OrderDate DESC
) o;
```

### APPLY with Table-Valued Function
```sql
-- Call function for each row
SELECT c.CustomerID, f.MonthlyTotal, f.OrderCount
FROM Customers c
CROSS APPLY dbo.GetCustomerMonthlyStats(c.CustomerID) f;
```

### APPLY to Unpivot Columns
```sql
-- Transform columns to rows
SELECT ID, AttributeName, AttributeValue
FROM Products
CROSS APPLY (
    VALUES
        ('Color', Color),
        ('Size', Size),
        ('Weight', CAST(Weight AS VARCHAR))
) AS Unpivoted(AttributeName, AttributeValue)
WHERE AttributeValue IS NOT NULL;
```

## MERGE Statement

### Basic Upsert
```sql
MERGE INTO TargetTable AS t
USING SourceTable AS s
ON t.ID = s.ID
WHEN MATCHED THEN
    UPDATE SET t.Name = s.Name, t.Value = s.Value, t.UpdatedAt = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
    INSERT (ID, Name, Value, CreatedAt)
    VALUES (s.ID, s.Name, s.Value, GETDATE())
WHEN NOT MATCHED BY SOURCE THEN
    DELETE
OUTPUT $action, inserted.*, deleted.*;
```

### MERGE with Conditions
```sql
MERGE INTO Products AS t
USING StagingProducts AS s
ON t.ProductID = s.ProductID
WHEN MATCHED AND s.Price <> t.Price THEN
    UPDATE SET t.Price = s.Price, t.LastModified = GETDATE()
WHEN MATCHED AND s.Discontinued = 1 THEN
    DELETE
WHEN NOT MATCHED THEN
    INSERT (ProductID, Name, Price) VALUES (s.ProductID, s.Name, s.Price);
```

## OUTPUT Clause

### Capture Inserted Rows
```sql
DECLARE @InsertedRows TABLE (ID INT, Name VARCHAR(100));

INSERT INTO Customers (Name, Email)
OUTPUT inserted.CustomerID, inserted.Name INTO @InsertedRows
VALUES ('John', '[email protected]'), ('Jane', '[email protected]');

SELECT * FROM @InsertedRows;
```

### Capture Updated Rows (Before and After)
```sql
UPDATE Products
SET Price = Price * 1.1
OUTPUT deleted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice
WHERE Category = 'Electronics';
```

### Capture Deleted Rows
```sql
DELETE FROM ExpiredOrders
OUTPUT deleted.*
INTO OrderArchive
WHERE ExpiryDate < DATEADD(year, -1, GETDATE());
```

## Advanced Grouping

### ROLLUP (Hierarchical Subtotals)
```sql
SELECT
    COALESCE(Region, 'Total') AS Region,
    COALESCE(Product, 'All Products') AS Product,
    SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY ROLLUP (Region, Product);
-- Groups: (Region, Product), (Region), ()
```

### CUBE (All Combinations)
```sql
SELECT Region, Product, SUM(Sales) AS TotalSales
FROM SalesData
GROUP BY CUBE (Region, Product);
-- Groups: (Region, Product), (Region), (Product), ()
```

### GROUPING SETS (Custom Combinations)
```sql
SELECT Region, Product, Year, SUM(Sales)
FROM SalesData
GROUP BY GROUPING SETS (
    (Region, Product),
    (Region, Year),
    (Product),
    ()
);
```

### Identify Grouping Level
```sql
SELECT
    CASE WHEN GROUPING(Region) = 1 THEN 'All' ELSE Region END AS Region,
    CASE WHEN GROUPING(Product) = 1 THEN 'All' ELSE Product END AS Product,
    SUM(Sales) AS TotalSales,
    GROUPING_ID(Region, Product) AS GroupLevel
    -- GroupLevel: 0 = both, 1 = Product rolled up, 2 = Region rolled up, 3 = both
FROM SalesData
GROUP BY ROLLUP (Region, Product);
```

## Temporal Tables (SQL 2016+)

### Create System-Versioned Table
```sql
CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    Name NVARCHAR(100),
    Price DECIMAL(18,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));
```

### Query Historical Data
```sql
-- Point in time
SELECT * FROM Products
FOR SYSTEM_TIME AS OF '2024-01-01 12:00:00';

-- Time range
SELECT * FROM Products
FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-06-30';

-- All history
SELECT * FROM Products
FOR SYSTEM_TIME ALL;
```

## In-Memory OLTP

### Create Memory-Optimized Table
```sql
-- First add filegroup
ALTER DATABASE YourDB
ADD FILEGROUP MemOptFG CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE YourDB
ADD FILE (NAME = 'MemOptFile', FILENAME = 'C:\Data\MemOpt') TO FILEGROUP MemOptFG;

-- Create table
CREATE TABLE OrdersMemOpt (
    OrderID INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT = 1000000),
    CustomerID INT NOT NULL INDEX IX_Customer NONCLUSTERED HASH WITH (BUCKET_COUNT = 100000),
    OrderDate DATETIME2 NOT NULL,
    Amount DECIMAL(18,2) NOT NULL,
    INDEX IX_Date NONCLUSTERED (OrderDate)
) WITH (MEMORY_OPTIMIZED = ON, DURABILITY = SCHEMA_AND_DATA);
```

### Natively Compiled Procedure
```sql
CREATE PROCEDURE InsertOrderFast
    @CustomerID INT,
    @Amount DECIMAL(18,2)
WITH NATIVE_COMPILATION, SCHEMABINDING
AS
BEGIN ATOMIC WITH (TRANSACTION ISOLATION LEVEL = SNAPSHOT, LANGUAGE = N'English')
    INSERT INTO dbo.OrdersMemOpt (OrderID, CustomerID, OrderDate, Amount)
    VALUES (NEXT VALUE FOR dbo.OrderSeq, @CustomerID, SYSDATETIME(), @Amount);
END;
```

## Table-Valued Constructor

### VALUES as Table
```sql
SELECT * FROM (
    VALUES
        (1, 'Apple', 1.50),
        (2, 'Banana', 0.75),
        (3, 'Orange', 2.00)
) AS Products(ID, Name, Price);
```

### Use in MERGE
```sql
MERGE INTO Products AS t
USING (VALUES
    (1, 'Apple', 1.60),
    (2, 'Banana', 0.80)
) AS s(ID, Name, Price)
ON t.ID = s.ID
WHEN MATCHED THEN UPDATE SET Price = s.Price
WHEN NOT MATCHED THEN INSERT VALUES (s.ID, s.Name, s.Price);
```