Overview
23 Using someone else’s script
This chapter emphasizes developing the equally important skills of reading and improving other people’s SQL as you learn to write it. Framed as a guided review rather than a hands-on lab, it walks through real-world scripts you might encounter, showing how to infer the author’s intent, assess correctness, and refine design and performance. The running example is a royalty-payment workflow that introduces a new table to track payments by author, title, and year, and a stored procedure to populate it. The overarching goals are clarity, data integrity, consistency with existing schema conventions, and a preference for set-based solutions over row-by-row processing.
The first script, a CREATE TABLE for author payments, is critiqued for ambiguous naming and weak constraints. Improvements include renaming generic columns (ID, Author, Title) to meaningful keys (AuthorPaymentID, AuthorID, TitleID), enforcing NOT NULL where appropriate, adding a primary key and foreign keys, and aligning data types with their semantics (e.g., using an integer for year). The chapter also recommends CHECK constraints to guard ranges (for years and positive amounts) and suggests considering a composite uniqueness across AuthorID, TitleID, and PaymentYear. The result is a table definition that better enforces integrity, improves readability, and aligns with the wider database design.
The second script, a cursor-driven procedure that inserts annual royalty rows, is analyzed and then refactored into a set-based approach. Observations include unnecessary variables, mismatched data types, and inefficient use of functions (like extracting year from dates) that impede performance at scale. The improved solution computes author counts and annual sales with joins and aggregations, uses date boundaries instead of per-row date functions, and inserts the results in one set-based operation. Further enhancements consider temporary tables for heavy workloads, replacing a single-year parameter with a date-range interface for flexibility, and even substituting the whole persistence flow with a view when persisted data isn’t required. The key takeaway is to choose designs that are clear, scalable, and maintainable, leveraging constraints and set-based SQL to do the heavy lifting.
FAQ
What were the main problems with the original CREATE TABLE for authorpayment?
The table used ambiguous column names (ID, Author, Title), lacked primary and foreign key constraints, allowed NULLs where values are required, and stored PaymentYear as a char(4) instead of a numeric type. Fixes include renaming columns to AuthorPaymentID, AuthorID, and TitleID; adding a PRIMARY KEY (optionally AUTO_INCREMENT); enforcing NOT NULL; defining FOREIGN KEYs to author and title; and using an int for PaymentYear.Why use an integer for PaymentYear instead of char(4)?
Years are numeric and don’t require character storage. Using int enforces numeric integrity and avoids issues like accidental non-digit characters or inefficient comparisons. Character types are better reserved for values that need leading zeros (for example, ZIP codes).Which constraints help protect data integrity in authorpayment?
NOT NULL ensures required values exist for AuthorID, TitleID, PaymentYear, and PaymentAmount. CHECK constraints can restrict PaymentYear to a sensible range (for example, 2000–2100) and ensure PaymentAmount is positive. FOREIGN KEYs to author(AuthorID) and title(TitleID) prevent orphaned references.Should the primary key be a surrogate AuthorPaymentID or a composite key?
Both are valid. A surrogate key (AuthorPaymentID) is simple and stable, while a composite key on (AuthorID, TitleID, PaymentYear) reflects natural uniqueness and can remove the need for a separate unique index. Choose based on consistency with your schema, expected queries, and maintenance preferences.Why is a cursor a poor fit for the InsertAnnualPayment stored procedure?
Cursors process rows one-by-one, which is slow and complex compared to set-based operations that let the database optimize work across sets. The original cursor also introduced unnecessary variables and queries, type mismatches, and conditional logic that set-based joins could handle implicitly.How can we compute annual sales without using YEAR(OrderDate) in the WHERE clause?
Filter by a date range, not by applying a function to each row. Build start and end boundaries for the target year (for example, first day of the year inclusive, first day of the next year exclusive) using date functions like MAKEDATE and DATE_ADD (or your RDBMS equivalents). This approach is more index-friendly and scales better.What does the set-based rewrite of InsertAnnualPayment look like conceptually?
Use a single INSERT ... SELECT that joins titleauthor to two aggregated sets: one that returns total annual sales per title, and another that returns royalty and author count per title. Compute each author’s share in the SELECT (royalty percentage × total sales ÷ author count), and insert all qualifying rows in one statement.When should I consider a temporary table instead of a subquery for total sales?
If order tables are large, a temp table that stores pre-aggregated sales per title for the period can reduce repeated scans and improve join performance. It adds write overhead but can yield faster subsequent joins; test both approaches with your data volumes to decide.How can the stored procedure be made more flexible than a single _PaymentYear parameter?
Accept a start and end date instead. This enables annual, quarterly, monthly, or custom ranges without changing the code, making the procedure more reusable and adaptable to future needs.When is a view preferable to storing payments in a physical table?
If you don’t need to persist summarized payments, a view can compute royalties on demand, removing redundancy and letting you filter by any author, title, order, or date range. Use a table when you need historical snapshots, auditing, or performance for repeated reads of the same summarized results.
Learn SQL in a Month of Lunches ebook for free
