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

Use the graph table features in Azure SQL that were introduced in SQL Server 2017 and further refined in SQL Server 2019. This book shows you how to create data structures to capture complex connections between items in your data. These connections will help you analyze and draw insights from connections in your data that go beyond classic relationships.

The graph examples in the book are useful for analyzing social media relationships, complex product-to-customer relationships, and any other type of data analysis in which indirect connections that otherwise might be missed using conventional techniques can be mined for their insight and business value.

Tree structures are covered, with emphasis on a structure commonly used by organizations to aggregate data at different levels of an organization. The book provides code examples of SQL Graph objects as well as an alternate tree implementation technique. Included is sample data (and data generators) for you to test for performance and choose the implementation approach that best suits your needs and that of your application.

If your job involves analyzing or storage of data elements that are connected in a networked topology, then this is the book that will help you bring the power of SQL Server to bear on that data and take advantage of your existing knowledge.

What You Will Learn

• Understand the graph model and the associated terms used in graph analysis
• Store highly connected data in SQL Server and Azure SQL alongside existing relational data
• Make full use of the graph table feature that is refined and enhanced in SQL Server 2019
• Implement high performance tree structures that will make storing and querying tree data possible
• Report on data associated with a tree structure to aggregate results at different levels