SQL Server & T-SQL

Microsoft SQL Server is a relational database management system (RDBMS). T-SQL (Transact-SQL) is Microsoft's extension of ANSI SQL with variables, control flow, error handling, stored procedures and more.

The SQL Server family:

Editions: Enterprise, Standard, Developer (free, full features, non-production), Express (free, limited), and Azure SQL.

SQL statement categories:

How SQL Server executes a query:

1. Parse — syntax check, build a parse tree.
2. Bind (algebrize) — resolve names, types, permissions.
3. Optimise — the cost-based query optimizer uses statistics to choose an execution plan (join types, index seeks vs scans, parallelism).
4. Plan cache — plan stored for reuse (parameterised queries reuse plans).
5. Execute — the storage engine reads pages from the buffer pool (memory) or disk, acquiring locks/latches.
6. Return results to the client.

Logical query processing order (not the written order):

FROM → ON → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → TOP/OFFSET-FETCH

That's why you can't use a SELECT alias in WHERE, but you can in ORDER BY.

Transaction lifecycle: BEGIN TRAN → statements write to the transaction log first (write-ahead logging) → COMMIT (durable once the log is hardened) or ROLLBACK (undo using the log). Checkpoints write dirty pages to data files; recovery replays/undoes the log after a crash.

Database development lifecycle: requirements → conceptual model (ER diagram) → logical design (normalisation) → physical design (types, indexes, partitions) → implementation (DDL scripts/migrations) → testing → deployment (DACPAC/scripts) → monitoring and tuning → maintenance (backups, index/statistics maintenance).