Tag: SQL

1. Auto-Increment, Sequences and Identity Compared Across Databases

   Dec 22, 2025

   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.

   Read more

2. Generated Columns in Practice

   Dec 22, 2025

   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.

   Read more

3. Simulating Graph Queries in Relational Databases

   Dec 19, 2025

   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.

   Read more

4. Diagnosing and Fixing Parameter Sniffing

   Dec 18, 2025

   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.

   Read more

5. Distributed Transactions and Two-Phase Commit

   Dec 15, 2025

   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.

   Read more

6. Data Modeling: Entity-Relationship Fundamentals

   Dec 11, 2025

   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.

   Read more

7. Dirty Read, Phantom Read, Non-Repeatable Read Compared

   Dec 3, 2025

   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.

   Read more

8. Why Isn't the Database Using My Index: Systematic Troubleshooting

   Nov 9, 2025

   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.

   Read more

9. Isolation Levels: From Read Committed to Serializable

   Nov 8, 2025

   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.

   Read more

10. LAG and LEAD for Previous and Next Values

    Nov 6, 2025

    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.

    Read more