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

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: iamBrzDev
• Source: iamBrzDev/enterprise-agent-skills
• License: MIT

Install and usage instructions live in the source repository linked above.