Skip to content

sql-graph-overview.md

Latest commit

 

History

History
79 lines (62 loc) · 4.99 KB

sql-graph-overview.md

File metadata and controls

79 lines (62 loc) · 4.99 KB

title: "Graph processing with SQL Server and Azure SQL Database | Microsoft Docs" ms.custom: "" ms.date: "07/18/2017" ms.prod: sql ms.prod_service: "database-engine, sql-database" ms.component: "graphs" ms.reviewer: "" ms.suite: "sql" ms.technology:

  • "database-engine" ms.tgt_pltfrm: "" ms.topic: "language-reference" helpviewer_keywords:
  • "SQL graph"
  • "SQL graph, overview" ms.assetid: caps.latest.revision: 1 author: "shkale-msft" ms.author: "shkale" manager: craigg monikerRange: "= azuresqldb-current || >= sql-server-2017 || = sqlallproducts-allversions"

Graph processing with SQL Server and Azure SQL Database

[!INCLUDEtsql-appliesto-ss2017-asdb-xxxx-xxx-md]

[!INCLUDEssNoVersion] offers graph database capabilities to model many-to-many relationships. The graph relationships are integrated into [!INCLUDEtsql-md] and receive the benefits of using [!INCLUDEssNoVersion] as the foundational database management system.

What is a graph database?

A graph database is a collection of nodes (or vertices) and edges (or relationships). A node represents an entity (for example, a person or an organization) and an edge represents a relationship between the two nodes that it connects (for example, likes or friends). Both nodes and edges may have properties associated with them. Here are some features that make a graph database unique:

  • Edges or relationships are first class entities in a Graph Database and can have attributes or properties associated with them.
  • A single edge can flexibly connect multiple nodes in a Graph Database.
  • You can express pattern matching and multi-hop navigation queries easily.
  • You can express transitive closure and polymorphic queries easily.

When to use a graph database

There is nothing a graph database can achieve, which cannot be achieved using a relational database. However, a graph database can make it easier to express certain kind of queries. Also, with specific optimizations, certain queries may perform better. Your decision to choose one over the other can be based on following factors:

  • Your application has hierarchical data. The HierarchyID datatype can be used to implement hierarchies, but it has some limitations. For example, it does not allow you to store multiple parents for a node.
  • Your application has complex many-to-many relationships; as application evolves, new relationships are added.
  • You need to analyze interconnected data and relationships.

Graph features introduced in [!INCLUDEsssqlv14]

We are starting to add graph extensions to SQL Server, to make storing and querying graph data easier. Following features are introduced in the first release.

Create graph objects

[!INCLUDEtsql-md] extensions will allow users to create node or edge tables. Both nodes and edges can have properties associated to them. Since, nodes and edges are stored as tables, all the operations that are supported on relational tables are supported on node or edge table. Here is an example:

CREATE TABLE Person (ID INTEGER PRIMARY KEY, Name VARCHAR(100), Age INT) AS NODE;
CREATE TABLE friends (StartDate date) AS EDGE;

person-friends-tables
Nodes and Edges are stored as tables

Query language extensions

New MATCH clause is introduced to support pattern matching and multi-hop navigation through the graph. The MATCH function uses ASCII-art style syntax for pattern matching. For example:

-- Find friends of John
SELECT Person2.Name 
FROM Person Person1, Friends, Person Person2
WHERE MATCH(Person1-(Friends)->Person2)
AND Person1.Name = 'John';

Fully integrated in [!INCLUDEssNoVersion] Engine

Graph extensions are fully integrated in [!INCLUDEssNoVersion] engine. We use the same storage engine, metadata, query processor, etc. to store and query graph data. This enables users to query across their graph and relational data in a single query. Users can also benefit from combining graph capabilities with other [!INCLUDEssNoVersion] technologies like columnstore, HA, R services, etc. SQL graph database also supports all the security and compliance features available with [!INCLUDEssNoVersion].

Tooling and ecosystem

Users benefit from existing tools and ecosystem that [!INCLUDEssNoVersion] offers. Tools like backup and restore, import and export, BCP just work out of the box. Other tools or services like SSIS, SSRS or PowerBI will work with graph tables, just the way they work with relational tables.

Next steps

Read the SQL Graph Database - Architecture