Instruction file imported from astenmark/copilot-dotnet-reference (.github/instructions/sql.instructions.md). Copyright stays with the author.

SQL Instructions

These instructions apply to all .sql files in this repository.

Query Style

• Use uppercase for SQL keywords: SELECT, FROM, WHERE, JOIN, ORDER BY
• Qualify all column references with table alias or table name
• Always specify column names explicitly — never use SELECT *
• Place each clause on its own line for readability

Joins and Filtering

• Prefer explicit INNER JOIN and LEFT JOIN over implicit comma-separated joins
• Put join conditions in the ON clause, not the WHERE clause
• Filter on indexed columns where possible — flag unindexed filter columns with a comment

Null Handling

• Use ISNULL(expression, default) for null substitution
• Use IS NULL and IS NOT NULL — never = NULL or <> NULL

Parameters

• Always use named parameters: @ProductId, @CustomerId
• Never concatenate user input into SQL — all variable values must be parameterised

Stored Procedures

• Begin with a comment block describing purpose, parameters, and return value
• Use SET NOCOUNT ON at the start of every stored procedure
• Always include error handling with TRY/CATCH and RAISERROR or THROW
• Use SCOPE_IDENTITY() rather than @@IDENTITY when retrieving inserted IDs

Comments

• Comment any query that is non-obvious or performance-sensitive
• Note any assumptions about data state or index availability