# Sqlserver Query Patterns

> >

- **Type:** Skill
- **Install:** `agentstack add skill-iambrzdev-enterprise-agent-skills-sqlserver-query-patterns`
- **Verified:** Yes — security-reviewed for prompt injection and unsafe behavior
- **Seller:** [iamBrzDev](https://agentstack.voostack.com/s/iambrzdev)
- **Installs:** 0
- **Category:** [Agent Skills](https://agentstack.voostack.com/c/agent-skills)
- **Latest version:** 0.1.0
- **License:** MIT
- **Upstream author:** [iamBrzDev](https://github.com/iamBrzDev)
- **Source:** https://github.com/iamBrzDev/enterprise-agent-skills/tree/main/skills/sqlserver-query-patterns

## Install

```sh
agentstack add skill-iambrzdev-enterprise-agent-skills-sqlserver-query-patterns
```

Requires the [AgentStack CLI](https://agentstack.voostack.com/docs/cli). Works with Claude Code, Cursor, and any MCP-compatible agent.

## About

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

```sql
-- 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

```sql
-- ✅ 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

```sql
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

```sql
-- 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](https://github.com/iamBrzDev)
- **Source:** [iamBrzDev/enterprise-agent-skills](https://github.com/iamBrzDev/enterprise-agent-skills)
- **License:** MIT

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

## Pricing

- **Free** — Free

## Security capabilities

Automated source analysis of v0.1.0 — what this tool can access:

- **Network access:** no
- **Filesystem access:** no
- **Shell / process execution:** no
- **Environment & secrets:** no
- **Dynamic code execution:** no

*"Yes" means the capability is present in the source — more access means more to trust, not that it is unsafe.*


## Versions

- **0.1.0** — security scan: passed — Imported from the upstream source.

## Links

- Listing page: https://agentstack.voostack.com/l/skill-iambrzdev-enterprise-agent-skills-sqlserver-query-patterns
- Seller: https://agentstack.voostack.com/s/iambrzdev
- Browse the marketplace: https://agentstack.voostack.com/browse

---
Listed on AgentStack — the marketplace for AI agent skills and MCP servers. Every listing is security-reviewed. Creators keep 70%.