What this book covers
The book is organized into four parts, with multiple chapters in each part. The book is designed to grow your experience from beginning to end if you choose to read it in that fashion. One of the greatest values is that each chapter is self-standing and can be used as a reference if you come across a situation where you need to improve your query writing specifically with that scenario. This also allows you to engage this book at whichever point makes the most sense for your query writing capabilities and skills.
In Chapter 1, Reducing Rows and Columns in Your Result Sets, the focus is on reducing your rows and columns to build an efficient dataset that can be used in reporting and other use cases.
The focus shifts to aggregating your results in Chapter 2, Efficiently Aggregating Data in Your Results. You can return a much more refined dataset that is easier to consume by effectively aggregating results to the right granularity in the content you need to pass along.
In Chapter 3, Formatting Your Results for Easier Consumption, the focus shifts to formatting your results. Often when working with data coming from a database, it must support levels of granularity or specific types of data to be efficient or inclusive. However, this comes at the price of storing data in a format that is not necessarily conducive to end users. Common formatting difficulties include handling currencies and dates. In this chapter, you will learn how to effectively format your data for more efficient use outside the database.
We expand on query efficiency by using conditional SQL in Chapter 4, Manipulating Your Data Results Using Conditional SQL. This will allow you to refine results even further for your end user. SQL Server supports several conditional logic statements that will be explored in this chapter.
Chapter 5, Using Common Table Expressions, delves into the world of common table expressions (CTEs) as they are commonly referred to. This technique is used to reduce subqueries and support other complex scenarios. In this chapter, you will learn how to build efficient CTEs to solve complex business problems.
Chapter 6, Analyze Your Data Using Window Functions, introduces you to window functions inside SQL. These functions allow you to do inline query aggregations or other techniques, such as row numbers. Some of the problems that are solved using window functions include building and running totals in your results.
Chapter 7, Reshaping Your Data with Advanced Techniques, dives into advanced techniques for reshaping your data. This includes handling hierarchical data and working with the PIVOT and UNPIVOT commands.
Chapter 8, Impact of SQL Security on Query Results, helps you deal with the impact of security on your query results. Citizen developers are often unaware of the impact security might have on the data they are pulling into their results. This chapter looks at the various implications of security and how to understand that impact on the results that you get and deliver to your end user.
Chapter 9, Understanding Query Plans, describes how to understand query plans. In this chapter, you will be introduced to the query plan itself and how to read it to determine what you can do to improve the performance and the query that you’re writing.
We then move on to understanding the impact of indexes on your query design in Chapter 10, Understanding the Impact of Indexes on Your Query Design. While we will not dive into authoring indexes, understanding the impact of indexes, including which indexes will improve your query performance, is the focus here. This will help you communicate your index needs to those that own the database design.
JSON data or NoSQL data has been disruptive to SQL writers around the world. In Chapter 11, Handling JSON Data in SQL Server, we will walk through the various functions and capabilities of SQL in SQL Server that supports JSON.
In Chapter 12, Integrating File Data and Data Lake Content with SQL, we will walk through techniques to integrate file and data lake content in your queries. This more complex technique is becoming very necessary in the new modern data platform.
We then have Chapter 13, Organizing and Sharing Your Queries with Jupyter Notebooks, covering Jupyter notebooks. Jupyter notebooks are available in Azure Data Studio and Synapse Workspace. These notebooks can be used to organize and share queries and their results more efficiently than SQL Server Management Studio (SSMS). In this chapter, we will walk you through notebook creation and sharing.
In the Appendix, Preparing Your Environment, we will walk you through setting up your environment to support the exercises in this book.
