MSSQL Writing Guidelines

When to Use

• Starting a new SQL Server application database from scratch
• Adding tables, views, procedures, or functions to an existing schema that follows this methodology
• Reviewing SQL for adherence to type safety, access control, or structural enforcement
• Writing migrations that must be idempotent and safe to rerun
• Designing table hierarchies (base/subtype, parent-child composite keys)
• Implementing background job queues backed by relational tables

When NOT to use: one-off ad-hoc queries, read-only reporting databases, or any non-SQL-Server engine (PostgreSQL, MySQL, Oracle, SQLite). Examples and syntax throughout are T-SQL — they will not run on other dialects without translation.

The Two Access Rules

1. All reads go through views. Never SELECT directly from tables. Views filter by role, enforce row-level security, flatten joins, and evolve independently of tables.

2. All mutations go through stored procedures. No ad-hoc INSERT, UPDATE, or DELETE. Procedures validate inputs, manage transactions, check business rules, and return structured errors.