SQL Algorithms Checklist

Version: 2.1
Scope: Stored procedures, views, migrations, and query logic across MS SQL / MySQL / SQLite.

MUST

[ ] Parameterization: no string concatenation for SQL commands; strict typing
[ ] Schema assumptions are documented (collation, case sensitivity, null semantics)
[ ] Transactions are explicit when needed; isolation level is justified
[ ] Idempotence: migrations and setup scripts can run safely (or clearly state non-idempotence)
[ ] Concurrency: locking behavior is considered (deadlocks, long locks, escalation)
[ ] Index strategy is validated; changes are accompanied by rationale and impact notes
[ ] Query plans are reviewed for critical paths (MS SQL: actual plan; MySQL: EXPLAIN; SQLite: query plan)
[ ] Pagination uses deterministic ordering (no „OFFSET without ORDER BY“)
[ ] Date/time semantics are explicit (UTC vs local; time zone conversions)
[ ] Error handling maps DB errors to safe application errors (no schema leakage)

[ ] Avoid RBAR/cursors where set-based solutions are available (unless justified)
[ ] Use bounded result sets; avoid „SELECT *“ in public interfaces
[ ] Provide test datasets and regression queries for edge cases

[ ] Document engine-specific differences (e.g., LIMIT/OFFSET, RETURNING support, JSON functions)
[ ] Provide performance baselines for high-volume tables

Version: 2.1 (Split)
Author: Wolfgang van der Stille