SQL Graph and Hierarchies

Kevin Feasel

2019-07-16

Graph

Hasan Savran shows how you can use SQL Server graph tables to handle hierarchical data:

The > determines the direction of the query. By changing this arrow into other direction, you can use the same relation table to find who manages employee. For example, let’s rewrite this query and look who manages East Manager

SELECT emp.Name as Emp, emp2.Name as Mgr
FROM Hr as emp, ReportsTo as r, Hr as emp2 
WHERE MATCH(emp<-(r)-emp2) and emp.empid = 8

There are a few relational approaches which work well for hierarchies, though they all come with tradeoffs in terms of read complexity, write complexity, and development complexity. Click through for Hasan’s take on the graph-based approach.

Related Posts

Measuring Closeness Centrality in Graphs

Niko Neugebauer explains the concept of Closeness Centrality: The real center of the network or also known as The King of the Network, Closeness Centrality is a measure which represents the relative location of the Vertice to the center of the network, or better to say the average distance to all other Vertices within that […]

Read More

Using Graph + Spatial to Find Closest Points

Hasan Savran shows how you can combine graph tables with spatial data types in SQL Server to find the nearest thing—in this case, a distribution center: Today, I want to show you how Graph Processing Tables can make your data models flexible and smart. Let’s say we work in a e-commerce company, we have many […]

Read More

Categories

July 2019
MTWTFSS
« Jun Aug »
1234567
891011121314
15161718192021
22232425262728
293031