Skip to main content
skill CX Codex

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

Install into Claude Code · Skills CLI (Vercel) All methods and platforms ↓
npx skills add JosiahSiegel/claude-plugin-marketplace --skill advanced-patterns -a claude-code
Open on GitHub

What 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 MERGE and capture diffs via OUTPUT
  • 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-code

2Manually: put the files in place

~/.claude/skills/advanced-patterns/SKILL.md

Copy the whole skill folder; for one project use .claude/skills/advanced-patterns/

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

Similar skills

All skills for Data and databases →

From the same set96

The whole set →
azure-emulators-2025 azure-emulators-2025 sets up local Azure Docker emulators including Azurite, Cosmos DB, and SQL Server 2025. Useful for integration testing without live cloud subscriptions. CX Codex +1 159installs advanced-features-2025 The advanced-features-2025 skill covers plugin hooks, MCP setup, and repository-level team distribution. Use it when building or configuring Claude Code plugins for a team. CX Codex +1 159installs plugin-master plugin-master guides the architecture, manifest creation, and marketplace publishing for Claude Code plugins and subagents. CX Codex +1 158installs deployment-stacks-2025 The deployment-stacks-2025 skill covers Azure Deployment Stacks, including resource protection, cleanup rules, Bicep templates, and az stack commands. CX Codex +1 157installs microsoft-guide microsoft-guide is a Claude Code skill for finding Microsoft guidance on .NET microservice architecture. It activates for questions about DDD, CQRS, integration events, or deployment. CX Codex +1 157installs terraform-tasks terraform-tasks — a skill for autonomous Terraform work: generates infrastructure code, builds CI/CD pipelines, migrates resources to state, fixes version issues. CX Codex +1 155installs data-cloud-2025 data-cloud-2025 is a Claude Code skill with Salesforce Data Cloud architecture patterns for designing data streams, identity resolution rules, and CRM activation pipelines. CX Codex +1 155installs advanced-array-patterns advanced-array-patterns is a Claude Code skill with Bash array recipes for indexed and associative arrays, mapfile, slicing, and safe iteration under set -u. CX Codex +1 155installs

Project sponsors

Companies that keep this analytics open