Tag: SQL

1. Granular Database User Privileges: GRANT, Roles, Least Privilege

   Mar 28, 2026

   Giving every application and developer the same database account with full rights automatically turns any future security gap into total damage. Granular database user privileges, built from GRANT statements, role hierarchies and the principle of least privilege, limit the damage of a single compromised account to exactly what that account genuinely needs.

   Read more

2. Generating Seed Data for Tests

   Mar 27, 2026

   Test databases with a handful of hand-picked rows rarely cover the cases that actually occur in production. Generating seed data systematically instead of maintaining it by hand yields reproducible, referentially correct datasets that capture edge cases and realistic distributions alike.

   Read more

3. Calculating Running Totals with Window Functions

   Mar 23, 2026

   A running total, a cumulative sum that grows with every row, is one of the most common requirements in account statements, revenue reports and inventory management. With SUM() OVER, this calculation can be solved directly in SQL, without the slow detour through a correlated subquery. This article shows the syntax, the reset per group, and the concrete performance difference.

   Read more

4. Denormalization: Deliberate Tradeoffs, Not Chance

   Mar 13, 2026

   Denormalization means deliberately deviating from a normalized schema to make read queries faster, usually by introducing redundancy or precomputed values. This post shows when it pays off, which techniques exist, what maintenance cost it creates and how to document the decision instead of leaving it to the chance of an incomplete data model.

   Read more

5. Anti-Joins and Semi-Joins Explained

   Mar 3, 2026

   An anti-join finds every row of a table that has no matching row in a second table, a semi-join finds every row that has at least one matching row, without pulling that table's columns into the result. Both patterns solve a common problem, finding customers with no order or products with at least one review, but only one of the common implementations is truly robust with NULL values and performance.

   Read more

6. Full Text Search: SQL Standard vs. Proprietary Features

   Mar 3, 2026

   Full text search is one of the areas where the SQL standard deliberately prescribes very little, and every database has developed its own proprietary solution. This article compares the full text search implementations of PostgreSQL, MySQL, and SQL Server, shows indexing and relevance ranking with concrete examples, and outlines when dedicated search engines like Elasticsearch make more sense than full text search directly in the database.

   Read more

7. ANSI SQL Standard vs. Vendor Dialects Compared

   Feb 28, 2026

   The ANSI SQL standard defines less than many developers assume, and the major database systems rarely implement it in full. This article shows which parts of SQL are actually standardized, where PostgreSQL, MySQL, Oracle, and SQL Server use their own dialect extensions, and how to recognize whether a query is portable or not.

   Read more

8. Stored Procedures: Pros and Cons Honestly Weighed

   Feb 26, 2026

   Stored procedures polarize like few other topics in the database world: fewer network round trips and fine-grained access control on one side, hard to test code without simple versioning on the other. An objective assessment shows in which situations stored procedures are actually the right tool and where application code remains the better choice.

   Read more

9. Query Optimization: A Checklist for Developers

   Feb 25, 2026

   A slow query is rarely a coincidence, it almost always has one of a handful of recurring causes. This checklist walks developers step by step through query optimization, from the WHERE clause through missing indexes to the N+1 problem, so that most cases get solved before a DBA even needs to be consulted.

   Read more

10. Column-Level Data Encryption: Concepts, Key Management, and Practice

    Feb 10, 2026

    Volume encryption only protects data against a stolen hard drive, not against a compromised database account reading data normally through SQL. Column-level data encryption closes exactly this gap, but demands careful decisions around key management, searchability, and indexing that blanket volume encryption never has to make.

    Read more