Sep 3, 2020
SQL Server (2017/2019) — An opportunity lost?
In 2017 Microsoft released SQL Server 2017. I was excited to hear that SQL Server now supports graph data structures and queries.
Microsoft now proudly and rightly states, “A relational database can achieve anything a graph database can”, and that is what excited me most about the product. Entering at the peak of the hype cycle for graph databases, Microsoft was putting paid to the adage that graph databases offered solutions for use cases that relational databases could not handle.
Rather than dedicated graph databases being able to do things that relational databases cannot do I have always thought graph query languages offer the ability to do things much easier than the standard Structured Query Language (SQL) of relational databases. Microsoft knows this and released SQL Server 2017 to drive home the point. Indeed, research shows that graph queries over a relational database can outperform those same queries over a graph database [1]. To my knowledge Microsoft still claims to have the fastest queries around.
The Sales Pitch for Graph Databases
If you do cursory reading of the sales material of graph database vendors, very quickly you hear that graph databases are for “highly connected data where the relationships between objects matter”, as if to say that relational databases are not. Nearly every pitch of this nature has a cursory “unlike relational databases” statement to help make the pitch.
Let us be real, the very nature of relational databases is the importance of relationships between tables and, now, nodes. For example, ORACLE defends their relational database with graph capabilities as well.
The graph database sales pitch falls flat on those who can see it for what it is, but what is undeniably true is that the Structured Query Language of a relational database does not reference foreign keys between tables as if they were intrinsic to the very nature of querying the database; not on the surface anyway.
“Relationship data is tied heavily into the structure of the underlying data storage of a graph database”, the pitch for a dedicated graph database goes. But the realities are that foreign key relationships in relational databases are too very much how data is stored in a relational database; you just cannot include the name of the foreign key relationship in the SQL query, but rather structure the SQL query to name and qualify the relationship attribute values as being equal in the joins between entities in the relational database. How to incorporate named relationships into SQL?
Without writing a treatise on what graph query languages look like as opposed SQL, enough to say…if you know what I am talking about this article is for you.
Microsoft solved this problem by creating another special table type of sorts, acting as the storage of edges/relationships, and incorporating those into the query language as a nameable edge.
This is where I wished for more. This is where the FactEngine initiative (www.factengine.ai) aims to change thinking on relational databases when it comes to graphs. The FactEngine initiative invites all relational databases to stay as they are and simply put a graph query language over them without changing the database.
How else could it have been done?
There was another way Microsoft could have solved the problem of converting SQL Server into a database that can handle graph queries, and that is by the use of Object-Role Modeling at the metamodel/data dictionary level, and I will explain.
The problem was two-fold…many-to-many join tables, forming an ostensible edge, have two foreign keys to think about, and the simpler scenario is when a column in one table simply references a column in another table.
E.g. Person lives in City is a simple relationship, but Person likes City as a many-to-many relationship with a Rating thrown in, requires a joining table in the relational model, requiring two foreign keys.
How do you manage both scenarios for graph queries over a relational structure?
In the 1990s the academic, Terry Halpin (PhD), from Australia came to work as a program manager in Microsoft’s database division on Microsoft’s acquisition of Visio Corporation. Visio previously acquired Halpin’s services when having bought-out Asymetrix Corporation. Microsoft was after the Visio conceptual modelling tool, and came with it code for Object-Role Modeling and where Object-Role Modeling is product of the language, Niam, the syntax of which was formalised in Halpin’s PhD thesis awarded in 1989.
Object-Role Modeling holds the key to solving the problem. Conceptualise the model in Object-Role Modeling, and then convert the model to a graph model or a relational model as if they already exist:
…and the Property Graph Schema:
Then just query your database in the best graph query language in the world…natural language:
For reasons unknown to the author, Halpin left Microsoft and went back to academia, but I can only imagine that the SQL Server folk at Microsoft did not take to Object-Role Modeling and its more complex, but semantically more expressive, way of expressing data structures; rather than the relative simplicity of Entity-Relationship Diagrams.
Halpin’s thesis defined ways to quickly and easily transform Object-Role Models into relational structures for relational databases, and by default, Entity-Relationship Diagrams. It is, however, because of ORM’s low level analysis and design of data structures, that ORM diagrams can as quickly be converted to the Property Graph Schemas of graph databases as well.
The technology to leap-frog all graph database vendors, and have relational and graph modelling in SQL Server in the mid-1990s was right under Microsoft’s nose. Granted, Object-Role Modeling was ahead of its time in that regard, and while graph databases and their query may have been theoretically discussed and experimented with, it was some years before commercial grade graph databases became widely available and used.
So how do you incorporate Object-Role Modeling into SQL Server, or any relational database?
The theory is simple, the implementation complex but the practicality straight forward. Either don’t change your database at all and just do graph queries over your existing database or have Object-Role Modeling as the core to your conceptual modelling in your database.
…then query your database as if a graph database…
Had the SQL Server team foreseen the potential of Object-Role Modeling in the 1990s it is quite possible that Microsoft would be leading the graph database sphere today.
I take my hat off to Microsoft for taking action and introducing graph-based queries in SQL Server. Regardless of future architecture directions, one thing is certain…graph queries over relational databases are here to stay.
You guessed it, no opportunity has been lost. We merely now have a choice as to which type of database we want to work with, all within relational databases.
Thank you for reading. As time permits I will write more on conceptual modelling for multi-model databases and graph queries over relational databases.
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —
1. Rawlani, P. et al, “Graph analytics on relational databases”, Massachusetts Institute of Technology, https://dspace.mit.edu/handle/1721.1/100670, Accessed at 24/08/2020
