Page 10 - SQL

A moving average smooths noisy time series directly in the database: with AVG() OVER() and a precisely defined ROWS BETWEEN window, you calculate rolling averages for trend analysis, without loading raw data into an application or resorting to slow self-joins.

A query without a timeout is a bet on the future: as long as everything runs normally, it never shows up as a problem. But the moment a table grows unexpectedly, an index is missing, or a lock gets held longer than planned, that same query can block for minutes or hours, occupy connections in the pool, and in the worst case bring down the entire application. A properly configured timeout is therefore not cosmetic, it's one of the most effective defenses against cascading failures. This article separates statement timeout on the database side from connection timeout on the application side, shows sensible values for different use cases, and explains how a transaction should be cleanly handled after a timeout.

A schema migration without clear versioning and a rollback plan quickly turns into a risk on a large live table. This article shows naming conventions for migration files, the difference between forward-only and reversible migrations, the expand-contract pattern, and safe approaches for NOT NULL columns on tables with millions of rows, with concrete SQL for every step.

A savepoint marks an intermediate point inside a running transaction that you can later roll back to, without giving up the entire transaction. Especially in batch processing, this tool lets you discard individual failed items while keeping the work already completed successfully.

An execution plan shows how a database actually runs a query, which indexes it uses, in what order it joins tables and how expensive each step is estimated to be. Anyone who can read this execution plan finds performance problems in minutes instead of guessing, regardless of whether the query runs against MySQL, PostgreSQL or SQL Server.

NULL values in aggregate functions are treated differently by COUNT, SUM and AVG, and these exact differences regularly lead to wrong average values and misleading reports. This article explains why COUNT(*) and COUNT(column) return different results, how AVG can be skewed by NULL, and how COALESCE, NULLIF and deliberate GROUP BY control this NULL behavior on purpose.

A migration that spontaneously renames a column or locks a table can bring a running Symfony application to its knees right in the middle of rush hour. Zero downtime database migrations with Doctrine instead follow the expand contract pattern: schema and application grow through small, always backward compatible steps. This article walks through every step in detail, from the first new column to safely removing the old one.

A regular index speeds up finding rows based on a filter condition, but in most cases still requires an additional access to the actual table afterward to fetch the remaining requested columns. This extra jump, often called a key lookup or bookmark lookup, costs noticeable time with many matches. A covering index already contains every column a query needs, making the lookup entirely unnecessary, an effect known as an index-only scan.

A CHECK constraint can do far more than enforce a positive price. Multi-column conditions, state machines for status fields, IMMUTABLE functions, and EXCLUDE constraints against overlapping ranges turn a simple range check into a tool for complex business rules that would otherwise only be captured with triggers or application code.

Anyone stitching subtotals and grand totals together from several separate queries with UNION is solving a problem that GROUP BY ROLLUP already handles natively. With ROLLUP, CUBE, and GROUPING SETS, multi-level aggregations arrive in a single statement, consistent and performant.