sqlserver-query-patterns

name	sqlserver-query-patterns
description	Optimizes SQL Server queries, designs indexing strategies, writes efficient T-SQL and Stored Procedures, and interprets execution plans. Use when writing queries against SQL Server or Azure SQL, optimizing slow queries, designing indexes, creating or refactoring Stored Procedures, working with execution plans, or building reporting and aggregation queries in enterprise SQL Server environments.
license	MIT
metadata	{"author":"iamBrzDev","version":"1.0"}

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

CREATE OR ALTER PROCEDURE usp_GetOrdersByCustomer
    @CustomerId  INT,
    @PageNumber  INT = 1,
    @PageSize    INT = 20,
    @Status      NVARCHAR(50) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT ON;

    IF @CustomerId IS NULL OR @CustomerId <= 0
        THROW 50001, 'CustomerId must be a positive integer.', 1;

    SELECT  o.OrderId, o.Total, o.Status, o.CreatedAt,
            COUNT(*) OVER() AS TotalCount
    FROM    Orders o
    WHERE   o.CustomerId = @CustomerId
      AND   o.IsDeleted  = 0
      AND   (@Status IS NULL OR o.Status = @Status)
    ORDER   BY o.CreatedAt DESC
    OFFSET  (@PageNumber - 1) * @PageSize ROWS
    FETCH   NEXT @PageSize ROWS ONLY;
END;
GO

Non-negotiables

• ❌ Never SELECT * in stored procedures
• ❌ Never cursors when set-based works
• ❌ Never functions on indexed columns in WHERE
• ❌ Never NOLOCK as performance shortcut
• ✅ Always SET NOCOUNT ON
• ✅ Always SET XACT_ABORT ON in procedures that modify data
• ✅ Use GETUTCDATE() — store UTC, display local in app layer

Quick diagnostics

-- Missing indexes suggested by optimizer
SELECT mid.statement, mid.equality_columns, migs.avg_user_impact
FROM   sys.dm_db_missing_index_details mid
JOIN   sys.dm_db_missing_index_groups mig  ON mid.index_handle = mig.index_handle
JOIN   sys.dm_db_missing_index_group_stats migs ON mig.index_group_handle = migs.group_handle
WHERE  mid.database_id = DB_ID()
ORDER  BY migs.avg_user_impact DESC;

Reference files

• references/indexing-strategy.md — Fragmentación, rebuild vs reorganize, mantenimiento