SQL Best Practices

SQL isn’t just about data retrieval; it’s about engineering. It’s about performance, readability, and stability. Bad queries scale poorly, and small schema mistakes can bring down entire systems. This guide moves beyond syntax to show you how to write performant SQL.

SQL Schema Design Best Practices

A strong schema is your most significant source of performance gain. You cannot query your way out of a bad design.

• Normalize to remove duplicates, but stop before joins explode.
• Use clear names for tables and columns
• Pick proper data types:
  • Use decimal for money (never float or real due to rounding errors.
  • Use bigint for IDs if you expect growth.
  • Standardize Time: Store all timestamps in UTC (datetimeoffset or similar). Convert to local time only at the application/presentation layer.
• Always define Primary Keys, Foreign Keys, and Unique constraints. The database is the final guardian of data quality.
• Add indexes carefully.

Example: A Robust Table Definition

CREATE TABLE orders (
    id bigint PRIMARY KEY,                -- BigInt for future-proofing
    user_id bigint NOT NULL REFERENCES users(id), -- Enforce referential integrity
    total decimal(19,4) NOT NULL CHECK (total >= 0), -- Decimal for money; Check constraint for logic
    status varchar(20) NOT NULL DEFAULT 'pending',   -- Non-nullable state prevents logic errors
    created_at datetimeoffset DEFAULT sysdatetimeoffset() -- UTC compliant
);

-- Index designed for the most common access pattern
CREATE INDEX ix_orders_user_date ON orders(user_id, created_at);

💡 Tip: design around how data will be queried, not just how it’s stored.

SQL Query Style Best Practices

Code is read more often than it is written. SQL is code.

• Avoid select * It breaks the application if columns are added/removed, and wastes I/O fetching data you don't need.
• Avoid cursors and WHILE loops. SQL engines are optimized for set-based operations. Loops defeat the optimizer.
• Stop using self-joins or subqueries for running totals or "previous row" logic. Use ROW_NUMBER(), LEAD(), LAG(), and RANK().
• Use CTEs (Common Table Expressions) instead of deeply nested subqueries. use CTEs (WITH clauses) to make logic read top-to-bottom.
• Use consistent aliasing (e.g., u for users), indentation, and comment your complex filters.

SQL Performance and Optimization Best Practices

Make Queries SARGable

SARGable (Search ARGument ABle) means writing queries so the engine can use an index to find results. If you wrap a column in a function, the engine must scan the whole table.

Optimization Tactics

• Check the Execution Plan: Don't guess. Look at the plan to see if you are doing a Table Scan (reading everything) or an Index Seek (jumping to the data).
• EXISTS > COUNT: When checking for presence, use IF EXISTS (SELECT 1...). It stops looking after the first match. COUNT(*) scans the whole set.
• Don’t wrap indexed columns in functions
• Use cte instead of nested subqueries
• Insert or update in batches (e.g., 1,000 rows) rather than one by one or one massive million-row transaction
• Use pagination for large selects

💡 Tip: Set STATISTICS IO ON (in SQL Server) to see precisely how many pages of data your query is reading.

SQL Transactions and Concurrency Best Practices

• Keep it Short: A transaction holds locks. The longer it stays open, the more likely it is to block other users.
• Atomic Logic: Wrap related operations (e.g., debit user A, credit user B) in a single transaction. It must happen all at once or not at all.
• Know Isolation Levels: Understand READ COMMITTED vs. SERIALIZABLE. Higher isolation ensures consistency but increases locking/deadlocks.
• No User Interaction: Never wait for user input (like a UI popup) while a transaction is open.

💡 Tip: One long transaction can block the whole system.

SQL Security Best Practices

• Never concatenate strings to build SQL queries. This is the #1 cause of SQL Injection.
• The application user shouldn't be db_owner. Grant only SELECT, INSERT, UPDATE permissions on specific tables or schemas.
• Log who is changing the schema or accessing sensitive tables.

💡 Tip: The database should never trust user input directly.

SQL Maintenance and Versioning

• SQL is Code: Keep all DDL (schema) and DML (data) scripts in Git.
• Automate Migrations: Use tools like Flyway, Liquibase, or EF Migrations. Never make manual changes to a production DB.
• Monitor Slow Queries: Set up alerts for queries that take longer than X seconds or consume high CPU.
• Archive Data: A smaller table is faster. Regularly move old, inactive data to an archive table.

💡 Tip: your DBA is not your backup strategy.

SQL Common Anti-patterns

• Using distinct to “fix” bad joins. If you get duplicates you didn't expect, your join logic is wrong. Fix the join, don't hide it with DISTINCT.
• Adding too many indexes.
• The NULL Trap: Remember that NULL != NULL. Use IS NULL or COALESCE for safe comparisons.
• Using nvarchar(max) for everything.
• Blindly trusting ORMs. ORMs are great for CRUD, but often generate terrible SQL for complex reports. Write raw SQL when performance matters.
• Nested views on views.

Checklist

• Is the schema normalized, and are keys defined?
• Are dates in UTC and money in Decimal?
• Did you remove all SELECT *?
• Are predicates SARGable (no functions on columns)?
• Are parameters used for all inputs?
• Is the logic SARGable, and are indexes being used?
• Is the script in Git?