Practical Graph Structures in SQL Server and Azure SQL: Enabling Deeper Insights Using Highly Connected Data

Table of Contents....5 About the Author....8 About the Technical Reviewer....9 Acknowledgments....10 Preface....11 Chapter 1: Introduction to Graphs....14 Graph Fundamentals....16 Definition....17 Graphs In Computing/Directed Graphs....24 Cyclic and Acyclic Graphs....26 Summary....28 Chapter 2: Data Structures and Algorithms....29 Basic Implementation....29 Acyclic Graphs....31 Trees....34 Other Acyclic Graphs....39 Cyclic Graphs....42 Non-Directed Graphs....47 Summary....48 Chapter 3: SQL Graph Table Basics....50 Object Creation....51 Creating Data....54 Querying Data....59 Node-to-Node Querying....59 Filtering Output....62 Multiple MATCH Expressions....64 Traversing Variable Level Paths....70 Displaying the Last Node in the Path....71 Aggregation Along the Path....73 Controlling Depth of Processing....76 Filtering for One Path....78 Finding All Paths Between Nodes....79 Weighted Graph Calculations....82 Checking Conditions on the Matched Item....84 Summary....86 Chapter 4: SQL Graph Tables: Extended Topics....87 Advanced Data Creation Techniques....87 Building an Interface Layer....88 INSERT....88 UPDATE....93 DELETE....94 Loading Data Using Composable JSON Tags....97 Heterogenous Queries....103 Integrity Constraints and Indexes....111 Edge Constraint....111 Uniqueness Constraints (and Indexes)....117 Additional Constraints....121 Metadata Roundup....125 List Graph Objects in the Database....125 Types of Graph Columns....126 Tools for Fetching Graph Information....127 Summary....128 Chapter 5: Tree Data Structures....130 Creating the Data Structures....131 Base Table Structures....131 Demo Sales Structure....134 Essential Tree Maintenance Code....136 Code To Create New Nodes....137 Reparenting Nodes....147 Deleting a Node....150 Tree Output Code....157 Returning Part of the Tree....158 Determining If a Child Node Exists....160 Aggregating Child Activity at Every Level....163 Summary....171 Chapter 6: Tree Structures, Algorithms, and Performance....172 Alternative Tree Implementation....173 Path Technique....175 Code....177 Table Create Script....178 Insert New Rows....178 Return Hierarchy....179 Check For Child....181 Report Sales....182 Helper Table....184 Kimball Helper Table....185 Code....187 Table Create Script....187 Check For Child....190 Hierarchy Display Helper....191 Code....191 Table Create Script....191 Using the Helper Objects....192 Report Sales....192 Performance Comparison....195 Summary....198 Chapter 7: Other Directed Acyclic Graphs....199 The Problem Set....199 The Example....201 Determining If a Part Is Used in a Build....205 Picking Items for a Build....208 Printing Out the Parts List for a Build....209 Summary....214 Chapter 8: A Graph For Testing....216 The Example....216 Creating the Tables....217 Loading the Data....219 The Queries....222 Find Every Node That Is Connected to a Specific Node....222 Seeing If One Node is Connected to Another....224 Returning All Paths Between Two Nodes....225 Finding All People That a User Connects To At Any Level Where They Share an Interest....229 Finding a Specific User Who a User Connects To at Any Level Where They Share a Specific Interest....231 Finding Connections Through Other Nodes....233 Performance Tuning Results....235 Performance Tuning Roundup....238 Test....238 Index the Internal Columns....238 Employ a Maximum Degree of Parallelism of One....239 Consider Breaking Up Some Queries....239 The End (or Is It the Beginning?)....240 Index....241