Book Review: SQL Query Design Patterns and Best Practices

Over the years I have been to different organizations that have their own conventions in their business practices. Codes are written and maintained in certain ways that are compatible with how they operate. These organizations would have written conventions for specific business functions while others maintain theirs through unwritten rule-of-thumbs, and some are even left to their own devices to come up with their own rules. The latter oftentimes lead to technical debt, but that’s another topic for another day. Whether documented in official artifacts or kept by oral traditions and heuristics, design patterns and best practices are ingrained in all the teams’ customs and practices.

SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns (yes, that’s the complete title of the book) will provide you with a good foundation upon which a consistent convention for practicing your day-to-day operations can have a solid footing in your database discipline, with few exceptions. Read on.

This book is not perfect to say the least. Some glaring inconsistencies in the way the codes were written have already been talked about in Koen Verbeeck’s review of the same book (review here). I am not going to rehash those here. Let me say, though, that those points raised by Koen are all valid and should be corrected in future edition of the book.

That said, let’s take a peek at what this book has to offer.

The book is divided into 4 parts:

• Part 1 – Refining Your Queries to Get the Results You Need
• Part 2 – Solving Complex Business and Data Problems in Your Queries
• Part 3 – Optimizing Your Queries to Improve Performance
• Part 4 – Working with Your Data on the Modern Data Platform

As you can see in the list above, this book pretty much covers a wide range of topics that include data retrieval, troubleshooting, query performance, and data platform. That’s a fair coverage if you want to talk about design patterns and best practices in general; but it will take a great deal of effort to jam-pack all these great topics into a 270-page volume. The content covers the topics it presented quite fairly if you don’t set your expectations too high. It is just not possible to cover all these topics in depth within that limited amount of pages.

If you’re new to the industry, this is a good book to have in your arsenal. I would pick this as a reference if I were establishing conventions from scratch.

I love the fact that the very first chapter talks about Reducing Rows and Columns in Your Result Sets. Sometimes even the best codes can perform poorly if the volume of the data being processed is more than what the system resources can handle within a reasonable amount of time. Trust me, you don’t need a billion-row dataset in your presentation layer.

For some this book will seem like it’s a primer on TSQL. For the most part, it really looks like a primer rather than an operative manual that the title presupposes; and there are better primer materials out there for such purposes.

This, then, begs the question: who is this book for? If you are just starting out in the database industry and SQL Server is your primary tool of the trade, this is a good book for refreshing your TSQL skills. If you are a well-established data professional who already built your company’s database conventions from the ground up, you can skip this book.

Don’t get me wrong. I’d recommend this book if you need a starter in TSQL. This is a good companion to our well-beloved volumes such as Itzik Ben-Gan’s T-SQL Fundamentals (Developer Reference) or Pedro Lopes and Pam Lahoud’s Learn T-SQL Querying: A guide to developing efficient and elegant T-SQL code (these two books should already be in your tech library).