Page 4 - SQL

An automatically incrementing primary key sounds like a unified concept, but it is not. MySQL's AUTO_INCREMENT, PostgreSQL's SERIAL and sequences, SQL Server's IDENTITY, and Oracle's sequence objects differ so much in semantics, concurrency, and migration behavior that blindly switching between systems leads to gaps, collisions, or wrong assumptions in application code.

A generated column automatically computes its value from other columns of the same row, without a trigger and without the application having to maintain the value itself. This article covers STORED versus VIRTUAL, practical use cases from derived prices to full-text search indexes, migrations on existing tables, and the most important vendor differences between MySQL and PostgreSQL.

Graph queries for hierarchies, organizational structures, and networks can be modeled directly in a relational database using adjacency lists, recursive CTEs, and closure tables, without introducing a dedicated graph database for every connected use case. Whoever knows the limits of this approach makes the right choice for their own data volume and query depth.

A stored execution plan gets compiled for a first, unrepresentative parameter value and then reused for every subsequent call, even when the data distribution behind those calls looks completely different. Parameter sniffing is not random instability, it is a logical consequence of how query planners cache execution plans. Understanding the mechanism means recognizing the symptoms immediately and choosing deliberately between statistics maintenance, query hints, and plan guides.

Once a transaction spans multiple independent databases or services, a simple COMMIT no longer guarantees atomicity. Two-phase commit solves this through a coordinator-based prepare-and-commit protocol, but it has structural weaknesses that lead most large, distributed systems today to prefer the saga pattern with compensating actions instead.

Data modeling with entity-relationship diagrams is the step that comes before every single CREATE TABLE statement: entities, attributes and relationships get defined conceptually first, before being translated into actual tables and foreign keys. This post explains entities, attributes, relationship types and cardinality notation through one continuous example, a library system, and shows the complete path from a diagram to SQL DDL.

Dirty read, non-repeatable read, and phantom read are the three concurrency anomalies the SQL standard uses as a reference when defining isolation levels. Each anomaly arises when two transactions overlap in time, and each can be reproduced with a concrete two-transaction timeline instead of only being described abstractly.

The index exists, the column is selective, yet EXPLAIN shows a full table scan. That pattern almost always has one of a handful of concrete causes: a function on the indexed column, implicit type coercion, a leading wildcard, or stale statistics. This guide walks through a fixed order that narrows down the cause in minutes.

Isolation levels define how much a transaction is allowed to see of concurrently running, not-yet-completed transactions. The SQL standard defines four stages from Read Uncommitted to Serializable, each with a different trade-off between correctness and throughput, and every major database picks its own default.

LAG and LEAD solve a problem that used to almost always require a self-join: accessing the value of the previous or next row within a sorted order. Whether it is month-over-month comparisons, trend analyses, or detecting gaps in a sequence, this article explains the syntax, the offset and default parameters, and the practical use cases of LAG and LEAD in detail.