sql-server-safe-tsql — GitHub Copilot rule: safer T-SQL
The sql-server-safe-tsql GitHub Copilot rule sets safeguards for SQL Server scripts. It applies to *.sql and *.ps1 files while writing or reviewing T-SQL.
MIT ★ 41,430 updated 2026-10-23
.github/instructions/sql-server-safe-tsql.instructions.mdWhat sql-server-safe-tsql does
The rule sets a safe pattern for SQL Server changes: transaction handling, @@TRANCOUNT checks, and a dry run as the default. T-SQL writes proceed only with an explicit @DryRun = 0; NULL also keeps the run in dry-run mode. PowerShell mutations require an explicit -Apply switch, which is off by default.
Large changes are split into batches. When rows move to an archive, deletion is limited to keys captured by OUTPUT ... INTO. Scripts should be idempotent, and every UPDATE or DELETE needs a selective WHERE clause. Administrative statements such as BACKUP run outside a user transaction; the CATCH block rethrows errors with THROW. Batch procedures reject caller-owned transactions and enabled IMPLICIT_TRANSACTIONS.
What it helps with
- prevent unintended SQL Server writes with a dry run
- propagate transaction errors instead of swallowing them
- split large data changes into separate batches
- delete archived rows only after capturing their copied keys
- avoid silently replacing existing SQL Server objects
Example prompts for the agent
- Write a T-SQL procedure to archive old orders with a safe dry run.
- Review this SQL Server script for risks in its data changes.
- Create a PowerShell script that changes SQL Server only with an explicit
-Applyswitch.
How to install sql-server-safe-tsql
Manually: put the files in place
.github/instructions/sql-server-safe-tsql.instructions.mdThe applyTo field sets which files it applies to
Manually: put the files in place
.claude/rules/sql-server-safe-tsql.mdThe paths frontmatter field limits the files: move applyTo from the source into paths
AGENTS.md
Codex reads AGENTS.md: paste the rule text there
Manually: put the files in place
.cursor/rules/sql-server-safe-tsql.mdcFrontmatter: description, globs, alwaysApply. Move applyTo from the source into globs, or the rule will not attach on its own
Manually: put the files in place
.devin/rules/sql-server-safe-tsql.mdManually: put the files in place
.clinerules/sql-server-safe-tsql.mdStep by step, with commands for each platform: AGENTS.md vs CLAUDE.md: what they are and which one to use
Questions about sql-server-safe-tsql
How do I install sql-server-safe-tsql in GitHub Copilot?
Add the rule file to your repository at .github/instructions/sql-server-safe-tsql.instructions.md.
What does sql-server-safe-tsql do?
It sets safety rules for T-SQL and PowerShell scripts that interact with SQL Server, covering dry runs, transactions, batched changes, and error handling.
Which files does sql-server-safe-tsql apply to?
The rule applies to **/*.sql and **/*.ps1 files.
Source text
sql-server-safe-tsql.instructions.md · MIT
---
description: 'SQL Server-specific write-safety rules for T-SQL: XACT_ABORT with THROW, @@TRANCOUNT-aware transactions, fail-closed dry runs, batched OUTPUT...INTO archiving, autocommit-only admin statements, and read-only DMV diagnostics.'
applyTo: '**/*.sql, **/*.ps1'
---
# SQL Server Production-Safe T-SQL
Apply these rules when writing or reviewing T-SQL, SQL Server Agent jobs, or PowerShell that runs against SQL Server. They complement general SQL style guidance by focusing on what keeps a script safe to run against production-adjacent systems.
## Script header
Start every script with a comment block that states:
- Purpose (one line)
- `COMPAT:` the minimum SQL Server version (for example `SQL Server 2016 SP1+` for `CREATE OR ALTER`) and whether it works on Azure SQL Managed Instance
- `Perms:` the permissions required (for example `VIEW SERVER STATE`, or `VIEW SERVER PERFORMANCE STATE` on SQL Server 2022+)
- Whether the script is read-only or writes data
If you are not sure a syntax or DMV column exists in the target version, say so instead of guessing.
## Required shape for any write (DML or DDL)
- Begin with `SET NOCOUNT ON; SET XACT_ABORT ON;`.
- Wrap transaction-compatible work in `BEGIN TRY ... BEGIN TRANSACTION ... COMMIT ... END TRY BEGIN CATCH ... END CATCH`.
- Some administration statements cannot run inside a user transaction (for example `CREATE DATABASE`, `ALTER DATABASE`, `DROP DATABASE`, `BACKUP`, `RESTORE`, and `RECONFIGURE`). For these, use an autocommit path instead of the transaction wrapper: keep the same dry-run default and confirmation gate, run each statement on its own inside `TRY ... CATCH` with `THROW;`, check state before and after, and write the operation-specific recovery step (for example the backup to restore from, or the previous configuration value to set back).
- In the `CATCH` block, roll back only if a transaction is open, then `THROW;` so the original error number and line reach the caller and Agent job history. Do not swallow errors.
- In single-transaction procedures that may be called inside an outer transaction, check `@@TRANCOUNT` on entry, before any read, and only begin and commit a transaction when it is `0`. If it is `0` and `IMPLICIT_TRANSACTIONS` is ON (`@@OPTIONS & 2`), reject the call before `BEGIN TRANSACTION`, because `BEGIN TRANSACTION` then opens a second level and the single `COMMIT` leaves the write uncommitted with its locks held (for example `IF @@TRANCOUNT = 0 AND (@@OPTIONS & 2) = 2 THROW 50000, N'Run with IMPLICIT_TRANSACTIONS OFF; this procedure owns its transaction.', 1;`). Procedures that commit in batches must refuse to run inside a caller-owned transaction or with `IMPLICIT_TRANSACTIONS ON` (`@@OPTIONS & 2`), because either one keeps every batch's locks and log until an outer commit and lets a later rollback undo earlier batches.
- Default to a dry run that fails closed. In T-SQL, use a `@DryRun bit = 1` parameter and treat anything other than an explicit `0`, including `NULL`, as a dry run (`IF @DryRun IS NULL OR @DryRun <> 0`). The parameter default only covers omitted arguments, so never test `IF @DryRun = 1` alone. The dry run prints or returns what would change (for example a row count) and changes nothing.
- In PowerShell, `SupportsShouldProcess` and `-WhatIf` alone do not make dry run the default. Require an explicit `-Apply` switch that is off by default, and wrap every mutation in `if ($Apply -and $PSCmdlet.ShouldProcess($target, $action)) { ... }`. Without `-Apply`, report what would change and exit.
- Make scripts idempotent: `CREATE OR ALTER`, `IF OBJECT_ID(...) IS NULL`, `IF NOT EXISTS`. Never silently overwrite an existing Agent job, linked server, alert, login, or user; detect it and stop or require an explicit flag.
- Every `UPDATE` and `DELETE` has a selective `WHERE` clause. Batch large changes with a `TOP (N)` loop, one short transaction per batch, and an accumulated row count.
- When moving rows (archive then delete), capture each batch's keys into a freshly cleared key set with `OUTPUT ... INTO`, and delete only the rows proven to be copied in that batch.
- Validate parameters at the top of procedures and return a documented non-zero return code on invalid input.
### Procedure template
```sql
CREATE OR ALTER PROCEDURE dbo.usp_ArchiveOrders
@OlderThanDays int,
@BatchSize int = 1000,
@DryRun bit = 1,
@RowsAffected int = NULL OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
IF @OlderThanDays IS NULL OR @OlderThanDays < 1
BEGIN
RAISERROR(N'@OlderThanDays must be >= 1.', 16, 1);
RETURN 1;
END;
IF @BatchSize IS NULL OR @BatchSize NOT BETWEEN 1 AND 50000
BEGIN
RAISERROR(N'@BatchSize must be between 1 and 50000.', 16, 1);
RETURN 2;
END;
-- Batches commit independently, so a caller-owned transaction or
-- IMPLICIT_TRANSACTIONS ON is not allowed.
IF @@TRANCOUNT > 0 OR (@@OPTIONS & 2) = 2
BEGIN
RAISERROR(N'Run outside a caller transaction with IMPLICIT_TRANSACTIONS OFF; this procedure commits per batch.', 16, 1);
RETURN 3;
END;
DECLARE @Cutoff datetime2(0) = DATEADD(DAY, -@OlderThanDays, SYSDATETIME());
SET @RowsAffected = 0;
-- Fail closed: anything other than an explicit 0, including NULL, is a dry run.
IF @DryRun IS NULL OR @DryRun <> 0
BEGIN
SELECT @RowsAffected = COUNT(*) FROM dbo.Orders WHERE Status = 'Closed' AND CreatedAt < @Cutoff;
PRINT CONCAT(N'DRY RUN: would archive ', @RowsAffected, N' rows in batches of ', @BatchSize, N'.');
RETURN 0;
END;
DECLARE @moved TABLE (OrderId int PRIMARY KEY);
DECLARE @BatchRows int = 1;
BEGIN TRY
WHILE @BatchRows > 0
BEGIN
DELETE FROM @moved; -- fresh key set for every batch
BEGIN TRANSACTION;
INSERT dbo.OrdersArchive (OrderId, Status, CreatedAt)
OUTPUT inserted.OrderId INTO @moved (OrderId)
SELECT TOP (@BatchSize) o.OrderId, o.Status, o.CreatedAt
FROM dbo.Orders o WITH (UPDLOCK)
WHERE o.Status = 'Closed' AND o.CreatedAt < @Cutoff
ORDER BY o.OrderId;
DELETE o FROM dbo.Orders o JOIN @moved m ON m.OrderId = o.OrderId;
SET @BatchRows = @@ROWCOUNT;
COMMIT TRANSACTION;
SET @RowsAffected += @BatchRows;
END;
RETURN 0;
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; -- rolls back only the failing batch
THROW;
END CATCH;
END;
```
The default `@BatchSize` is 1000; tune larger values, because a batch that takes thousands of locks can escalate to a table lock that blocks other sessions.
On failure, earlier batches stay committed and the error is rethrown. Because the error is rethrown, SQL Server does not copy `@RowsAffected` back to the caller, so treat it as reliable only on successful completion; to see partial progress, count the archive table or log each batch before rethrowing. A rerun picks up the remaining qualifying rows.
## Ask before generating destructive or risky operations
Stop and ask for explicit confirmation, stating the data-loss or availability impact, before producing any of:
- `DROP`, `TRUNCATE`, `ALTER TABLE ... DROP COLUMN`, or column type narrowing
- `UPDATE` or `DELETE` without a selective predicate
- `KILL`, or `DBCC` repair options
- Enabling `xp_cmdshell`, Ole Automation, or CLR
- Server-level configuration changes (for example `max server memory`)
- Disabling constraints or triggers
Before any data-loss operation, state what should be backed up, where, and how to restore it. Include rollback steps for every change.
## Diagnostics are read-only
- Diagnostic queries against DMVs (blocking chains, wait statistics, expensive queries, orphaned users, linked server checks) must not change state. Label them `READ-ONLY` in the header.
- For blocking, identify head blockers (sessions that block others but are not blocked themselves) and show the chain with `sys.dm_exec_sessions`, `sys.dm_exec_requests`, and `sys.dm_exec_sql_text`. Do not issue `KILL`; if the user decides to, tell them to record the session id and reason, and that `KILL` rolls back the open transaction.
- For wait statistics, capture two snapshots of `sys.dm_os_wait_stats` and report the delta, excluding benign idle waits, rather than reporting cumulative values since startup.
- For orphaned users, restrict detection to instance-authenticated SQL users (`type = 'S'` and `authentication_type = 1` in `sys.database_principals`) whose SID has no matching row in `sys.server_principals`. Exclude system principals (`dbo`, `guest`, `INFORMATION_SCHEMA`, `sys`). Users created `WITHOUT LOGIN`, contained database users, and Windows users intentionally have no matching SQL login and are not orphans.
- Keep orphaned-user detection read-only. Any repair is a separate, dry-run-first script that requires a confirmed user-to-login mapping, uses `ALTER USER ... WITH LOGIN = ...`, never creates logins, and never drops users.
© github · MIT · github/awesome-copilot