tomevault-io 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 ONin 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
- T-SQL Advanced Patterns: See references/tsql-advanced.md
- .NET Core Integration: See references/dotnet-integration.md
- Performance Tuning & Deadlocks: See references/performance.md
- Change Data Capture (CDC): See references/cdc.md
- System Queries & Metadata: See references/system-queries.md
Converted and distributed by TomeVault — claim your Tome and manage your conversions.