SQL Query Design Patterns and Best Practices: A practical guide to writing readable and maintainable SQL queries using its design patterns

270 Pages·2023·14.715 MB·English

by  Steve Hughes, Dennis Neer, Dr. Ram Babu Singh, Shabbir H. Mala, Leslie Andrews, Chi Zhang

SQL Query Design Patterns and
Best Practices
A practical guide to writing readable and maintainable SQL
queries using its design patterns
Steve Hughes
Dennis Neer
Dr. Ram Babu Singh
Shabbir H. Mala
Leslie Andrews
Chi Zhang
BIRMINGHAM—MUMBAI
SQL Query Design Patterns and Best Practices
Copyright © 2023 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted
in any form or by any means, without the prior written permission of the publisher, except in the case
of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information
presented. However, the information contained in this book is sold without warranty, either express
or implied. Neither the authors, nor Packt Publishing or its dealers and distributors, will be held liable
for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and
products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot
guarantee the accuracy of this information.
Publishing Product Manager: Arindam Majumdar
Senior Editor: Tiksha Abhimanyu Lad
Technical Editor: Sweety Pagaria
Copy Editor: Safis Editing
Project Coordinator: Farheen Fathima
Proofreader: Safis Editing
Indexer: Sejal Dsilva
Production Designer: Arunkumar Govinda Bhat
Marketing Coordinator: Nivedita Singh
First published: February 2023
Production reference: 1240223
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham
B3 2PB, UK.
ISBN 978-1-83763-328-9
www.packtpub.com
Contributors
About the authors
Steve Hughes is a senior director of data and analytics at 3Cloud. In this role, he focuses on growing
the teams’ skills and capabilities to deliver data projects with Azure. He has worked with technology
for over 20 years, with much of that time spent on creating business intelligence (BI) solutions and
helping customers implement leading BI and cloud solutions. He is passionate about using data
effectively and helping customers understand that data is valuable and profitable. Steve has recently
been diagnosed with ALS but continues to work and share with others what he has learned. Steve
is also the founder of Data on Wheels where he blogs with one of his daughters on topics such as
data, analytics, and work enablement.
I want to thank my family, friends, and peers for supporting me through my health changes, and I am
especially thankful to Sheila, my wife. I could not do this without her support and encouragement.
Dennis Neer is a senior architect of data and analytics at 3Cloud. In this role, he focuses on working
with clients to design Azure solutions for their data and analytic needs so that they may use that
data to make business decisions based on that data. This includes using tools such as SQL Server
databases, Synapse, data lakes, and Power BI. He has worked with technology for over 30 years, with
25 years in designing and building database and visualization solutions. He is passionate about data
and helping businesses to understand the information contained in their data and how it can be
used to make important decisions regarding their business to improve the strength of their business.
I want to thank my wife, Jeanie, for all of the support and encouragement she has given me throughout
my career, and while working on this book; I couldn’t do this without you.
Dr. Ram Babu Singh is a Microsoft Certified Professional with a PhD in computer science. He
is a lead architect of data and analytics at 3Cloud, using complex data analytics and data science
solutions. He has over 20 years of experience in data science and data platforms and over 10 years
of leadership experience leading data science and data platform solutions for the world’s top IT
consulting companies. He has been published in international journals and has a patent in his name.
I want to thank my wife, Dr. Nirmal Lodhi, for supporting me throughout my journey, and my son
Rudra and daughter Riddhima for letting me steal their family time. I also want to thank my colleagues
for their support.
Shabbir H. Mala is a director of data and analytics at 3Cloud. He has over 23 years of experience
in thought leadership and consulting, developing complex data solutions, business systems, and
processes using Azure Data Services and Power BI. He currently manages a data architecture team
of over 40 principals and senior architects focusing on business growth, successful project delivery,
and client experience. He loves and has done talks at Power BI conferences and local Power BI user
groups. He has been awarded Microsoft FastTrack Solution Architect for the last 2 consecutive
years, in 2021 and 2022. He was born and raised in Mumbai (India), and came to the United States
in 2006 and is currently living in Chicago. He is married with three kids.
Leslie Andrews is a lead data architect of data and analytics at 3Cloud. Working in the IT sector
for over 20 years in local government, electrical supply, law enforcement, and healthcare, she has
broad experience to draw from to assist clients in making data more accessible for end users. Leslie’s
expertise includes SQL development, ETL, data warehousing, data modeling, and analytics. Leslie
currently holds several Microsoft certifications related to Azure technologies for data engineering.
She approaches each day as an opportunity to use data and technology to make it easier for others
to do their daily work. Leslie has been doing public speaking since 2015, was an IDERA ACE in
2019, and is a supporter of and contributor to the SQL community.
Chi Zhang is a lead data architect at 3Cloud. After completing her master’s degree at Carnegie Mellon
University, she worked in data consulting for the past 5 years. She has helped clients from various
industries to deliver customized data solutions within Azure. Focusing on ETL, data warehousing,
data modeling, and BI reporting, Chi solves data problems for clients and builds data solutions that
provide business users with better operational insights and a smoother experience. Recently, she
has been continued to grow her footprint in the Azure community: earning certifications in Azure
data engineering and data science, giving her first public tech talk, co-authoring a technical book,
and becoming an active contributor to the SQL community.
About the reviewers
Paul Turley is a competency lead director for the 3Cloud solutions BI&A practice, and Microsoft
Data Platform MVP. He consults, writes, speaks, teaches, and blogs about BI and reporting solutions.
He works with companies to model data and visualize and deliver critical information to make
informed business decisions, using the Microsoft Data Platform and business analytics tools. He
is the director of the Oregon Data Community PASS chapter and user group, the author and lead
author of Professional SQL Server 2016 Reporting Services, and 14 other titles from Wrox & Microsoft
Press. Paul is a FastTrack Recognized Solution Architect and holds several certifications related to
the Microsoft Data Platform and BI.
Christopher J Knapp is a cloud solutions director at 3Cloud. For the last 17 years, he has worked
primarily in the healthcare industry in all aspects of data, including transformation, storage, data
modeling, BI, advanced analytics, and machine learning. He has spoken in multiple settings across
the nation on various data topics. More recently, CJ has focused on Spark-based ETL and analytical
methods, as well as cloud-based data platform systems at scale for healthcare. He is a father, husband,
polyglot, and constant consumer of culture outside of work. He spends the majority of his free time
having adventures with his family, traveling, and mixing craft cocktails at home. Sometimes, he
even manages to mix them well.
Andie Letourneau is a cloud solution consultant with over 25 years of experience specializing in
MS SQL Server databases, data warehousing, and BI. She has Azure Data Fundamentals and Azure
Administrator Associate Microsoft certifications. She has presented at numerous SQL user group
meetings and SQL Saturday events. She lives with her husband in beautiful Cave Creek, AZ.

