Advanced Querying Techniques in SQL Server
SQL Server’s data engine traditionally stores relational data but it can also be used to store data in non-relational schemas. You’ll learn about one example -- Entity, Attribute, Value (EAV).
What you'll learn
SQL Server’s Transact-SQL language has the ability to transform relational data in a wide variety of ways. In this course, Advanced Querying Techniques in SQL Server, you’ll gain the ability to solve four important problems using SQL Server. First, you’ll learn how to navigate hierarchical data using recursive common table expressions (CTEs). Second, you’ll explore how to rotate tabular data while aggregating desired metrics using PIVOT. Next, you’ll discover how to transform cross tabulated data into relational data using UNPIVOT. Then, you’ll see how to use semantic data modeling and Entry, Attribute, and Value data structures when a purely relational model limits flexibility and attributes can change frequently. Finally, you'll learn how to sample SQL Server data for analysis and testing. When you’re finished with this course, you’ll have the skills and knowledge of advanced SQL Server querying techniques needed to quickly address the problems presented here.
Table of contents
- Introduction to Recursive Queries 2m
- Using the WITH Statement 3m
- Demo: Iterative Queries Using the WITH Statement 2m
- Writing Recursive Transact-SQL 4m
- Demo: Simple Recursive Queries 5m
- T-SQL Restrictions for Recursive Queries 1m
- Demo: Navigating a Data Hierarchy Using Recursion in T-SQL 3m
- Module Summary 2m
- Introduction to PIVOT and UNPIVOT 2m
- Demo: Pivoting in a Spreadsheet 2m
- Demo: Pivoting in Transact-SQL Using Cross-tabulation 2m
- Understanding PIVOT Syntax 2m
- Demo: Using PIVOT in Transact-SQL 2m
- Demo: Pivoting on Multiple Columns 2m
- Demo: Building a Dynamic PIVOT Query 3m
- Understanding UNPIVOT Syntax 2m
- Demo: Using UNPIVOT and CROSS APPLY 3m
- Module Summary 1m
- Introduction to the EAV Model 1m
- Demo: Handling Sparse Data Using a Traditional Approach 2m
- Demo: Refactoring into an EAV Model 2m
- Demo: Factoring out Attributes Using Foreign Keys 2m
- Recapping Progress and Issues 2m
- Demo: Handling Types and Constraints 3m
- Demo: Implementing Dynamic Constraints 2m
- Demo: Exploring Sparse Columns 2m
- Demo: Using XML as an Alternative 1m
- Demo: From XML to JSON 1m
- Module Summary 2m
About the author
Gerald Britton is a Pluralsight author and expert on Python programming practices and Microsoft SQL Server development and administration. A multiple-year of the Microsoft MVP award, Gerald has led introductory classes in Python and SQL for industry-sponsored events at Ryerson University, Toronto and the University of Toronto (his alma mater).