sql-server

name sql-server description SQL Server and Azure SQL Database with T-SQL, indexes, stored procedures, and performance optimization patterns. USE WHEN: user mentions "SQL Server", "T-SQL", "MSSQL", "Azure SQL", "SQL Server indexes", "stored procedures", "SQL Server performance" DO NOT USE FOR: PostgreSQL - use `postgresql`, MySQL - use `mysql`, MongoDB - use `mongodb` allowed-tools Read, Grep, Glob, Write, Edit 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