| name | sqlserver-expert |
| description | Expert in Microsoft SQL Server development and administration. Use when writing T-SQL queries, stored procedures, functions, triggers, optimizing database performance (deadlocks, slow queries, execution plans, index tuning), designing schemas, configuring SQL Server, implementing CDC (Change Data Capture), or integrating SQL Server with .NET Core/C# using Entity Framework Core or Dapper. Also use when user asks "why is my query slow", "how to fix deadlock", "optimize this query", "design this table", or mentions tempdb, query plan, wait stats, or SQL Server error messages. |
SQL Server Expert
Act as DBA and developer expert in Microsoft SQL Server.
Quick Reference
CTEs with Window Functions
WITH RankedData AS (
SELECT Id, Name, Department,
ROW_NUMBER() OVER (PARTITION BY Department ORDER BY HireDate) AS RowNum,
SUM(Salary) OVER (PARTITION BY Department) AS DeptTotal
FROM Employees
)
SELECT * FROM RankedData WHERE RowNum = 1;
MERGE Statement
MERGE INTO Target AS t
USING Source AS s ON t.Id = s.Id
WHEN MATCHED THEN UPDATE SET t.Name = s.Name
WHEN NOT MATCHED THEN INSERT (Id, Name) VALUES (s.Id, s.Name)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
Pagination
SELECT * FROM Orders ORDER BY OrderDate DESC
OFFSET @PageSize * (@PageNumber - 1) ROWS FETCH NEXT @PageSize ROWS ONLY;
Best Practices
Performance
- Avoid
SELECT * - list columns explicitly
- Use appropriate indexes for WHERE/JOIN columns
- Avoid functions on columns in WHERE (not sargable)
- Use
SET NOCOUNT ON in stored procedures
- Use
OPTION (RECOMPILE) for parameter-sensitive queries
Security
- Never concatenate strings - use parameters
- Least privilege for application users
- Use schemas to organize and control access
Detailed References
