SQL Knowledge

SQL Server Expert

Expert assistance for Microsoft SQL Server and T-SQL development with live documentation verification.

Quick Reference

Anti-Patterns to Catch

-- Non-SARGable (BAD)
WHERE YEAR(date_column) = 2024
-- SARGable (GOOD)
WHERE date_column >= '2024-01-01' AND date_column < '2025-01-01'

-- Implicit conversion (BAD)
WHERE nvarchar_column = @varchar_param
-- Type match (GOOD)
WHERE nvarchar_column = @nvarchar_param

Error Handling Template

BEGIN TRY
    BEGIN TRANSACTION;
    -- operations
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
    THROW;
END CATCH;

Version-Specific Features

Workflow

1. Query Development

Follow T-SQL best practices:

• Parameterized queries with sp_executesql for dynamic SQL
• Appropriate data types matching column definitions
• SARGable predicates for index utilization
• TRY...CATCH with proper transaction handling

See references/patterns.md for query templates.

2. Performance Optimization

Analyze and optimize query performance:

• Execution plan analysis for operator costs
• Index recommendations from missing index DMVs
• Parameter sniffing detection and solutions
• Query Store for regression analysis

See references/performance.md for tuning techniques.

3. Security Implementation

Protect against SQL injection and enforce least privilege:

• Always use sp_executesql with parameters
• QUOTENAME for dynamic object names
• Row-level security for multi-tenant
• Dynamic data masking for sensitive columns

See references/security.md for security patterns.

Live Verification

You MUST verify T-SQL syntax against official Microsoft documentation when accuracy is critical. Do not rely solely on training data for function syntax or statement options.

Tools to use:

• WebFetch: Retrieve raw markdown from Microsoft's GitHub docs
• WebSearch: Find correct documentation URLs

Documentation Sources

When Verification is Required

Step 1: Verify Function/Statement Syntax

When providing T-SQL syntax, use WebFetch to verify:

WebFetch call:

• URL: https://raw.githubusercontent.com/MicrosoftDocs/sql-docs/live/docs/t-sql/functions/{function-name}.md
• Prompt: Extract the complete function syntax, required parameters, return type, and SQL Server version requirements

Example for STRING_AGG:

• URL: https://raw.githubusercontent.com/MicrosoftDocs/sql-docs/live/docs/t-sql/functions/string-agg-transact-sql.md

Step 2: WebSearch Fallback

If the exact URL is unknown, use WebSearch:

WebSearch call:

• Query: {function-name} T-SQL site:learn.microsoft.com/en-us/sql

Then use WebFetch on the returned URL to get the markdown content.

Step 3: State Uncertainty

If verification fails, tell the user:

"I wasn't able to verify this syntax against live documentation. Please confirm by checking: https://learn.microsoft.com/en-us/sql/t-sql/functions/{function-name}"

Verification Examples

Good (verified with live data):

"STRING_AGG (SQL Server 2017+) concatenates string values with a separator: STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY ...)] Verified against Microsoft docs."

Bad (unverified claim):

"Use CONCAT_WS with 5 parameters..." ← May have incorrect syntax!

Key Patterns

Pagination

-- Offset-fetch (SQL Server 2012+)
SELECT columns FROM table
ORDER BY sort_column
OFFSET @PageSize * (@PageNumber - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY;

Running Totals

SELECT column, amount,
    SUM(amount) OVER (ORDER BY date_column ROWS UNBOUNDED PRECEDING) AS running_total
FROM table;

Safe Dynamic SQL

DECLARE @sql NVARCHAR(MAX) = N'SELECT * FROM Users WHERE Name = @Name';
EXEC sp_executesql @sql, N'@Name NVARCHAR(100)', @Name = @UserInput;

References

• references/patterns.md - CTEs, pagination, PIVOT, MERGE, window functions, APPLY operators
• references/performance.md - Execution plan analysis, parameter sniffing, Query Store, wait statistics
• references/security.md - SQL injection prevention, dynamic SQL safety, permissions, data masking
• references/data-types.md - Type selection, collation handling, precision/scale, storage optimization
• references/transactions.md - Isolation levels, deadlock prevention, distributed transactions, sagas

Deployment & Configuration

For SQL Server deployment, configuration, and infrastructure-as-code tasks, reference the SqlServerDsc PowerShell DSC module:

• Repository: https://github.com/dsccommunity/SqlServerDsc
• Wiki (resource docs): https://github.com/dsccommunity/SqlServerDsc/wiki

Use WebFetch to retrieve DSC resource documentation:

• README: https://raw.githubusercontent.com/dsccommunity/SqlServerDsc/main/README.md
• Resource wiki page: https://github.com/dsccommunity/SqlServerDsc/wiki/{ResourceName}
• Resource schema: https://raw.githubusercontent.com/dsccommunity/SqlServerDsc/main/source/DSCResources/{ResourceName}/{ResourceName}.schema.mof

When to Reference SqlServerDsc

Documentation Resources

• SQL Server Docs: https://learn.microsoft.com/en-us/sql/
• T-SQL Reference: https://learn.microsoft.com/en-us/sql/t-sql/language-reference
• GitHub Docs (Raw Markdown): https://github.com/MicrosoftDocs/sql-docs
• SqlServerDsc (Deployment): https://github.com/dsccommunity/SqlServerDsc