sqlserver-query-patterns

When to activate

• "This query is slow" or performance issues in SQL Server
• Writing or refactoring a Stored Procedure
• Index design or indexing strategy
• Reading or interpreting an execution plan
• Pagination in SQL Server
• Reporting queries with aggregations, CTEs, or window functions

The Optimization Workflow

1. Know the data volume — row counts and table sizes
2. Check existing indexes on all involved tables
3. Write the query using set-based operations
4. Review the estimated execution plan
5. Identify operators above 15% cost
6. Add or adjust indexes if needed
7. Validate actual vs estimated rows

Index Strategy

-- Covering index: include all columns the query needs → eliminates Key Lookup
CREATE NONCLUSTERED INDEX IX_Orders_CustomerId_Covering
ON Orders (CustomerId)
INCLUDE (Status, Total, CreatedAt);

-- Filtered index: for queries that always filter by a known condition
CREATE NONCLUSTERED INDEX IXF_Orders_Pending
ON Orders (CreatedAt)
WHERE Status = 'pending';

Naming: IX_{Table}_{Columns} · Unique: UX_{Table}_{Columns} · Filtered: IXF_{Table}_{Condition}

T-SQL Patterns

-- ✅ EXISTS for existence checks
IF EXISTS (SELECT 1 FROM Orders WHERE CustomerId = @CustomerId AND Status = 'active')

-- ✅ Sargable date range (index-friendly)
WHERE CreatedAt >= '2024-01-01' AND CreatedAt < '2025-01-01'

-- ❌ Functions on indexed columns — breaks index seek
WHERE YEAR(CreatedAt) = 2024

-- ✅ Pagination
SELECT OrderId, Total, Status, CreatedAt
FROM   Orders
WHERE  CustomerId = @CustomerId
ORDER  BY CreatedAt DESC
OFFSET (@Page - 1) * @PageSize ROWS
FETCH  NEXT @PageSize ROWS ONLY;

-- ✅ CTE for readability
WITH RecentOrders AS (
    SELECT CustomerId, COUNT(*) AS OrderCount, SUM(Total) AS TotalSpent
    FROM   Orders
    WHERE  CreatedAt >= DATEADD(MONTH, -6, GETUTCDATE())
    GROUP  BY CustomerId
)
SELECT c.Name, r.OrderCount, r.TotalSpent
FROM   Customers c
JOIN   RecentOrders r ON c.Id = r.CustomerId;

Stored Procedure Template