advanced-patterns — Claude Code skill: what it does, how to install
advanced-patterns provides advanced T-SQL query patterns for SQL Server and Azure SQL, covering recursive CTEs, APPLY operators, MERGE, and temporal tables.
MIT 157 installs ★ 66 updated 2026-06-18
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a claude-codeWhat advanced-patterns does
This skill equips your coding agent with structured T-SQL patterns tailored for Microsoft SQL Server and Azure SQL. It includes query recipes for hierarchical traversal using recursive CTEs, duplicate row cleanup, and subquery alternatives via CROSS APPLY and OUTER APPLY.
The catalog also details data modification patterns using MERGE combined with the OUTPUT clause to audit affected rows. For reporting, it supplies multi-dimensional grouping templates using ROLLUP, CUBE, and GROUPING SETS, alongside system-versioned temporal table definitions.
Default recursion limit in T-SQL stops at 100 iterations. The provided templates explicitly configure MAXRECURSION up to 32767 to keep deep hierarchy queries from failing.
What it helps with
- Traverse organizational hierarchies using recursive CTEs
- Retrieve top N records per group with
CROSS APPLY - Execute atomic upsert operations using
MERGEand capture diffs viaOUTPUT - Track row history using system-versioned temporal tables
- Generate subtotal aggregations across dimensions with
GROUPING SETS
Example prompts for the agent
- Write a recursive CTE in T-SQL to navigate an employee manager hierarchy
- Show me how to fetch the latest order per customer using OUTER APPLY
- Draft an upsert query using MERGE that outputs inserted and deleted values into a table variable
How to install advanced-patterns
1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a claude-code2Manually: put the files in place
~/.claude/skills/advanced-patterns/SKILL.mdCopy the whole skill folder; for one project use .claude/skills/advanced-patterns/
1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a codex2Manually: put the files in place
.agents/skills/advanced-patterns/SKILL.mdFor all projects use ~/.agents/skills/
1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a cursor2Manually: put the files in place
.cursor/skills/advanced-patterns/SKILL.mdCursor also reads .agents/skills/ and .claude/skills/
1GitHub CLI
gh skill install JosiahSiegel/claude-plugin-marketplace plugins/tsql-master/skills/advanced-patterns2Manually: put the files in place
.github/skills/advanced-patterns/SKILL.md1Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a gemini-cli2Platform CLI
gemini skills install https://github.com/JosiahSiegel/claude-plugin-marketplace --path plugins/tsql-master/skills/advanced-patternsManually: put the files in place
.devin/skills/advanced-patterns/SKILL.mdThe legacy .windsurf/skills/ path still works
Skills CLI (Vercel)
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a opencodeClaude.ai
Zip the folder and upload it in Settings → Capabilities → Skills
Step by step, with commands for each platform: How to install skills in Claude Code: five ways
Questions about advanced-patterns
How to install advanced-patterns in Claude Code?
Run npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a claude-code in your terminal.
Which database engines are supported?
The patterns target Microsoft SQL Server and Azure SQL. Features like system-versioned temporal tables apply to SQL Server 2016 and later.
What query patterns are included?
It covers recursive CTEs, APPLY operators, MERGE with OUTPUT, PIVOT/UNPIVOT, window functions, temporal tables, In-Memory OLTP, and GROUPING SETS.
Source text
SKILL.md · MIT
---
name: advanced-patterns
description: |
Advanced T-SQL patterns and techniques for SQL Server and Azure SQL.
PROACTIVELY activate for: (1) writing CTEs and recursive queries, (2) APPLY operator (CROSS APPLY, OUTER APPLY), (3) MERGE statement and OUTPUT clause, (4) temporal tables (system-versioned), (5) In-Memory OLTP / memory-optimized tables, (6) advanced grouping (ROLLUP, CUBE, GROUPING SETS), (7) PIVOT and UNPIVOT, (8) ROW_NUMBER/RANK/DENSE_RANK with PARTITION BY, (9) hierarchies via recursive CTE or HierarchyId, (10) JSON_ARRAYAGG and STRING_AGG patterns.
Provides: pattern catalog with examples for each technique, performance tradeoffs, and copy-pasteable templates for common advanced query shapes.
---
# 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);
```
## Sequences
### Create and Use Sequence
```sql
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;
```
© JosiahSiegel · MIT · JosiahSiegel/claude-plugin-marketplace