Key Takeaways
Treat every SQL shortcut as a debt transfer, not a free simplification
Convenience has a bill. An antipattern is a familiar technique that seems to solve an immediate problem but creates larger costs elsewhere. The book’s central warning is that database shortcuts often move work from the schema into every query, application, and maintenance task that follows. A comma-separated list saves a table today, then makes searching, validating, counting, and editing harder tomorrow.
Model before coding. Karwin’s first encounter with SQL captures the scale mismatch: a manager thought implementing SELECT meant building a small parser, but a working SELECT depends on a full database engine. SQL is not merely a way to fetch data. Its structure, constraints, and query rules are part of the application’s design.
The debt metaphor is useful because it redirects attention from the code saved now to the obligations created later. A schema is a shared interface used by applications, reports, operators, and future developers. When a design makes its rules explicit, all those users can rely on the same guarantees. The caveat is that not every shortcut is reckless: prototypes and disposable scripts have different maintenance horizons. The useful test is whether the design’s hidden costs are acceptable for its expected lifespan and number of users.
Store each member of a changing collection in its own row
Give values structure. A product with several contacts should connect to them through an intersection table, with one row per product-account pair. That makes ordinary joins, counts, foreign keys, and uniqueness checks possible. The same idea solves multicolumn lists: store a bug’s tags as rows in a Tags table, not in tag1, tag2, and tag3 columns. A primary key on the pair prevents duplicate tags while allowing the set to grow.
Keep values out of schema names. Revenue columns named revenue2002, revenue2003, and so on force a table change every year. Store the year in a row instead. This avoids repeated schema edits and makes cross-year reports straightforward. Lists and year-specific columns are different symptoms of the same confusion: values are being encoded as text or as database structure.
This principle is a practical application of separating data from its representation. Rows let a collection grow without requiring every consumer to learn a new column name or parse a string. That flexibility is especially valuable when many independent tools use the same database. Still, normalization is not a command to split every fact into the smallest possible pieces. A genuinely fixed set of distinct roles, such as reporter and verifier, may deserve separate columns because each role has its own meaning.
Choose a tree model by its reads, writes, and integrity needs
Match structure to use. The simplest hierarchy design, an Adjacency List, stores each node’s parent identifier. It makes adding a child easy, but finding every descendant is awkward unless the database supports recursive queries. A recursive common table expression can traverse the tree without hard-coding its depth.
Pay for the operations you need. Path Enumeration stores each node’s ancestry as a string, which makes breadcrumbs convenient but cannot reliably enforce references. Nested Sets make subtree reads efficient, but inserting or moving nodes requires renumbering. A Closure Table records ancestor-descendant pairs, making tree queries and many updates clear, at the cost of extra rows and another table. The book’s inventory story warns against building for theoretical unlimited depth when actual data needs only one parent-child level.
Hierarchies expose a broader engineering lesson: data models optimize particular operations, not every operation equally. Choosing a model is therefore a workload decision, much like selecting an index or cache. Recursive queries also change the tradeoff by making the conventional parent-reference model more useful. Before adopting a sophisticated structure, teams should verify that its benefits address frequent, important tasks rather than a hypothetical future. The inventory anecdote shows how imagined requirements can produce real complexity without producing real value.
Make the database enforce identity and relationships, not application promises
Put invariants near the data. A primary key may be a natural value, a composite of several columns, or an automatically generated surrogate. Requiring every table to use a generic integer named id can hide meaning and permit duplicate associations unless the meaningful columns also have a unique constraint. In a product-contact intersection table, the pair of product and account identifiers can itself be the key.
Let constraints reject bad states. Foreign keys prevent references to nonexistent rows, while lookup tables keep changing categories, such as bug status, in data rather than hard-coded column definitions. Karwin’s equipment-booking example shows the cost of omitting foreign keys: application checks and cleanup scripts cannot reliably prevent concurrent changes from creating inconsistencies.
Declarative constraints function like guardrails that apply no matter which application, script, or administrator changes the database. This is stronger than relying on every code path to remember the same rule. It also supports fail-fast design: invalid state is rejected at the point of entry, while the cause is still clear. Constraints do require thought about deletion and update policies. The point is not to constrain everything indiscriminately, but to make genuine business rules enforceable and visible.
Treat NULL as unknown, and never smuggle missingness in as zero
Unknown is not ordinary. In SQL, NULL means a value is missing or inapplicable; it is not zero, an empty string, or false. Comparisons involving NULL produce an unknown result, so `column = NULL` does not find missing values. Use `IS NULL` or `IS NOT NULL`. Likewise, adding a number to NULL produces NULL, and concatenating a NULL middle initial can blank an entire name unless the query uses a function such as COALESCE.
Do not disguise absence. Replacing missing hours with -1 corrupts averages unless every query remembers to exclude that marker. Using a fake account to represent an unassigned bug creates a real account reference where no person exists. Declare a column NOT NULL only when a value is truly required.
Missing information has meaning, and suppressing that meaning tends to create brittle conventions. SQL’s three-valued logic is less intuitive than ordinary true-or-false reasoning, but it makes uncertainty explicit rather than pretending it is a value. This is particularly important in analytics, where a placeholder can silently distort totals. Some domains do have several kinds of absence, such as not yet known versus intentionally withheld. In that case, a single NULL may be insufficient, but an arbitrary numeric sentinel is not a reliable substitute.
Store money in NUMERIC, not approximate binary FLOAT values
Exactness depends on representation. FLOAT stores numbers in a finite binary format. Many decimal fractions, including 59.95, have no exact finite binary representation, so the stored value is only an approximation. A query may display 59.95 while an equality comparison against 59.95 fails; repeated calculations can also accumulate small errors.
Choose a declared scale. NUMERIC or DECIMAL stores fixed-precision decimal values. For example, NUMERIC(9,2) allows nine total digits, with two after the decimal point. This suits prices and hourly rates, where exact decimal comparisons matter. Floating-point values remain useful for scientific measurements that tolerate approximation and benefit from a very wide range. The type should reflect the meaning of the quantity, not just the fact that it has a fractional part.
A data type is a statement about what kinds of error the application can tolerate. Binary floating point is not defective; it is designed for a different class of computation, where scale and approximation matter more than exact decimal equality. The failure comes from using it without recognizing that contract. This distinction generalizes beyond currency: measurement systems, sensor readings, and accounting quantities often need different representations even when all look like numbers in an interface.
Never ask GROUP BY to guess which row an aggregate came from
One output value per group. A grouping query can safely return its grouping columns and aggregate results, such as the latest bug date per product. It cannot reliably return a bug identifier from that group unless the query defines which row supplies it. Several bugs may share the latest date, and an average or sum may not correspond to any single row.
Select the row deliberately. A window function can rank bugs within each product by date and keep the first. A derived table can calculate each product’s maximum date and join back to matching bugs, though ties may return multiple rows. A correlated subquery or an outer-join method can also express the rule. If the requirement is only the latest date, omit the identifier rather than implying a relationship the query has not established.
This is a reminder that SQL reports are governed by logical meaning, not by what a programmer hopes the database will infer. Ambiguous queries can appear to work on one dataset and fail when ties or new records appear. That makes them especially dangerous: their defect is conditional, not necessarily immediate. Writing the tie-breaking rule explicitly also forces a useful product question: if two bugs share the newest date, should the report show both, or choose one by a secondary criterion?
Measure real workloads before adding indexes or rewriting queries
Replace guessing with evidence. Indexes speed some reads but consume storage and add work to inserts and updates. An index on every column is not a strategy: a compound index on last name and first name generally cannot efficiently answer a search on first name alone, and an index cannot help a substring search such as `%crash%` in the same way it helps an exact lookup.
Follow MENTOR. The book’s index checklist is to measure expensive queries, inspect their execution plans, nominate targeted index changes, test the results, optimize server resources such as memory when justified, and maintain indexes when evidence supports it. A slow page may not be a database problem at all; one example traced most of an application’s delay to parsing its own HTML.
The performance advice is strongest when treated as experimental method rather than a fixed recipe. Profiling establishes where time is actually spent; execution plans reveal how the database approaches a query; retesting checks whether a change helped. This resembles controlled diagnosis in operations engineering, where interventions are evaluated against a measured baseline. Index selectivity and query frequency matter, so a technically usable index is not automatically valuable. The correct optimization is the one that improves the workload that matters.
Use full-text indexes for words, and avoid random sorts on large tables
Use tools built for the search. A predicate such as `LIKE '%crash%'` scans text and can match unintended fragments, such as finding `one` inside `money`. Full-text search indexes tokenize text and support word-oriented searches more efficiently. Database products provide different implementations, while independent search engines can handle larger or specialized search needs.
Do not randomize a full table. `ORDER BY RAND()` assigns a random value to every row and sorts the results, so its cost grows with the table. Alternatives include selecting by a random offset or sampling among identifiers, each with tradeoffs around gaps, memory, and equal probability. For a small fixed set, random sorting may be perfectly adequate. Reusing a cached choice, such as one advertisement for a short interval, can also avoid repeating expensive work.
The central distinction is between a query that is merely expressible and one whose operations align with the available data structures. Substring matching and random sorting ask a conventional index to solve problems it was not built to solve. Specialized search indexes or sampling strategies change the problem rather than trying to force an unsuitable tool. There is no universally fair random-selection method for every dataset, so requirements such as uniform probability, freshness, and cost should be stated before choosing.
Keep queries small, explicit, and resistant to schema changes
Complexity can multiply rows. Joining a bug to its tags and products independently creates every tag-product pairing. Counts then inflate, and adding DISTINCT may conceal the faulty join while adding more work. Separate queries are often clearer and safer than one giant statement. When a report truly needs combined results, each join should have an explicit relationship.
Name what the code depends on. `SELECT ` can return duplicate column names or unexpected data after a schema change, and implicit INSERT columns depend on table order. Listing columns makes changes fail visibly instead of silently shifting values. When dynamic SQL is confusing, inspect or log the generated query itself. Check database errors and exceptions rather than assuming every operation succeeded.
Shorter code is not always simpler software. A query that compresses several unrelated calculations can hide assumptions about joins, duplicates, and result shape. Splitting work may add statements while reducing the number of ideas a maintainer must hold at once. Explicit columns work similarly: they trade typing for a clear contract between database and application. One caveat is that query count can matter on high-latency connections, so simplicity should be weighed against measured network cost.
Keep user input as data, and store only password verifiers
Separate values from SQL code. Concatenating a request parameter into a query can change its syntax, even through an innocent name containing an apostrophe. Parameterized queries keep ordinary values separate from parsed SQL. Table names, column names, and sort directions cannot generally be parameters, so map user choices to a short allowlist of permitted identifiers. Stored procedures and frameworks do not automatically eliminate injection if they construct unsafe dynamic SQL.
Make passwords unreadable. Store a salted password hash, not the password itself, and use a current password-hashing method such as Argon2, bcrypt, or PBKDF2. A reset token or temporary credential replaces password recovery. The database should never be able to reveal a user’s original password to support staff or an attacker.
Both practices protect a boundary: input remains data rather than executable instructions, and stored credentials remain evidence for verification rather than recoverable secrets. Parameterization is powerful precisely because the database parses the query before receiving its values. It does not validate business meaning, so authorization and input checks still matter. Password hashing also needs purpose-built, slow algorithms; a fast general-purpose hash is not an equivalent defense against large-scale guessing.
Version, test, and document the database as production code
Schemas have a lifecycle. Database definitions, migrations, seed data, triggers, procedures, tests, and diagrams belong alongside application code. The book’s account of inheriting an undocumented C application shows how much time disappears when one person’s knowledge is the only record of how a system works. Test constraints and queries, and keep development close to production in database version and configuration.
Choose architecture by evidence. Stored procedures can be useful for privileged operations or reducing network round-trips, but they are not automatically faster or easier to maintain. Likewise, storing images in the database simplifies transactional consistency and backups, while external files may suit systems that prioritize lean databases and easy media editing. Each choice assigns operational responsibilities somewhere; document who owns them.
Database reliability is socio-technical: correctness depends not only on SQL, but also on deployment routines, institutional memory, and who can safely make changes. Version control and tests turn implicit assumptions into reviewable artifacts. The architectural examples also resist absolutism. A database can coordinate transactions, but application servers may offer more scalable compute; a BLOB can simplify integrity, but separate media storage may fit particular operational needs. The durable rule is to make tradeoffs explicit and test them in the real environment.
Analysis
The book’s deepest argument is that relational design is a way to make facts legible and enforceable, not an academic preference for tidy tables. Many antipatterns arise by confusing data with metadata: lists become strings, values become column names, and possible parent types become labels attached to unverified identifiers. These choices seem flexible because they postpone decisions, but they also remove the database’s ability to validate meaning. The resulting complexity returns in application code, reports, migrations, and incident response.
A second through-line is the importance of explicit contracts. Keys define identity, foreign keys define valid relationships, data types define acceptable values, and query columns define what an application can rely on. SQL can be permissive in ways that conceal ambiguity, especially around NULL, grouping, implicit columns, and vendor-specific behavior. Reliable systems therefore make assumptions visible and test them against real data and deployed database versions.
The book also resists simplistic optimization folklore. Joins are not automatically slow, indexes are not automatically beneficial, stored procedures are not inherently superior, and normalization is not synonymous with performance. The proper choice depends on access patterns, query plans, concurrency, operational constraints, and the relative cost of reads and writes. Measurement is the method for resolving these tradeoffs, while the examples show why a small development dataset can disguise a design that fails at scale.
Its practical significance extends beyond SQL. A database is shared infrastructure, often used by more clients and over a longer lifespan than the application code that first created it. Treating its schema as executable, testable, documented software reduces dependence on individual memory and makes invalid states harder to create. The most durable lesson is therefore not to obey a catalog of rules mechanically. It is to ask what each shortcut moves out of the schema, what guarantees disappear, and whether the team is prepared to own the resulting complexity.
Review Summary
SQL Antipatterns, Volume 1 is highly praised for its clear explanations of common database design mistakes and solutions. Readers appreciate its practical approach, easy-to-follow format, and real-world examples. Many found it valuable for both beginners and experienced developers, highlighting its usefulness in avoiding pitfalls and improving database design skills. The book's coverage of security issues and application development anti-patterns was also well-received. While some felt certain topics could have been explored more deeply, most reviewers considered it an essential read for anyone working with relational databases.
People Also Read
Glossary
Antipattern
Tempting but costly techniqueA commonly used technique that appears to solve a problem but tends to introduce other, more serious problems. In database work, an antipattern often reduces immediate effort by making data harder to validate, query, update, or maintain. The book studies these patterns so developers can recognize their warning signs and choose alternatives suited to the actual requirement.
Intersection Table
Stores many-to-many linksA table whose rows connect records from two other tables, usually by storing a foreign key to each. For example, a Contacts table can associate products with accounts, with one row for each product-account pairing. A composite key can prevent duplicate pairings, while additional columns can describe the relationship itself.
Entity-Attribute-Value
Stores attributes as rowsA database design in which an entity identifier, an attribute name, and an attribute value are stored as rows rather than using a fixed column for each attribute. It can accommodate changing fields, but often weakens type checks, required-value rules, referential integrity, and straightforward reporting. The book recommends subtype-based or semistructured designs when those better fit the need.
Adjacency List
Stores each node's parentA hierarchy design in which each row represents a node and contains a reference to its immediate parent, commonly through a self-referencing foreign key. Adding a child is simple and the structure is normalized. Finding an entire subtree can be awkward unless the database supports recursive queries or the application performs the traversal.
Closure Table
Stores ancestor-descendant pathsA hierarchy design that stores each ancestor-descendant relationship in a separate table, including a self-reference for every node. Queries for ancestors and descendants become direct joins, and the structure can support flexible moves and multiple trees. The tradeoff is additional storage and the need to keep path rows synchronized with hierarchy changes.
MENTOR
Evidence-based index checklistA method for making index decisions through measurement and verification rather than guesswork. The steps are Measure query costs, Explain execution plans, Nominate possible indexes, Test their effect, Optimize relevant server resources, and Rebuild or maintain indexes when justified. Its purpose is to focus tuning on expensive real workloads and confirm that changes improve them.
Single-Value Rule
One value per groupA rule for grouped SQL queries: every selected expression must yield one well-defined value for each output group. Grouping columns and aggregate functions satisfy this requirement. An unrelated column may contain several possible values within a group, so selecting it without defining which row or value is intended can produce an error or an unreliable result.
SQL injection
Input alters query syntaxA security flaw in which content incorporated into a dynamically constructed SQL string changes the statement’s intended syntax or behavior. Parameterized queries separate ordinary values from the SQL text. Dynamic identifiers and keywords require validation or mapping to approved choices, since they generally cannot be supplied as value parameters.
FAQ
1. What is SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin about?
- Focus on common mistakes: The book explores frequent errors and bad practices in SQL database programming, aiming to help developers recognize and avoid these pitfalls.
- Comprehensive coverage: It addresses issues in logical and physical database design, query writing, and application integration with SQL.
- Practical, example-driven approach: Bill Karwin uses real-world scenarios and a hypothetical bug-tracking database to illustrate antipatterns and their solutions.
- Goal of the book: The main objective is to help readers write more reliable, maintainable, and efficient SQL code by understanding why certain patterns are problematic.
2. Why should I read SQL Antipatterns by Bill Karwin?
- Learn from others’ mistakes: The book reveals common errors that even experienced developers make, helping you avoid bugs, security vulnerabilities, and performance issues.
- Applicable to all skill levels: Whether you’re a beginner or a seasoned professional, the book offers insights that reinforce good practices and dispel widespread misconceptions.
- Improve collaboration: By explaining the consequences of poor SQL practices, it helps bridge the gap between developers and DBAs, fostering better teamwork.
- Enhance application reliability: Following the advice reduces risks like SQL injection, data inconsistencies, and maintenance headaches.
3. What are the key takeaways and main categories of SQL antipatterns in SQL Antipatterns by Bill Karwin?
- Four main categories: The book covers Logical Database Design, Physical Database Design, Query Antipatterns, and Application Development Antipatterns.
- Learn to spot pitfalls: Readers learn to identify and correct common mistakes in schema design, query writing, and application integration.
- Emphasis on practical solutions: Each antipattern is paired with recommended best practices and alternative approaches.
- Improved database skills: The book aims to make readers more proficient in writing clear, efficient, and secure SQL.
4. What are the best quotes from SQL Antipatterns by Bill Karwin and what do they mean?
- “An expert is a person who has made all the mistakes that can be made in a very narrow field.” This quote highlights the value of learning from mistakes, both your own and others’.
- “One size does not fit all.” Refers to the importance of context in database design, especially regarding primary key conventions.
- “Avoid guessing” (about indexes): Stresses the need for evidence-based decisions when optimizing database performance.
- “Don’t execute unverified input as code.” A reminder of the dangers of SQL injection and the importance of secure coding practices.
5. How does SQL Antipatterns by Bill Karwin address logical database design mistakes like Jaywalking and EAV?
- Jaywalking antipattern: Storing multiple values in a single column (e.g., comma-separated lists) complicates queries and data integrity.
- Recommended fix: Use intersection (join) tables to represent many-to-many relationships, enabling proper foreign key constraints and easier queries.
- EAV (Entity-Attribute-Value) drawbacks: Storing attributes as rows leads to complex queries, poor data integrity, and reporting difficulties.
- Alternatives to EAV: Model subtypes explicitly with inheritance patterns or use semistructured data types like JSON columns when appropriate.
6. What does Bill Karwin recommend for handling hierarchical data in SQL database design?
- Naive tree model limitations: Using a parent_id column makes querying ancestors or descendants complex and inefficient.
- Alternative models: The book presents Recursive Queries (CTEs), Path Enumeration, Nested Sets, and Closure Tables as better options.
- Trade-offs of each model: Nested Sets are efficient for querying but hard to maintain; Closure Tables are versatile but require extra storage.
- Choosing the right model: The best approach depends on your application’s query and update requirements.
7. What are Bill Karwin’s recommendations on primary key conventions and foreign key constraints in SQL Antipatterns?
- Meaningful primary keys: Avoid generic id columns; use descriptive names like bug_id or account_id to clarify relationships.
- Compound and natural keys: Sometimes these are preferable, especially in intersection tables, to prevent duplicates and maintain integrity.
- Importance of foreign keys: Always declare foreign key constraints to enforce referential integrity and automate cascading updates/deletes.
- Avoid manual integrity checks: Relying on application code for data integrity is error-prone and inefficient.
8. How does SQL Antipatterns by Bill Karwin explain and address query antipatterns such as ambiguous GROUP BY, random selection, and full-text search?
- Ambiguous GROUP BY: Referencing nongrouped columns in aggregation queries leads to errors or unreliable results; use window functions or subqueries for clarity.
- Random row selection: ORDER BY RAND() is inefficient; instead, select random keys or use database-specific sampling features.
- Full-text search: LIKE and regex are slow; use vendor-specific full-text indexes or external search engines for better performance.
- Pattern matching alternatives: Build inverted indexes or use third-party tools if built-in full-text search isn’t available.
9. What advice does Bill Karwin give in SQL Antipatterns for handling NULL values and grouping queries?
- Use COALESCE for nulls: Substitute nulls with default values to prevent unexpected null propagation in expressions.
- Beware of NOT IN (NULL): Understand SQL’s three-valued logic to avoid confusing query results.
- Single-Value Rule in GROUP BY: Every column in a GROUP BY query must have a single value per group, enforced by aggregate functions or inclusion in the GROUP BY clause.
- Use standard functions: Prefer standard SQL functions like COALESCE for portability and clarity.
10. What security pitfalls and solutions regarding passwords and SQL injection does SQL Antipatterns by Bill Karwin highlight?
- Plain-text password storage: Storing passwords in plain text is a critical security flaw; always use salted cryptographic hashes.
- Password reset best practices: Avoid sending passwords by email; use temporary tokens and force users to set new passwords securely.
- Preventing SQL injection: Never interpolate user input directly into SQL; use prepared statements with parameter placeholders.
- Input validation: Filter and validate all user input, and use proper escaping for dynamic SQL parts that can’t be parameterized.
11. What does Bill Karwin recommend for using indexes effectively in SQL Antipatterns?
- Avoid guesswork: Don’t create indexes without understanding query patterns; too many or too few indexes can harm performance.
- MENTOR methodology: Measure, Explain, Nominate, Test, Optimize, and Rebuild indexes to ensure they’re effective.
- Beware of index misuse: Indexes on low-selectivity columns or mismatched expressions are often ineffective.
- Regular maintenance: Rebuild and optimize indexes as part of ongoing database management.
12. What is Bill Karwin’s perspective on stored procedures in modern application development, according to SQL Antipatterns?
- Not always the best choice: Don’t use stored procedures by default; consider their portability, debugging, and deployment challenges.
- Performance considerations: Overusing stored procedures can create bottlenecks by overloading the database server.
- Modern architecture preference: Implement business logic in application code when possible, leveraging modern languages and frameworks.
- Selective use cases: Use stored procedures for privileged operations, reducing network latency for complex tasks, or infrequent administrative jobs.
Download PDF
Download EPUB
.epub digital book format is ideal for reading ebooks on phones, tablets, and e-readers.