SQL Query Design Patterns and Best Practices
Article Index
SQL Query Design Patterns and Best Practices
Chapters 5 - 10
Chapter 11 to end; Conclusion

Author: Steve Hughes et al
Publisher: Packt Publishing
Pages: 270
ISBN: 978-1837633289
Print: 1837633282
Kindle: B0BWRD7HQ7
Audience: Query writers
Rating: 2.5
Reviewer: Ian Stirk

This book aims to improve your SQL queries using design patterns, how does it fare? 

The book contains a wide range of topics, supposedly held together by design patterns and best practices. Design patterns are template solutions to common problems.

The target audience is “…SQL developers, data analysts, report writers, data scientists, and other data gatherers…”. Most of the chapters are introductory, so are suitable for new or intermediate-level SQL Server users.

Below is a chapter-by-chapter exploration of the topics covered.


Chapter 1: Reducing Rows and Columns in Your Result Sets 

The book opens with the premise that with ever increasing amounts of data, now more than ever we need more manageable datasets, containing only the data we need. Details of the data required can be garnered by interviewing the relevant users, followed with some analysis of the data tables and columns (e.g. SELECT TOP (100) * FROM information_schema.columns can be used to identify relevant columns and the tables they relate to). Where possible, we should only get the columns and rows we need, reducing the amount of data returned and hopefully improving performance. Next, the chapter looks at creating a view, so that the query can be persisted. The impact of reduced data on aggregations is explored.

The content of this chapter suggests the book is really an introduction to querying SQL Server. There’s some useful template SQL code that might be useful in your own work. While the chapter contains some useful basic advice, it also contained some leaps of faith (one query uses COUNT and GROUP BY but these are not discussed - they are explained in the next chapter). Additionally, getting details of what users want is rather glossed over – which may be ok for an introductory book. I suspect the title of this book is wrong, perhaps “An introduction to querying SQL Server” might be better.

The chapter is generally easy to read, with useful example code to illustrate the concepts discussed. There’s a good flow through the chapter, with later ideas building on earlier examples. These traits apply to the whole of the book.

Chapter 2: Efficiently Aggregating Data 

Data is often aggregated to provide summarised data that can then be investigated for patterns, trends, and insights. The importance of the GROUP BY clause is explained with examples. The value of data granularity on aggregation is noted. The main common aggregation functions are explained (i.e. AVG, SUM, COUNT, MAX, MIN), with brief examples. There’s a helpful link to the Microsoft documentation on aggregation functions. Lastly, the chapter looks at using a subquery to improve performance. I’ve often found this to be suboptimal, instead it may be better to put the results of the subquery into a temp table, then join this temp table back into the main body of the parent query.

Overall, a useful chapter explaining the basics of aggregation functions.

Chapter 3: Formatting Your Results for Easier Consumption 

Data is often formatted to make it more readable. The versatile FORMAT function is discussed with various examples. The use of the culture parameter is illustrated, showing dates in different formats (e.g. British English). Formatted strings can be used to format the data into a given template, this is shown with dates and numbers. Other common formatting functions are then briefly discussed, including: CONVERT, CAST, and ROUND. Example code is provided throughout. A useful comparison between the various formatting functions is provided. The chapter ends with a very brief look at using aliases to provide more meaningful names for the columns output.

Overall, a useful, if basic introduction to formatting SQL output.

Chapter 4: Manipulating Data Results Using Conditional SQL 

Various conditional statements (e.g. CASE) can be used to evaluate data and dictate what is output. Most of the chapter concentrates on the CASE statement, showing examples of various complexity (e.g. single and multiple conditions). More complex examples look at the CASE used in an ORDER BY, UPDATE, and HAVING clauses. Useful example code is provided. Next, there’s a look at the COALESCE function (it returns the first non-null value in a list), and the ISNULL function (it returns the first non-null value in a list of 2 items).  Various comparisons are made between these conditional SQL functions, together with usage recommendations.

Another useful chapter on a helpful SQL statement. I do wonder why the focus is narrow – why are IF/ELSE not discussed?!


Last Updated ( Tuesday, 18 July 2023 )