SQL / T-SQL Coding Standards
The full reference for writing SQL against SQL Server with Dapper: safety, naming, formatting, correct queries, NULL handling, joins, transactions, concurrency, indexing, sargability, and migrations. Because we hand-write SQL (no ORM), these standards decide whether our data is correct, isolated, and fast, or quietly wrong. See also Gotchas: SQL, Database Design, and SQL Performance Tuning.
We use Dapper over Microsoft SQL Server, so the SQL we write is exactly what runs. Correctness, safety, and performance are all ours. Two rules are absolute and appear throughout the handbook because they matter so much: every value is a parameter (never concatenated), and every tenant-scoped statement carries its tenant predicate. The rest keeps our SQL consistent, correct, and efficient.
Treat SQL as first-class code: keep it in a data-access layer, review it, keep it consistent, and test it on realistic data.
Safety — non-negotiable
- NeverBuild SQL by concatenating input (
"... WHERE x='" + v + "'"). That is SQL injection. Always pass values as Dapper parameters. - AlwaysInclude the tenant predicate (
WHERE TenantId = @t) on every read, update, and delete of tenant-owned data, taken from the validated context (see Multi-Tenancy). - NeverRun an
UPDATEorDELETEwithout aWHEREagainst live data. One slip rewrites or wipes the whole table. - DoParameterise even "trusted" values. As well as being safer, it lets SQL Server reuse query plans.
- NeverHard-delete regulated records (KYC, AML, audit, SARs). Soft-delete them with a role gate, tenant scope, and an audit entry (see Data Retention & Erasure).
var sql = "SELECT * FROM Accounts WHERE Status='" + status + "'";
conn.Query<Account>(sql);
SQL injection through status, every tenant's rows returned (no tenant predicate), and SELECT * pulling back columns you do not need. Three of the most serious SQL mistakes in two lines.
const string sql = @"SELECT Id, Status, Balance FROM Accounts
WHERE TenantId=@t AND Status=@status";
conn.Query<Account>(sql, new { t = ctx.TenantId, status });
Input cannot change the query, rows are limited to the caller's tenant, and only the needed columns are returned.
Naming & formatting
- DoWrite SQL keywords in upper case (
SELECT,FROM,WHERE,JOIN). Write identifiers in the schema's convention (PascalCase to match our tables). - DoPut one clause per line in multi-line queries. List and align columns, and lay out joins and conditions so the logic is easy to read.
- DoQualify columns with table aliases in multi-table queries. Use short, meaningful aliases, not
a,b,c. - DoName tables and columns clearly and consistently. Keep SQL in a dedicated repository or query layer, not inline in services (see Separation of Concerns, Data Modelling & Persistence).
Projection & SELECT
- AlwaysSelect only the columns you need; never use
SELECT *. This keeps results stable, small, and index-friendly. - DoGive computed or derived columns a clear alias. Keep result shapes stable so the calling code maps them reliably.
- DoAdd
ORDER BYwhen order matters. Without it the result order is undefined and can change between runs. - DoPage large result sets (
OFFSET ... FETCH). Never return an unbounded set to the app (see Performance & Resource Use).
NULL, types & arithmetic
- DoHandle NULL explicitly with
IS NULLandIS NOT NULL. Remember that= NULLis never true, andNOT IN (subquery with NULL)returns nothing. - DoRemember that aggregates ignore NULLs, and that
COUNT(col)differs fromCOUNT(*). UseCOALESCEorISNULLon purpose. - DoUse
DECIMALfor money, and watch out for integer division (5/2 = 2); cast before dividing (see Money & Currency). - DoStore and compare datetimes in UTC. Be explicit about types and avoid implicit conversions, which stop indexes being used.
SELECT 5 / 2 AS Half; -- returns 2, not 2.5
Both operands are integers, so SQL Server does integer division and drops the fraction. On a fee or rate calculation this silently rounds money down.
SELECT CAST(5 AS DECIMAL(18,4)) / 2 AS Half; -- returns 2.5000
Casting one operand to DECIMAL makes the division decimal, so the fraction is kept.
Joins, sets & correctness
- DoUnderstand your joins. An accidental one-to-many turns a sum into an over-count. Aggregate at the right grain and check row counts.
- DoPrefer set-based operations over cursors and row-by-row loops. SQL Server is built for sets and is far faster.
- DoBe explicit about join types (
INNER,LEFT), and put filter conditions in the right place. UsingONinstead ofWHEREchanges outer-join results. - DoUse window functions for ranking and running totals instead of self-joins or cursors, where they are clearer and faster.
Transactions & concurrency
- DoWrap multi-statement changes that must succeed or fail together in one explicit transaction. Keep transactions short and do slow or external work outside them (see Data Integrity & Transactions).
- DoWrite the action and its audit record in the same transaction, so neither can exist without the other (see Audit Trails).
- DoUse optimistic concurrency (a row version) or the right locking for read-modify-write. Choose isolation levels on purpose.
- NeverDo a money- or balance-changing read-modify-write without concurrency control. Last-writer-wins loses money (see Concurrency).
var rows = conn.Execute(@"UPDATE Accounts SET Balance = Balance - @amt, Version = Version + 1
WHERE Id=@id AND TenantId=@t AND Balance >= @amt AND Version=@expected", p, tx);
if (rows == 0) throw new ConcurrencyConflict();
A single atomic statement, guarded by tenant, sufficient funds, and the expected version. A concurrent change is detected, not silently lost, and the balance cannot go negative.
Indexing, sargability & performance
- DoIndex for your real query patterns: filter, join, and order columns, including the tenant key. Consider covering indexes for hot reads (see Database Design).
- DoKeep predicates sargable. Do not wrap indexed columns in functions (
WHERE UPPER(Email)=...) or use leading wildcards; both stop index seeks. - DoRead the actual execution plan to diagnose problems, and test on production-like volumes. What is instant on 100 rows can be an outage on 100 million (see SQL Performance Tuning, Test Data & Environments).
- AvoidOver-indexing (every index slows writes),
SELECT *in views and reports, and N+1 query patterns from the application (see Data Modelling & Persistence). - AvoidUsing a random
uniqueidentifier(NEWID()/Guid.NewGuid()) as the clustered key. Random inserts cause page splits and fragmentation. Use a time-orderedUUIDv7(Guid.CreateVersion7()),NEWSEQUENTIALID(), or an identity/bigint instead (see Database Design).
SELECT Id FROM Users
WHERE UPPER(Email) = @email; -- index on Email is skipped
Wrapping the column in UPPER() makes the filter non-sargable, so SQL Server scans every row instead of seeking the index.
SELECT Id FROM Users
WHERE Email = @email; -- a case-insensitive collation handles the matching
With the column left untouched the index can be used. Let the column's collation handle case, or store a normalised copy to filter on.
Migrations, procs & security
- DoMake every schema change a versioned, reviewed, backward-compatible migration (expand then contract). Never change production by hand (see Schema Versioning).
- DoIf you use stored procedures, functions, or views, keep them in version control, parameterised, and tenant-aware like any other SQL.
- DoGive database accounts least privilege. The app's identity gets only the access it needs (see Managed Identity & Least-Privilege).
- DoEncrypt sensitive columns and handle KYC and special-category data with the right care (see Data Protection & Privacy, Data Classification).
Self-review checklist
- AskIs every value parameterised, and does every tenant-scoped statement carry its tenant predicate?
- AskAre columns explicit (no SELECT *), NULLs handled, joins not over-counting, and money exact?
- AskAre multi-step changes in a transaction, with concurrency control on read-modify-write?
- AskIs it indexed and sargable, tested on real volumes, with schema changes done as migrations and regulated records never hard-deleted?