Teaching GitHub Copilot Your T-SQL Coding Standards
If you’re a DBA or database developer using GitHub Copilot CLI, you’ve probably noticed it generates functional T-SQL — but not necessarily T-SQL that matches your team’s coding standards. After several weeks of working with Copilot on real-world stored procedure development, I’ve built up a comprehensive instructions file that teaches it how I want my code written. I will update this post periodically to ensure it represents my then-current instructions.
Here’s what I’ve learned, and the rules I’ve established.
Where the Instructions Live
GitHub Copilot CLI reads a global instructions file from ~/.copilot/copilot-instructions.md. This is a plain markdown file that persists across sessions. Every time Copilot starts a new conversation, it reads this file and applies the rules to all code it generates.
The beauty of this approach is that rules accumulate organically. Each time Copilot generates something that doesn’t match your standards, you correct it once, add the rule to the instructions file, and never correct it again. You can even ask Copilot to update the global instructions file with specific choices you want it to be aware of.
The instructions I have so far include the following (there is a single code-block at the end you can copy-and-paste into your own file for convenience, or you can take bits and pieces you like).
The Rules
Semicolon Termination
Every T-SQL statement must be terminated with a semicolon. Microsoft has deprecated unterminated statements, and it’s only a matter of time before they start enforcing it. Copilot’s default behavior is to omit semicolons — this rule fixes that immediately.
|
1 2 3 4 5 6 7 8 |
/* Good */ SET NOCOUNT ON; DECLARE @id int; /* Bad */ SET NOCOUNT ON DECLARE @id int |
Lowercase Data Types
Data types should always be lowercase. This is a stylistic choice, but it’s consistent with how SQL Server displays them in metadata views and INFORMATION_SCHEMA.
|
1 2 3 4 5 6 7 8 9 10 |
/* Good */ DECLARE @name nvarchar(128); DECLARE @count int; DECLARE @content varbinary(max); /* Bad */ DECLARE @name NVARCHAR(128); DECLARE @count INT; DECLARE @content VARBINARY(MAX); |
Always Specify varchar/nvarchar Size
Never use bare varchar or nvarchar without an explicit length. When converting an int to a string, use varchar(11) (enough for the full range including the negative sign). This prevents the silent truncation to varchar(1) or varchar(30) that catches people off guard.
|
1 2 3 4 5 6 7 8 |
/* Good */ DECLARE @msg nvarchar(2048); CONVERT(nvarchar(11), @error_number, 0) /* Bad */ DECLARE @msg nvarchar; CONVERT(nvarchar, @error_number) |
Unicode String Prefix
Use the N'' prefix for all string literals assigned to or compared with nvarchar variables or columns. Without it, you risk silent data loss for any characters outside the code page.
|
1 2 3 4 5 6 |
/* Good */ SET @msg = N'Could not locate the record.'; /* Bad */ SET @msg = 'Could not locate the record.'; |
COALESCE Over ISNULL
Use COALESCE() instead of ISNULL(). It’s ANSI standard, supports multiple arguments, and doesn’t silently constrain the return type to the first argument’s type the way ISNULL does.
|
1 2 3 4 5 6 |
/* Good */ SET @formatted_message = N'Error: ' + COALESCE(@error_message, N'Unknown'); /* Bad */ SET @formatted_message = N'Error: ' + ISNULL(@error_message, N'Unknown'); |
CONVERT Over CAST
Use CONVERT() instead of CAST(), and include an explicit style parameter where it makes sense. CONVERT is more flexible, and the style parameter makes date/time formatting intentions explicit rather than relying on server defaults.
|
1 2 3 4 5 6 7 8 |
/* Good */ CONVERT(nvarchar(11), @some_id, 0) CONVERT(varchar(10), @some_date, 23) /* ISO 8601: yyyy-mm-dd */ /* Bad */ CAST(@some_id AS NVARCHAR) CAST(@some_date AS VARCHAR(10)) |
COUNT(1) Over COUNT(*)
Use COUNT(1) instead of COUNT(*). While modern query optimizers treat them identically, COUNT(1) makes the intent explicit — you’re counting rows, not evaluating columns.
|
1 2 3 4 5 6 |
/* Good */ SELECT COUNT(1) FROM [dbo].[orders]; /* Bad */ SELECT COUNT(*) FROM [dbo].[orders]; |
Square Brackets on All Identifiers
Every column name, table name, schema name, and object name gets [square brackets]. No exceptions. This prevents collisions with reserved words and makes it immediately clear what’s an identifier versus a keyword.
|
1 2 3 4 5 6 7 8 9 |
/* Good */ SELECT [o].[order_id] , [o].[created_date] FROM [dbo].[orders] [o] WHERE [o].[status] = 'active'; |
BEGIN…END on All IF Statements
Every IF statement must use BEGIN...END, even for single-line bodies. This prevents the class of bugs where someone adds a second line to an IF block and doesn’t realize it’s not actually inside the conditional.
|
1 2 3 4 5 6 7 8 9 10 |
/* Good */ IF @count = 0 BEGIN SET @msg = N'No records found.'; END; /* Bad */ IF @count = 0 SET @msg = N'No records found.'; |
SQL Comment Style
SQL comments shall always follow the /* .... */ pattern; never use the -- style. If existing code uses -- style comments, convert them to /* ... */ when modifying the file.
|
1 2 3 4 5 6 |
/* Good - block comment style */ /* Check if the record exists before attempting the delete */ /* Bad - line comment style */ -- Check if the record exists before attempting the delete |
Clause-Per-Line Formatting
Each major SQL clause starts on its own line, with arguments indented beneath it. This makes diffs cleaner and makes it easy to scan the structure of a query.
|
1 2 3 4 5 6 7 8 9 10 |
SELECT [t].[terminal_id] , [t].[code] FROM [dbo].[terminal] [t] WHERE [t].[active] = 1 ORDER BY [t].[code]; |
Stored Procedure Parameters
Always wrap the parameter list in parentheses on separate lines:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE PROCEDURE [dbo].[lookup_widget] ( @active_only bit = 0 , @category_id int = NULL ) AS BEGIN ... END; |
Variable Alignment
When declaring or setting multiple variables in a block, align the variable names, data types, and = signs into neat columns using spaces. Never use tabs.
|
1 2 3 4 5 6 7 8 9 10 |
DECLARE @error_number int; DECLARE @error_severity int; DECLARE @error_procedure nvarchar(128); DECLARE @error_message nvarchar(4000); SET @error_number = ERROR_NUMBER(); SET @error_severity = ERROR_SEVERITY(); SET @error_procedure = ERROR_PROCEDURE(); SET @error_message = ERROR_MESSAGE(); |
CREATE TABLE Formatting
Opening parenthesis on its own line, each column indented, closing parenthesis on its own line:
|
1 2 3 4 5 6 |
CREATE TABLE #results ( [item_id] int NOT NULL , [item_name] varchar(50) NOT NULL ); |
Subqueries in Expressions
Expand subqueries onto multiple lines, just like standalone statements:
|
1 2 3 4 5 6 7 8 9 10 11 |
IF ( SELECT COUNT(1) FROM #deleted_items ) = 1 BEGIN ... END; |
No Boilerplate or Commented-Out Code
Don’t leave behind template comments, copyright blocks, or commented-out lines like -- SELECT @sp_return. Keep the code clean. If it’s not executing, it shouldn’t be in the file.
Behavioral Rules
Context-Sensitive Transaction Handling
For databases where snapshot isolation is enabled, stored procedures should check @@TRANCOUNT at entry. If no transaction exists, open one and track ownership with a flag. On success, only COMMIT if we own the transaction. On error, only ROLLBACK if we own the transaction and @@TRANCOUNT > 0.
This keeps the proc safe when called standalone while being a good citizen inside a caller’s transaction (e.g., when an application wraps the call in a TransactionScope).
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
DECLARE @owns_transaction bit = 0; IF @@TRANCOUNT = 0 BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION; END; BEGIN TRY /* ... work here ... */ IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; /* ... error handling here ... */ END CATCH; |
SNAPSHOT Isolation Over NOLOCK
Prefer SET TRANSACTION ISOLATION LEVEL SNAPSHOT over WITH (NOLOCK) in stored procedures. NOLOCK gives you dirty reads, phantom reads, and can even return duplicate rows or skip rows entirely during page splits. SNAPSHOT isolation gives you a consistent point-in-time view without any of those hazards.
There’s a critical caveat: SQL Server will abort and roll back the entire transaction if you attempt to switch to SNAPSHOT isolation inside an active transaction that started under a different isolation level. Per Microsoft’s documentation:
“With one exception, you can switch from one isolation level to another at any time during a transaction. The exception occurs when changing from any isolation level to SNAPSHOT isolation. Doing this causes the transaction to fail and roll back.”
This means you must guard the change with @@TRANCOUNT = 0:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
DECLARE @isolation_changed bit = 0; IF @@TRANCOUNT = 0 BEGIN SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET @isolation_changed = 1; END; /* ... queries here ... */ IF @isolation_changed = 1 BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED; END; |
Also worth noting: when SET TRANSACTION ISOLATION LEVEL is issued inside a stored procedure, the isolation level automatically reverts to the caller’s level when the proc returns. The reset to READ COMMITTED at the end is a defensive measure for clarity, not strictly required.
Caution for write procedures: SNAPSHOT isolation is not universally safe for procedures that write data. Procedures that reference key-tables, counter-tables, sequence-tables, or any pattern where a column is updated using ROWLOCK, UPDLOCK, or other specific locking semantics may be negatively affected. SNAPSHOT isolation can mask concurrent updates and introduce race conditions in these scenarios. Do not impose SNAPSHOT isolation on write procedures without in-depth analysis and testing.
OUTPUT Clause Over @@ROWCOUNT
Prefer using the OUTPUT clause with a temp table to capture affected rows, rather than relying on @@ROWCOUNT. The OUTPUT clause gives you the actual data that was modified, which is far more useful for logging, auditing, and conditional logic.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
DELETE [fi] OUTPUT [deleted].[item_id] INTO #deleted_items ( [item_id] ) FROM [dbo].[folder_item] AS [fi] WHERE [fi].[item_id] = @item_id; |
The Payoff
After establishing these rules, every stored procedure Copilot generates comes out looking like it was written by someone who actually cares about the codebase. The formatting is consistent, the patterns are defensive, and the code is clean enough to pass a code review without a round-trip.
The key insight is that Copilot’s instructions file is a living document. Every time you see something you don’t like in the generated code, don’t just fix it — add the rule. Over a few weeks of active development, you’ll build up a comprehensive style guide that’s enforced automatically.
The instructions file doesn’t just help Copilot — it helps you crystallize what your standards actually are. Half of the rules in my file were things I’d been doing instinctively for years but had never written down. The act of writing them as explicit instructions forced me to think about why I do things a certain way, and in a few cases, I refined my own practices in the process.
Let me know in the comments if you’ve built up your own Copilot instructions for T-SQL, or if you have rules you think I should add to mine.
The Full Instructions File
Here’s what a complete ~/.copilot/copilot-instructions.md looks like, incorporating all of the rules above. Copy it, tweak it to match your team’s preferences, and start building on it.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 |
# Global Copilot CLI Instructions — T-SQL Standards ## SQL Comment Style SQL comments shall always follow the `/* .... */` pattern; never use the `--` style. If you see `--` comments when asked to update a SQL file, convert them to `/* ... */`. ## SQL Join Style NEVER use deprecated implicit/comma joins (`FROM table1, table2 WHERE table1.key = table2.key`). Always use explicit ANSI JOIN syntax. ## SQL Formatting Style When writing T-SQL code, follow these formatting conventions: - **Semicolon termination** — every T-SQL statement must be terminated with a semicolon. - **Data types** should always be lowercase (`int`, `nvarchar`, `varbinary`, `datetime`). - **Always specify a size** for `varchar` and `nvarchar` — never use bare `varchar` or `nvarchar` without an explicit length (e.g., `varchar(50)`, `nvarchar(128)`). Use `varchar(11)` when converting an `int` to a string. - **Use `N''` prefix** for string literals assigned to or compared with `nvarchar` columns/variables. - **Use `COALESCE()`** instead of `ISNULL()`. - **Use `CONVERT()`** instead of `CAST()`. Include an explicit style parameter where sensible. - **Use `COUNT(1)`** instead of `COUNT(*)`. - **Square brackets** on all column, table, schema, and object names. - **`IF` statements** must always use `BEGIN...END`, even for single-line bodies. - **No boilerplate or commented-out code** — if it's not executing, remove it. - **Indentation**: Always use spaces, never tabs. - **Clause-per-line formatting**: Each major SQL clause (`SELECT`, `FROM`, `WHERE`, `SET`, `UPDATE`, `DELETE`, `OUTPUT`, `INTO`) starts on its own line, with arguments indented beneath. - **Stored procedure parameters**: Always wrap in parentheses on separate lines: ```sql CREATE PROCEDURE [dbo].[my_proc] ( @param1 int , @param2 varchar(50) = NULL ) AS ``` - **`CREATE TABLE`**: Opening parenthesis on its own line, columns indented, closing on its own line: ```sql CREATE TABLE #results ( [item_id] int NOT NULL , [item_name] varchar(50) NOT NULL ); ``` - **`OUTPUT ... INTO`**: Each clause keyword on its own line, target columns explicitly listed: ```sql DELETE [fi] OUTPUT [deleted].[item_id] INTO #deleted_items ( [item_id] ) FROM [dbo].[folder_item] AS [fi] WHERE [fi].[item_id] = @item_id; ``` - **Variable alignment**: Align variable names, data types, and `=` signs into neat columns: ```sql DECLARE @error_number int; DECLARE @error_severity int; DECLARE @error_procedure nvarchar(128); SET @error_number = ERROR_NUMBER(); SET @error_severity = ERROR_SEVERITY(); SET @error_procedure = ERROR_PROCEDURE(); ``` - **Subqueries in expressions**: Expand onto multiple lines: ```sql IF ( SELECT COUNT(1) FROM #deleted_items ) = 1 ``` - **Context-sensitive transaction handling** — check `@@TRANCOUNT` at entry. If no transaction exists, open one and track ownership. Only `COMMIT`/`ROLLBACK` if we own it: ```sql DECLARE @owns_transaction bit = 0; IF @@TRANCOUNT = 0 BEGIN SET @owns_transaction = 1; BEGIN TRANSACTION; END; BEGIN TRY /* ... work here ... */ IF @owns_transaction = 1 BEGIN COMMIT TRANSACTION; END; END TRY BEGIN CATCH IF @owns_transaction = 1 AND @@TRANCOUNT > 0 BEGIN ROLLBACK TRANSACTION; END; /* ... error handling here ... */ END CATCH; ``` - **SNAPSHOT isolation over NOLOCK** — Prefer `SET TRANSACTION ISOLATION LEVEL SNAPSHOT` over `WITH (NOLOCK)`. Guard with `@@TRANCOUNT = 0` because SQL Server aborts the transaction if you switch to SNAPSHOT inside an active transaction. See [SET TRANSACTION ISOLATION LEVEL](https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql) for details. **Caution for write procedures:** SNAPSHOT isolation is not universally safe for procs that update key-tables, counter-tables, or sequence-tables with `ROWLOCK`/`UPDLOCK` semantics. Do not impose SNAPSHOT on write procs without in-depth testing. ```sql DECLARE @isolation_changed bit = 0; IF @@TRANCOUNT = 0 BEGIN SET TRANSACTION ISOLATION LEVEL SNAPSHOT; SET @isolation_changed = 1; END; /* ... queries here ... */ IF @isolation_changed = 1 BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED; END; ``` |