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

Reducing Rows and Columns in Your Result Sets

Today the sources of data that a data analyst has access to have grown to the point that the amount of data that is available to you is unlimited. The challenge that a data analyst faces today is to determine how to generate a result set that is manageable and has the information that ensures that it will meet the needs of the analyst for their reports and analysis. If there is too much data, the result set will become unmanageable and unusable due to information overload; too little data and the data will have gaps, and the end user will lose trust in the data.

In this chapter, we will review how you determine how much data and what data you should keep in your result set and how to filter that data efficiently. We will also review how to determine which columns you should keep and how you can efficiently select the correct columns. The chapter will then wrap up with a short discussion on how these activities will impact future data aggregations.

By the end of this chapter, you will understand how to identify the data and columns that you need and the most efficient method for getting the data into a usable result set that can easily be recreated.

In this chapter, we will cover the following main topics:

  • Identifying data to be removed from the dataset
  • Understanding the value of creating views versus removing data
  • Exploring the impact of row and column reductions on aggregations
Previous Section
End of Section 1
Next Section