Book Image

SQL Query Design Patterns and Best Practices

By : Steve Hughes, Dennis Neer, Dr. Ram Babu Singh, Shabbir H. Mala, Leslie Andrews, Chi Zhang
5 (1)
Book Image

SQL Query Design Patterns and Best Practices

5 (1)
By: Steve Hughes, Dennis Neer, Dr. Ram Babu Singh, Shabbir H. Mala, Leslie Andrews, Chi Zhang

Overview of this book

SQL has been the de facto standard when interacting with databases for decades and shows no signs of going away. Through the years, report developers or data wranglers have had to learn SQL on the fly to meet the business needs, so if you are someone who needs to write queries, SQL Query Design and Pattern Best Practices is for you. This book will guide you through making efficient SQL queries by reducing set sizes for effective results. You’ll learn how to format your results to make them easier to consume at their destination. From there, the book will take you through solving complex business problems using more advanced techniques, such as common table expressions and window functions, and advance to uncovering issues resulting from security in the underlying dataset. Armed with this knowledge, you’ll have a foundation for building queries and be ready to shift focus to using tools, such as query plans and indexes, to optimize those queries. The book will go over the modern data estate, which includes data lakes and JSON data, and wrap up with a brief on how to use Jupyter notebooks in your SQL journey. By the end of this SQL book, you’ll be able to make efficient SQL queries that will improve your report writing and the overall SQL experience.

Related Content you might be interested in

Current Title:

Small book image
SQL Query Design Patterns and Best Practices
Steve Hughes | Dennis Neer | Dr. Ram Babu Singh | Shabbir H. Mala | Leslie Andrews | Chi Zhang
Mar, 2023 | 270 pages
Small book image
Learn T-SQL Querying
Pam Lahoud | Pedro Lopes
Feb, 2024 | 456 pages
Small book image
Hands-On SQL Server 2019 Analysis Services
Steve Hughes
Oct, 2020 | 474 pages
Small book image
Learn T-SQL Querying
Pam Lahoud | Pedro Lopes
May, 2019 | 484 pages
Table of Contents (21 chapters)
Preface
Preface
Who this book is for
What this book covers
To get the most out of this book
Download the example code files
Download the color images
Get in touch
Download a free PDF copy of this book
1
Part 1: Refining Your Queries to Get the Results You Need
Part 1: Refining Your Queries to Get the Results You Need
Free Chapter
2
Chapter 1: Reducing Rows and Columns in Your Result Sets
Chapter 1: Reducing Rows and Columns in Your Result Sets
Technical requirements
Identifying data to remove from the dataset
Understanding the value of creating views versus removing data
Exploring the impact of row and column reductions on aggregations
Summary
3
Chapter 2: Efficiently Aggregating Data
Chapter 2: Efficiently Aggregating Data
Technical requirements
Identifying data to be aggregated
Determining when data should be aggregated
Improving performance when aggregating data
Summary
4
Chapter 3: Formatting Your Results for Easier Consumption
Chapter 3: Formatting Your Results for Easier Consumption
Technical requirements
Formatting dates and numbers with functions
Alias columns with meaningful names
Summary
5
Chapter 4: Manipulating Data Results Using Conditional SQL
Chapter 4: Manipulating Data Results Using Conditional SQL
Technical requirements
Using the CASE statement
Using the COALESCE() expression
Using ISNULL() function
Summary
6
Part 2: Solving Complex Business and Data Problems in Your Queries
Part 2: Solving Complex Business and Data Problems in Your Queries
7
Chapter 5: Using Common Table Expressions
Chapter 5: Using Common Table Expressions
Technical requirements
Creating CTEs
Creating a recursive CTE
Summary
8
Chapter 6: Analyze Your Data Using Window Functions
Chapter 6: Analyze Your Data Using Window Functions
Technical requirements
Understanding window functions
Scenarios and business problems
9
Chapter 7: Reshaping Data with Advanced Techniques
Chapter 7: Reshaping Data with Advanced Techniques
Technical requirements
Working with the PIVOT operator
Working with the UNPIVOT operator
Understanding hierarchical data
Summary
10
Chapter 8: Impact of SQL Server Security on Query Results
Chapter 8: Impact of SQL Server Security on Query Results
Technical requirements
Why is data missing from my result set?
Understanding SQL Server security
Validating security settings
Summary
11
Part 3: Optimizing Your Queries to Improve Performance
Part 3: Optimizing Your Queries to Improve Performance
12
Chapter 9: Understanding Query Plans
Chapter 9: Understanding Query Plans
Technical requirements
Understanding query optimization techniques
Understanding the Query Optimizer
Understanding and exploring the Query Execution Plan
Summary
13
Chapter 10: Understanding the Impact of Indexes on Query Design
Chapter 10: Understanding the Impact of Indexes on Query Design
Technical requirements
Understanding index and index types
Using CREATE to create an index
Exploring the types of indexes
The impact of indexes on query design
Understanding index maintenance
Summary
Further reading
14
Part 4: Working with Your Data on the Modern Data Platform
Part 4: Working with Your Data on the Modern Data Platform
15
Chapter 11: Handling JSON Data in SQL Server
Chapter 11: Handling JSON Data in SQL Server
Technical requirements
Introducing JSON functionality built into SQL Server
JSON path in SQL Server
Formatting results as JSON in SQL Server
Working with JSON data in SQL queries
Building tabular results from JSON data in SQL Server
Summary
16
Chapter 12: Integrating File Data and Data Lake Content with SQL
Chapter 12: Integrating File Data and Data Lake Content with SQL
Technical requirements
Understanding the OPENROWSET (BULK..) function
Required security and storage permissions
Understanding external tables
Summary
17
Chapter 13: Organizing and Sharing Your Queries with Jupyter Notebooks
Chapter 13: Organizing and Sharing Your Queries with Jupyter Notebooks
Technical requirements
Creating Jupyter notebooks in Azure Data Studio
Adding queries in your Jupyter notebooks
Documenting your code with markdown in your notebooks
Managing and sharing your queries with Jupyter notebooks
Summary
18
Index
Index
Why subscribe?
19
Other Books You May Enjoy
Other Books You May Enjoy
Packt is searching for authors like you
Share Your Thoughts
Download a free PDF copy of this book
Appendix: Preparing Your Environment
Appendix: Preparing Your Environment
Prerequisites for running the exercises

To get the most out of this book

Software/hardware covered in the book

Operating system requirements

Microsoft Azure Storage Explorer

Windows, macOS, or Linux

SQL Server 2022 or Azure SQL Database

Windows, macOS, or Linux

Azure Data Studio

Windows, macOS, or Linux

This book covers many illustrations and examples of working with SQL to improve your overall writing and performance. To make this simple for all our users, we have chosen to use the Wide World Importers sample databases available from Microsoft. Therefore, depending on the nature of the query that we are constructing and providing examples for, one of the two databases (operational: WideWorldImporters; data warehouse: WideWorldImportersDW) provided by Microsoft will be used. Bear in that means you will have two databases to run the exercises created within this book.

It is recommended that you install the following tools on your device, as these will be used with the exercises in this book:

If you are using the digital version of this book, we advise you to type the code yourself or access the code from the book’s GitHub repository (a link is available in the next section). Doing so will help you avoid any potential errors related to the copying and pasting of code.

Full instructions for installation and database configuration can be found in the Appendix.

Previous Section
Next Section