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

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

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)

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

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)

-- 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)

-- 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

-- 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

-- 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

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

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

DECLARE @InsertedRows TABLE (ID INT, Name VARCHAR(100));

INSERT INTO Customers (Name, Email) OUTPUT inserted.CustomerID, inserted.Name INTO @InsertedRows VALUES ('John', 'john@email.com'), ('Jane', 'jane@email.com');

SELECT * FROM @InsertedRows;

Capture Updated Rows (Before and After)

UPDATE Products SET Price = Price * 1.1 OUTPUT deleted.ProductID, deleted.Price AS OldPrice, inserted.Price AS NewPrice WHERE Category = 'Electronics';

Capture Deleted Rows

DELETE FROM ExpiredOrders OUTPUT deleted.* INTO OrderArchive WHERE ExpiryDate < DATEADD(year, -1, GETDATE());

Advanced Grouping

ROLLUP (Hierarchical Subtotals)

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)

SELECT Region, Product, SUM(Sales) AS TotalSales FROM SalesData GROUP BY CUBE (Region, Product); -- Groups: (Region, Product), (Region), (Product), ()

GROUPING SETS (Custom Combinations)

SELECT Region, Product, Year, SUM(Sales) FROM SalesData GROUP BY GROUPING SETS ( (Region, Product), (Region, Year), (Product), () );

Identify Grouping Level

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

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

-- 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

-- 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

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

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

Use in MERGE

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);

Sequences

Create and Use Sequence

CREATE SEQUENCE OrderSeq AS INT START WITH 1 INCREMENT BY 1;

-- Get next value SELECT NEXT VALUE FOR OrderSeq;

-- Use in INSERT INSERT INTO Orders (OrderID, CustomerID) VALUES (NEXT VALUE FOR OrderSeq, @CustomerID);

-- Use as default ALTER TABLE Orders ADD CONSTRAINT DF_OrderID DEFAULT NEXT VALUE FOR OrderSeq FOR OrderID;