SQL Server Best Practices

SQL Server Best Practices

This skill covers both on-premises SQL Server (2016+) and Azure SQL Database / Managed Instance. When differences exist between the two, they are called out in the relevant section.

How to Use This Skill

Identify which domain(s) the user's request falls into, then read the corresponding reference file(s) before responding. For requests that touch multiple domains (e.g., "review this stored proc and its indexes"), read all relevant files.

Quick Principles (always apply)

These apply regardless of domain — internalize them before reading any reference file:

1. Correctness first. A fast query that returns wrong results is worse than a slow correct one.
2. Set-based over row-by-row. SQL Server is optimized for set operations; cursors and loops are last resorts.
3. Explicit over implicit. Always specify schema (dbo.TableName), always use SET NOCOUNT ON in procs, always define column lists in INSERT.
4. Test with realistic data volumes. A query that looks fine on 1,000 rows can fall apart at 10 million.
5. Don't guess — measure. Use execution plans, SET STATISTICS IO ON, and sp_BlitzCache rather than intuiting performance.
6. Least surprise. Name things clearly. A reader unfamiliar with the system should be able to understand what a table, column, or proc does from its name alone.

Always Flag — Regardless of What Was Asked

When reviewing any SQL, DDL, or migration code, always call out the following issues unprompted — even if the user only asked about something else:

• ON DELETE CASCADE — warn that it will silently delete child rows without confirmation or audit trail. Recommend removing it and handling deletes explicitly in a stored procedure instead.
• ON DELETE SET NULL — warn that it silently breaks FK relationships. Recommend explicit handling.
• NOLOCK / WITH (NOLOCK) — warn about dirty reads and phantom rows. Recommend enabling RCSI at the database level instead.
• EXEC(@sql) with string concatenation — flag as SQL injection risk. Recommend sp_executesql with parameters.

Keep the callout brief and constructive — one or two sentences, then move on to what the user actually asked.

Pre-Flight Questions

Before writing any DDL or stored procedures, ask the questions below if the answers aren't already clear from context. Ask them together upfront — don't generate code first and ask after.

For any stored procedure or T-SQL code:

• Naming convention — ask once per conversation:

  "What prefix do you use for stored procedures — usp_ (common default), none, or something else?" Note: avoid sp_ — SQL Server searches the master database first for any proc starting with sp_, which causes unnecessary overhead and confusion with system procedures.

For schema / table design or new database setup:

• Primary key type — ask once per conversation:

  "Do you prefer INT IDENTITY (auto-increment integers: 1, 2, 3…) or GUID (UNIQUEIDENTIFIER — globally unique, better for distributed systems)?"

• Soft deletes — ask when designing tables that represent entities users might "delete":

  "Do you want soft deletes (an IsDeleted BIT flag + DeletedAt DATETIME2 column so rows are hidden but not removed) or hard deletes (physically remove the row)?"

• Audit trail — ask for any new schema:

  "Do you want standard audit columns on each table — CreatedAt DATETIME2, UpdatedAt DATETIME2, and optionally CreatedBy / UpdatedBy?"

If the user has already answered any of these earlier in the conversation, use that answer — don't ask again.

Determining Which Reference(s) to Read

Ask yourself what the user is trying to accomplish:

• "Write/fix/review a query or stored proc" → query-patterns.md
• "Why is this slow / add an index / review query plan" → indexing.md + maintenance.md (sp_BlitzCache/sp_BlitzFirst live there) + possibly query-patterns.md for SARGability
• "Design a table / choose a data type / naming question" → schema-design.md
• "Set up permissions / prevent SQL injection / audit access" → security.md
• "Set up maintenance jobs / check database health / configure backups" → maintenance.md
• "Review this migration or DDL script" → schema-design.md + security.md
• "General code review of SQL" → read all five, prioritize issues by severity