Table of Contents
Preface xiii
Part 1: Refining Your Queries to Get the Results
You Need
1
Reducing Rows and Columns in Your Result Sets 3
Technical requirements 3 Understanding the value of creating
Identifying data to remove from views versus removing data 9
the dataset 4 Exploring the impact of row and
column reductions on aggregations 10
Reducing the amount of data 5
Summary 15
2
Efficiently Aggregating Data 17
Technical requirements 17 The MIN() Function 25
Identifying data to be aggregated 18 Improving performance when
Determining when data should be aggregating data 26
aggregated 19 Summary 29
The AVG() function 22
The SUM() function 23
The COUNT() function 24
The MAX() function 25
viii Table of Contents
3
Formatting Your Results for Easier Consumption 31
Technical requirements 32 Formatting dates and numbers with
CONVERT() and CAST() 37
Using the FORMAT() function 32
Formatting numbers with ROUND()
Format() with culture 32
and CEILING() 40
Format() with custom formatting strings 34
Comparing FORMAT(), CONVERT(), and
Formatting dates and numbers CAST() 42
with functions 37
Alias columns with
meaningful names 42
Summary 43
4
Manipulating Data Results Using Conditional SQL 45
Technical requirements 45 Using the COALESCE() expression 55
Using the CASE statement 46 How to use COALESCE() 55
Using a simple CASE expression in Comparing COALESCE() and CASE() 58
a SELECT statement 46
Using ISNULL() function 58
Using a searched CASE expression in a
How to use ISNULL() 58
SELECT statement 47
Comparing ISNULL() and COALESCE() 60
Using CASE in an ORDER BY statement 47
Using CASE in an UPDATE statement 50 Summary 61
Using CASE in a HAVING statement 52
Part 2: Solving Complex Business and Data
Problems in Your Queries
5
Using Common Table Expressions 65
Technical requirements 65 Set theory for queries 67
Creating CTEs 66 Creating a more complex CTE 68
Table of Contents ix
Creating a recursive CTE 70 Recursive alternative 73
Creating the hierarchical data 71
Summary 75
Creating the recursive CTE 72
6
Analyze Your Data Using Window Functions 77
Technical requirements 77 Scenarios and business problems 84
Understanding window functions 78 Days between orders 85
Using a window function in a query 78 Finding a pattern 87
Adding a partition to the results 82 Finding first N records of every group 89
Window functions with frames 83 Running totals 90
First and last record in the partition 91
Year-over-year growth 92
7
Reshaping Data with Advanced Techniques 95
Technical requirements 95 Working with the UNPIVOT
Working with the PIVOT operator 96 operator 103
Understanding hierarchical data 105
Using PIVOT dynamically 100
Summary 109
8
Impact of SQL Server Security on Query Results 111
Technical requirements 111 Understanding SQL Server security 113
Why is data missing from my Validating security settings 114
result set? 112 Summary 116