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.
Table of Contents (21 chapters)
1
Part 1: Refining Your Queries to Get the Results You Need
6
Part 2: Solving Complex Business and Data Problems in Your Queries
11
Part 3: Optimizing Your Queries to Improve Performance
14
Part 4: Working with Your Data on the Modern Data Platform

Understanding the value of creating views versus removing data

You have now learned how to create a query to get a result set that you can use for analysis and answer questions for a user. The next challenge is how you make this reusable so that you do not have to recreate the query every time you need the same data for other analyses. The reason for the challenge is that as the query gets more complex, the more likely the query is to be incorrectly typed. The solution to this challenge is to create a view. A view is a way to save the query as a logical table so that anybody with access to the database can run the query, and if you move on to another opportunity, the next person can recreate the result set with very little effort.

So, how do you create a view? It is as simple as adding the following line to the beginning of the SELECT query:

Create View 'name of the view' AS

Here is how the query that we created earlier would look to create a view of the data by adding the following line to the beginning of the SELECT query:

CREATE VIEW v_Backorders as
SELECT Year([Order Date Key]) as "Order Year",
       Month([Order Date Key]) as "Order Month",
  [Order Key] as "Order",
  [stock item key] as "Stock Item",
  [Customer Key] as "Customer",
  [WWI Order ID] as "WWI Order",
  [WWI Backorder ID] as "WWI Backorder"
FROM [WideWorldImportersDW].[Fact].[Order]
WHERE [WWI Backorder ID] IS NOT NULL;

Now you can run the analysis query as the following:

SELECT [Order Year],
       [Order Month],
   [Order],
   [Stock Item],
   [Customer],
   [WWI Order],
   [WWI Backorder]
FROM [dbo].[v_Backorders];

In Figure 1.5, you will notice that the following results are the same as you saw in the preceding result, and you do not have to include the filters because they are already included in the view:

Figure 1.5 – Result set using a view

Figure 1.5 – Result set using a view

This can save you the time of having to create the query in the future once the initial query has been created, and you can be assured that the data is correct. Most things that you can do in a query can also be done in a view, and you can use the view as though it is a table and just select columns from the view as you would in the table.

Now let’s look at how this filtering impacts any aggregations that you may plan to do with the result set.