book

SQL Query Design Patterns and Best Practices

Name: SQL Query Design Patterns and Best Practices
ISBN: 9781837633289

by Steven Hughes, Dennis Neer, Ram Babu Singh, Shabbir Mala, Leslie Andrews, Chi Zhang

March 2023

Intermediate to advanced

270 pages

5h 51m

English

Packt Publishing

Read now

Unlock full access

SQL Query Design Patterns and Best Practices
ContributorsAbout the authorsAbout the reviewers
Preface
Who this book is forWhat this book coversTo get the most out of this bookDownload the example code filesDownload the color imagesGet in touchDownload a free PDF copy of this book
Part 1: Refining Your Queries to Get the Results You Need
Chapter 1: Reducing Rows and Columns in Your Result Sets
Technical requirementsIdentifying data to remove from the datasetReducing the amount of dataUnderstanding the value of creating views versus removing dataExploring the impact of row and column reductions on aggregationsSummary
Chapter 2: Efficiently Aggregating Data
Technical requirementsIdentifying data to be aggregatedDetermining when data should be aggregatedThe AVG() functionThe SUM() functionThe COUNT() functionThe MAX() functionThe MIN() FunctionImproving performance when aggregating dataSummary
Chapter 3: Formatting Your Results for Easier Consumption
Technical requirementsUsing the FORMAT() functionFormat() with cultureFormat() with custom formatting stringsFormatting dates and numbers with functionsFormatting dates and numbers with CONVERT() and CAST()Formatting numbers with ROUND() and CEILING()Comparing FORMAT(), CONVERT(), and CAST()Alias columns with meaningful namesSummary
Chapter 4: Manipulating Data Results Using Conditional SQL
Technical requirementsUsing the CASE statementUsing a simple CASE expression in a SELECT statementUsing a searched CASE expression in a SELECT statementUsing CASE in an ORDER BY statementUsing CASE in an UPDATE statementUsing CASE in a HAVING statementUsing the COALESCE() expressionHow to use COALESCE()Comparing COALESCE() and CASE()Using ISNULL() functionHow to use ISNULL()Comparing ISNULL() and COALESCE()Summary
Part 2: Solving Complex Business and Data Problems in Your Queries
Chapter 5: Using Common Table Expressions
Technical requirementsCreating CTEsSet theory for queriesCreating a more complex CTECreating a recursive CTECreating the hierarchical dataCreating the recursive CTERecursive alternativeSummary
Chapter 6: Analyze Your Data Using Window Functions
Technical requirementsUnderstanding window functionsUsing a window function in a queryAdding a partition to the resultsWindow functions with framesScenarios and business problemsDays between ordersFinding a patternFinding first N records of every groupRunning totalsFirst and last record in the partitionYear-over-year growth

Chapter 7: Reshaping Data with Advanced Techniques
Technical requirementsWorking with the PIVOT operatorUsing PIVOT dynamicallyWorking with the UNPIVOT operatorUnderstanding hierarchical dataSummary
Chapter 8: Impact of SQL Server Security on Query Results
Technical requirementsWhy is data missing from my result set?Understanding SQL Server securityValidating security settingsSummary
Part 3: Optimizing Your Queries to Improve Performance
Chapter 9: Understanding Query Plans
Technical requirementsUnderstanding query optimization techniquesUnderstanding the Query OptimizerUnderstanding and exploring the Query Execution PlanComparing execution plansAnalyzing the query planSummary
Chapter 10: Understanding the Impact of Indexes on Query Design
Technical requirementsUnderstanding index and index typesUsing CREATE to create an indexThe DROP indexUsing RENAME to rename an indexModifying indexesThe ALTER indexExploring the types of indexesClustered indexesThe NONCLUSTERED indexThe UNIQUE indexThe filtered indexThe covering indexThe impact of indexes on query designUnderstanding index maintenanceThe REBUILD indexThe REORGANIZE indexCautionSummaryFurther reading
Part 4: Working with Your Data on the Modern Data Platform
Chapter 11: Handling JSON Data in SQL Server
Technical requirementsIntroducing JSON functionality built into SQL ServerThe basics of JSONJSON functions in SQL ServerJSON as data in SQL ServerJSON path in SQL ServerFormatting results as JSON in SQL ServerUsing FOR JSONFilling the tableWorking with JSON data in SQL queriesDiscovering JSON data in SQL ServerExtracting JSON data in SQL ServerModifying JSON data in SQL ServerBuilding tabular results from JSON data in SQL ServerSummary
Chapter 12: Integrating File Data and Data Lake Content with SQL
Technical requirementsUnderstanding the OPENROWSET (BULK..) functionRequired security and storage permissionsUnderstanding external tablesSummary
Chapter 13: Organizing and Sharing Your Queries with Jupyter Notebooks
Technical requirementsCreating Jupyter notebooks in Azure Data StudioUnderstanding notebooks in Azure Data StudioCreating a notebook in Azure Data StudioCreating the Jupyter bookAdding a notebook to your Jupyter bookAdding queries in your Jupyter notebooksCreating connections in your notebookCreating and executing SQL code in your notebookFormatting resultsExporting resultsResetting resultsDocumenting your code with markdown in your notebooksAdding a text block to your notebookCreating a header for your notebookUsing basic markdown for documentationAdding images and links to your documentationManaging and sharing your queries with Jupyter notebooksSimple sharing of Jupyter books and notebooksUsing GitHub to collaborate on your notebookIntegrating third-party Jupyter booksSummary
Appendix: Preparing Your Environment
Prerequisites for running the exercisesChoosing a platformSetting up the Azure environmentSetting up the SQL Server developer environment
Index
Why subscribe?
Other Books You May EnjoyPackt is searching for authors like youShare Your ThoughtsDownload a free PDF copy of this book

Content preview from SQL Query Design Patterns and Best Practices

Appendix

Preparing Your Environment

As part of the Appendix, we will be setting up the technical prerequisites for this book. This includes several tools that’ll be used throughout this book to move data and explore queries. It is recommended that you install the following tools on the device as these will be used with the exercises in this book:

SQL Server Management Studio (SSMS) and Azure Data Studio. You can find the installation instructions at https://learn.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms. Be sure to install both SQL Server Management Studio and Azure Data Studio.
Microsoft Azure Storage Explorer is used to move files in and out of Azure. You’ll find the installation at https://azure.microsoft.com/en-us/products/storage/storage-explorer/. ...