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

Cover Page....2 Table of Contents....3 Preface....5 Part 1: Refining Your Queries to Get the Results You Need....15 Chapter 1: Reducing Rows and Columns in Your Result Sets....16 Technical requirements....16 Identifying data to remove from the dataset....17 Understanding the value of creating views versus removing data....23 Exploring the impact of row and column reductions on aggregations....26 Summary....31 Chapter 2: Efficiently Aggregating Data....32 Technical requirements....32 Identifying data to be aggregated....33 Determining when data should be aggregated....35 Improving performance when aggregating data....44 Summary....47 Chapter 3: Formatting Your Results for Easier Consumption....49 Technical requirements....50 Formatting dates and numbers with functions....57 Alias columns with meaningful names....64 Summary....65 Chapter 4: Manipulating Data Results Using Conditional SQL....67 Technical requirements....67 Using the CASE statement....68 Using the COALESCE() expression....80 Using ISNULL() function....84 Summary....87 Part 2: Solving Complex Business and Data Problems in Your Queries....89 Chapter 5: Using Common Table Expressions....90 Technical requirements....90 Creating CTEs....91 Creating a recursive CTE....97 Summary....102 Chapter 6: Analyze Your Data Using Window Functions....103 Technical requirements....103 Understanding window functions....104 Scenarios and business problems....115 Chapter 7: Reshaping Data with Advanced Techniques....128 Technical requirements....128 Working with the PIVOT operator....129 Working with the UNPIVOT operator....136 Understanding hierarchical data....139 Summary....143 Chapter 8: Impact of SQL Server Security on Query Results....145 Technical requirements....145 Why is data missing from my result set?....146 Understanding SQL Server security....148 Validating security settings....149 Summary....153 Part 3: Optimizing Your Queries to Improve Performance....154 Chapter 9: Understanding Query Plans....155 Technical requirements....155 Understanding query optimization techniques....156 Understanding the Query Optimizer....156 Understanding and exploring the Query Execution Plan....157 Summary....180 Chapter 10: Understanding the Impact of Indexes on Query Design....182 Technical requirements....182 Understanding index and index types....183 Using CREATE to create an index....183 Exploring the types of indexes....190 The impact of indexes on query design....197 Understanding index maintenance....201 Summary....205 Further reading....205 Part 4: Working with Your Data on the Modern Data Platform....206 Chapter 11: Handling JSON Data in SQL Server....207 Technical requirements....207 Introducing JSON functionality built into SQL Server....208 JSON path in SQL Server....212 Formatting results as JSON in SQL Server....213 Working with JSON data in SQL queries....224 Building tabular results from JSON data in SQL Server....231 Summary....234 Chapter 12: Integrating File Data and Data Lake Content with SQL....235 Technical requirements....235 Understanding the OPENROWSET (BULK..) function....236 Required security and storage permissions....244 Understanding external tables....247 Summary....253 Chapter 13: Organizing and Sharing Your Queries with Jupyter Notebooks....255 Technical requirements....255 Creating Jupyter notebooks in Azure Data Studio....256 Adding queries in your Jupyter notebooks....264 Documenting your code with markdown in your notebooks....275 Managing and sharing your queries with Jupyter notebooks....280 Summary....284 Index....285 Why subscribe?....301 Other Books You May Enjoy....303 Packt is searching for authors like you....307 Share Your Thoughts....307 Download a free PDF copy of this book....308 Appendix: Preparing Your Environment....310