MS SQL Server expert for writing optimized queries, selecting appropriate data types, and leveraging core SQL Server features. Use for: query optimization, performance tuning, T-SQL development, schema design, index strategy, stored procedures, functions, and advanced SQL Server features.
Expert assistance for Microsoft SQL Server development, focusing on performance, best practices, and leveraging SQL Server's core features.
When analyzing or writing queries:
Optimization checklist:
Choose the most efficient and appropriate data types:
Numeric Types:
TINYINT (0-255), SMALLINT (-32K to 32K), INT (-2B to 2B), BIGINT (large numbers)DECIMAL(p,s) / NUMERIC(p,s) for exact precision (money, quantities)FLOAT / REAL only when approximate values are acceptableMONEY / SMALLMONEY for currency (consider DECIMAL(19,4) for precision)String Types:
VARCHAR(n) for variable-length ASCII (max 8000)NVARCHAR(n) for Unicode (max 4000)VARCHAR(MAX) / NVARCHAR(MAX) for large text (>8000 bytes)CHAR(n) / NCHAR(n) for fixed-length (use sparingly)Date/Time Types:
DATE for dates only (3 bytes)DATETIME2(n) for date+time with precision (6-8 bytes, preferred over DATETIME)TIME(n) for time onlyDATETIMEOFFSET for timezone-aware timestampsDATETIME and SMALLDATETIME for new developmentOther Types:
BIT for boolean flagsUNIQUEIDENTIFIER for GUIDs (consider impact on indexing)VARBINARY(n) for binary dataXML for XML documents with schema validationJSON (via NVARCHAR with JSON functions in SQL Server 2016+)Best Practices:
-- Recursive CTE for hierarchical data
WITH EmployeeHierarchy AS (
SELECT EmployeeID, ManagerID, Name, 1 AS Level
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, e.ManagerID, e.Name, eh.Level + 1
FROM Employees e
INNER JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;
-- Running totals, ranking, and partitioned aggregates
SELECT
OrderID,
CustomerID,
OrderDate,
Amount,
SUM(Amount) OVER (PARTITION BY CustomerID ORDER BY OrderDate) AS RunningTotal,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RN,
RANK() OVER (ORDER BY Amount DESC) AS AmountRank
FROM Orders;
CREATE PROCEDURE usp_GetCustomerOrders
@CustomerID INT,
@StartDate DATE = NULL,
@EndDate DATE = NULL
AS
BEGIN
SET NOCOUNT ON;
SELECT OrderID, OrderDate, TotalAmount
FROM Orders
WHERE CustomerID = @CustomerID
AND (@StartDate IS NULL OR OrderDate >= @StartDate)
AND (@EndDate IS NULL OR OrderDate <= @EndDate)
ORDER BY OrderDate DESC;
END;
-- Upsert operation
MERGE INTO TargetTable AS target
USING SourceTable AS source
ON target.ID = source.ID
WHEN MATCHED THEN
UPDATE SET target.Value = source.Value
WHEN NOT MATCHED BY TARGET THEN
INSERT (ID, Value) VALUES (source.ID, source.Value)
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
Clustered Index:
Non-Clustered Index:
Index Design Principles:
-- Covering index with INCLUDE
CREATE NONCLUSTERED INDEX IX_Orders_CustomerDate
ON Orders (CustomerID, OrderDate)
INCLUDE (TotalAmount, Status);
-- Filtered index for active records
CREATE NONCLUSTERED INDEX IX_Orders_Active
ON Orders (OrderDate)
WHERE Status = 'Active';
Query Writing:
EXISTS instead of COUNT(*) > 0IN for small lists, EXISTS for subqueriesNOLOCK hint carefully (dirty reads)WITH (NOEXPAND) for indexed viewsTransaction Management:
TRY...CATCH for error handlingBatch Operations:
-- Process in batches to avoid lock escalation
DECLARE @BatchSize INT = 1000;
WHILE 1 = 1
BEGIN
DELETE TOP (@BatchSize)
FROM LargeTable
WHERE Status = 'Archived';
IF @@ROWCOUNT < @BatchSize BREAK;
WAITFOR DELAY '00:00:01'; -- Give other queries a chance
END;
Temporal Tables (System-Versioned):
CREATE TABLE Employee
(
EmployeeID INT PRIMARY KEY,
Name NVARCHAR(100),
Salary DECIMAL(10,2),
ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
ValidTo DATETIME2 GENERATED ALWAYS AS ROW END,
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON);
JSON Support:
-- Query JSON data
SELECT
JSON_VALUE(JsonColumn, '$.name') AS Name,
JSON_QUERY(JsonColumn, '$.address') AS Address
FROM Documents
WHERE JSON_VALUE(JsonColumn, '$.status') = 'active';
Dynamic SQL:
-- Parameterized dynamic SQL to prevent injection
DECLARE @SQL NVARCHAR(MAX);
DECLARE @TableName NVARCHAR(128) = 'Orders';
SET @SQL = N'SELECT * FROM ' + QUOTENAME(@TableName) + N' WHERE OrderDate > @Date';
EXEC sp_executesql @SQL, N'@Date DATE', @Date = '2024-01-01';
SET STATISTICS IO, TIME ON)UPDATE STATISTICS)VARCHAR for English-only, ASCII data (1 byte per char)NVARCHAR for international characters, Unicode (2 bytes per char)NVARCHAR(100) uses 2x space of VARCHAR(100)Use JOINs when:
Use Subqueries when:
Use CTEs when:
Batch operations to avoid:
Use TOP with WHILE loop or partition switching for very large operations.
CREATE PROCEDURE usp_SafeUpdate
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION;
-- Your operations here
UPDATE Table1 SET Status = 'Processed' WHERE ID = 1;
INSERT INTO Table2 (Data) VALUES ('Value');
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Log error
DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
DECLARE @ErrorState INT = ERROR_STATE();
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH;
END;
When providing SQL Server solutions:
For additional guidance, consult: