Page 13 - SQL

Choosing a primary key is one of the most consequential decisions in table design: a business natural key such as an email address or a product SKU carries meaning, an artificial surrogate key such as an auto-increment value or a UUID is stable and meaningless. This post compares both strategies concretely, presents composite keys as a third option, and delivers a practical decision guide.

NTILE(n) splits a sorted result set into n roughly equal buckets and turns quartile, decile and general percentile analysis into a single SQL query. Instead of exporting customer values, response times or test scores into statistics software, distribution buckets are produced directly in the database, reproducibly and without manual sorting work.

A wrongly chosen join type either returns too few rows, because unmatched records silently vanish, or too many rows, because a missing join condition creates a cartesian product. Inner join, left join, right join and full join differ exactly in which rows from the involved tables end up in the result set when a condition is not met, and anyone who does not understand this builds reports with silently wrong numbers.

Most data models only know a single time axis: when a row was last changed. Once retroactive corrections enter the picture, though, such as a miscalculated invoice that gets corrected three months later, a single time axis stops being enough. Bitemporal modeling consistently separates valid time, meaning when a fact was actually true from a business perspective, from transaction time, meaning when it became known to the system. This article explains both axes, walks through a fully worked example, and gives a realistic sense of when the extra modeling effort actually pays off.

EXISTS and IN look interchangeable at first glance, but differ in one decisive point: how they handle NULL values in the inner result set. While EXISTS only checks for the existence of a row and NULL plays no role there, a NOT IN with a NULL-containing list can silently return an empty result set, a behavior that regularly leads to missing rows in reports in production, without any error appearing.

Teams that only find slow queries once users complain are working reactively instead of proactively. With slow query log, pg_stat_statements and a clear sampling process, slow queries can be found systematically before they become a production problem, regardless of which database system is in use.

A thousand individual INSERT statements are almost always slower than the same thousand rows sent as one batch, because every single round trip to the database costs network latency, parsing, and transaction overhead. Batch processing bundles these costs, whether through multi-row INSERTs, bulk-load tools, or larger transactions, and can cut load time by an order of magnitude.

Normalization is the systematic process of splitting a table into smaller, linked tables so that each piece of information is stored exactly once. This post walks through a single, continuous orders table step by step to show what first, second and third normal form each require, which concrete anomaly they eliminate, and what the result looks like as an actual SQL table structure.

Where a NULL value ends up in a sorted result set, and which function replaces it with a fallback value, sounds like a footnote, yet it is regulated completely differently between MySQL, PostgreSQL, SQL Server, and Oracle. Anyone who wants to keep sorting logic and NULL handling portable across these systems needs to precisely distinguish NULLS FIRST, NULLS LAST, COALESCE, IFNULL, ISNULL, and NVL.

A transaction that never got committed does not block a single query, it silently prevents vacuum in the background, holds locks, and bloats undo structures without a single slow query ever showing up in the slow query log. Detecting idle in transaction sessions systematically through session views leads to the cause in application code, instead of treating symptoms only.