Book Review: SQL Query Design Patterns and Best Practices

Another book that I read recently was SQL Query Design Patterns and Best Practices Book Cover by Steve Hughes, Dennis Neer, Dr Ram Babu Singh, Shabbir H Mala, Leslie Andrews, and Chi Zhang. It also came from our friends at PackT publishing.

Purpose

The intro to the book says “This book is for the SQL developer who is ready to take their query development skills to the next level. This includes report writers, data scientists, or similar data gatherers and allows users to expand their skills for complex querying and build more efficient and performant queries.”

Worth noting that we have a much more detailed online course that covers these topics. You’ll find it here:

Advanced T-SQL for Developers and DBAs (sqldownunder.com)

The book does cover a lot of good common sense regarding writing queries, like not returning columns or rows in queries when you don’t need them.

There were many parts that I really liked. For example, the JSON chapter was pretty good. It was also good to see a chapter covering T-SQL notebooks in Azure Data Studio.

It was interesting to see a security chapter in the book but I found it was very lacking in content.

Multiple Authors and Editing

I get nervous whenever I see a book with a lot of authors. You know that unless both the general and tech editing are exemplary, that you’ll end up with a book that looks like it’s written by a lot of people, and not a single thought process.

I used to find it amusing when Wrox used to publish books with all the authors on the cover like this:

I think they thought is was something to be proud of, but I used to find an inverse relationship between the quality of the book and the number of pictures on the cover. When a book has 17 authors, I’ll just pass thanks, unless the book is a intended as a compendium like our old MVP Deep Dives books.

So yes, I’ve seen much worse, but even with (just) six authors, this book does suffer from inconsistencies, particularly in coding style that I don’t think would have come from a single author. Just a few examples:

• I struggled with the way they slipped in and out of quoted identifiers, sometimes using [] around identifiers, sometimes ’’ and other times “”.
• There was completely inconsistent use of statement terminators.
• There was a weird mix of capitalization and formatting in queries.

There are also general tech editing issues like talking about “database scope credentials” instead of “database scoped credentials”. I also noticed spaces inadvertently inserted into the middle of object names in places where they shouldn’t be (like a space after a DB name and before the period that starts the schema name).

Improvements I’d like to see

I kept seeing things that while I’m glad the topics were there, I did wish they were done better. Here are some examples:

• Objects were created with single part names, and then later referred to with two-part names. This can lead to unreliable code, as the outcome depends upon who runs the code.
• I wish they hadn’t used three part table names so much.
• I really wish they’d used the modern system catalog views rather than the old information_schema views. They came out in 2005.
• I didn’t like see them using varchar constants in WHERE clauses for nvarchar columns. Similar issue with concatenating varchar and nvarchar literals and values inconsistently.
• I’m not in love with the FORMAT function as much as the authors, given the performance impacts I see from it all the time. It’s a .NET function and I’d rather use an intrinsic function wherever I can to replace it.
• There was a discussion that compares ISNULL and COALESCE yet it didn’t mention one of the core differences: the data type returned.
• CTEs were mentioned but I was suprised to see no discussion around potential misuse and/or performance problems, particularly given the name of the book. I was also surprised that more important use cases like multiple references to the same CTE weren’t covered.
• I’m not sure when most of the writing was done, but the section on window functions completely ignores the super-useful named windows that were added in 2022.
• There were a few places in the query plans and optimization chapter where I thought: “That’s not quite how that works”, particularly with things like how plans are chosen, the difference between estimated and actual plans, etc.
• The real limitations around filtered indexes aren’t discussed.
• I would like to have seen external tables discussed for purposes other than just reading files.

Summary

This book left me with a mix of feelings but I applaud the authors for tackling these topics in the first place.

5/10

2024-06-11