Categories: Book Review

Book Review – SQL Query Design Patterns and Best Practices

Disclaimer: The publisher – Packt – sent me a free copy of the book for the intended purpose of a review. At the time of writing, the book is yet to be released (the date is set at the 22nd of March 2023).

The book SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns (that just rolls of the tongue, doesn’t it) is about a topic that’s quite dear to me: writing decent SQL. I’m not saying anything about performance, but I’m rather talking about “well-written” SQL; scripts that are written in such a way they are easy to understand by other people.

There are a whole bunch of authors for this book – six to be exact and they all seem to work for the same company – but fortunately the quality of the book doesn’t suffer from this large fellowship. Some books have so many different authors, it’s hard to find consistency (in writing and style) between the various chapters, but in this book I hardly noticed any difference between the chapters. So a job well done in that regard by the authors and their editors. Most of the authors seem to be working in the field of data analytics and business intelligence (so the same background as myself), which is really interesting as this book is written from a different viewpoint that most other SQL books. It’s not about hardcore performance tuning, indexing or writing very complex SQL stuff, but rather about how to write a query that satisfies the requirements of the business users.

The book itself consists of 13 chapters but most of them are quite short; the total length is about 270 pages, but pages with actual content are about 240. Which I guess is my main criticism: the book often doesn’t provide enough depth. The problem is that if the book would’ve provide more detail, it probably would’ve been over a 1,000 pages since many important topics are tackled.

The first part of the book – Refining Your Queries to Get the Results You Need – consists of 4 chapters who talk about how to write a SELECT statement, how to use a WHERE clause, how to aggregate data, how to format data and how to use certain functions like CASE, COALESCE and ISNULL. This part is really aimed at beginners; people who are just starting to write SQL. If you’re a more seasoned SQL developer, you can most likely skip those chapters.

The second part gets more interesting and talks about common table expressions, window functions, (UN)PIVOT and how security can influence the results of your query. For a book with “best practices” in the title, I was a bit ticked off that none of the SQL statements ends with the semicolon, but they do start each CTE with a semicolon. I know, this is one of those religious wars in SQL, like tabs vs spaces (and don’t get me started on aliases), but it’s called a statement terminator for a reason. The chapter on window functions was nice, albeit short, but it has some great examples. Like most topics in this book, each chapter can easily be a book on its own (like Itzik did with window functions). The security chapter was a bit weird in my opinion. It talks about how row-level-security can be the reason why you don’t get some data returned when you run your query, but it doesn’t really explain what RLS is and how it is set up.

Part 3 has only two chapters: one about execution plans and one about indexing. As with the rest of the book, they merely touch upon those topics. It would’ve been nice if more references for further learning was included (aside from the official Microsoft documentation).

The final part was the most interesting to me (as I was already familiar with all other topics). It talks about how to handle JSON data in SQL Server, the OPENROWSET function (for example to read Parquet data in Azure Synapse Serverless SQL Pools) and Jypiter notebooks in Azure Data Studio. I actually learned some stuff from those chapters.

Conclusion

The book starts with content for beginners, and gradually becomes “more complex” as it goes on, but there’s no real expert content in there. I would recommend this book to everyone starting out with SQL. I believe a book like this could have been beneficial to me at the start of my career 🙂

------------------------------------------------
Do you like this blog post? You can thank me by buying me a beer 🙂

Koen Verbeeck

Koen Verbeeck is a Microsoft Business Intelligence consultant at AE, helping clients to get insight in their data. Koen has a comprehensive knowledge of the SQL Server BI stack, with a particular love for Integration Services. He's also a speaker at various conferences.