sql-server

name	sql-server
description	| Use when this capability is needed.

SQL Server - Quick Reference

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: aspnet-core for EF Core + SQL Server patterns.

Common T-SQL Patterns

-- Table creation
CREATE TABLE Users (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) NOT NULL UNIQUE,
    CreatedAt DATETIME2 DEFAULT GETUTCDATE(),
    UpdatedAt DATETIME2 NULL,
    IsActive BIT DEFAULT 1
);

-- Pagination (SQL Server 2012+)
SELECT * FROM Users
ORDER BY Name
OFFSET @Skip ROWS
FETCH NEXT @Take ROWS ONLY;

-- MERGE (upsert)
MERGE INTO Users AS target
USING (SELECT @Email AS Email, @Name AS Name) AS source
ON target.Email = source.Email
WHEN MATCHED THEN
    UPDATE SET Name = source.Name, UpdatedAt = GETUTCDATE()
WHEN NOT MATCHED THEN
    INSERT (Name, Email) VALUES (source.Name, source.Email);

-- CTE (Common Table Expression)
WITH ActiveUsers AS (
    SELECT Id, Name, ROW_NUMBER() OVER (ORDER BY CreatedAt DESC) AS RowNum
    FROM Users WHERE IsActive = 1
)
SELECT * FROM ActiveUsers WHERE RowNum BETWEEN 1 AND 10;

Indexes

-- Clustered (one per table, usually PK)
CREATE CLUSTERED INDEX IX_Users_Id ON Users(Id);

-- Non-clustered
CREATE NONCLUSTERED INDEX IX_Users_Email ON Users(Email);

-- Covering index (includes extra columns)
CREATE NONCLUSTERED INDEX IX_Users_Email_Include
ON Users(Email) INCLUDE (Name, CreatedAt);

-- Filtered index
CREATE NONCLUSTERED INDEX IX_Users_Active
ON Users(Email) WHERE IsActive = 1;

-- Check missing indexes
SELECT * FROM sys.dm_db_missing_index_details;

Stored Procedures

CREATE PROCEDURE sp_GetUsersByStatus
    @IsActive BIT,
    @PageSize INT = 10,
    @PageNumber INT = 1
AS
BEGIN
    SET NOCOUNT ON;

    SELECT Id, Name, Email, CreatedAt
    FROM Users
    WHERE IsActive = @IsActive
    ORDER BY Name
    OFFSET (@PageNumber - 1) * @PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY;
END;

Performance Tips

Tip	Why
Use NVARCHAR over VARCHAR for Unicode	Avoid encoding issues
Add indexes on WHERE/JOIN columns	Speed up queries
Use SET NOCOUNT ON in procedures	Reduce network traffic
Avoid SELECT *	Only fetch needed columns
Use parameterized queries	Prevent SQL injection, plan reuse
Use DATETIME2 over DATETIME	Better precision, smaller storage

Query Performance Analysis

-- Execution plan
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
-- Run your query
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

-- Expensive queries
SELECT TOP 10
    qs.total_elapsed_time / qs.execution_count AS avg_elapsed,
    qs.execution_count,
    SUBSTRING(qt.text, 1, 200) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY avg_elapsed DESC;

Anti-Patterns

Anti-Pattern	Why It's Bad	Correct Approach
SELECT *	Wastes I/O, breaks on schema change	List specific columns
Cursors for row-by-row	Very slow	Use set-based operations
No indexes on FKs	Slow JOINs	Add non-clustered indexes
NOLOCK everywhere	Dirty reads	Use proper isolation levels
String concatenation in SQL	SQL injection	Use parameterized queries

Quick Troubleshooting

Issue	Likely Cause	Solution
Slow query	Missing index	Check execution plan
Deadlocks	Conflicting locks	Check sys.dm_tran_locks
Timeout	Long-running query	Optimize or increase timeout
Truncation error	Column too small	Check NVARCHAR length

---

Source: claude-dev-suite/claude-dev-suite — distributed by TomeVault.