You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Copy file name to clipboardExpand all lines: docs/relational-databases/graphs/sql-graph-architecture.md
+15-15Lines changed: 15 additions & 15 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -29,7 +29,7 @@ Users can create one graph per database. A graph is a collection of node and edg
29
29
Figure 1: SQL Graph database architecture
30
30
31
31
## Node Table
32
-
A node table represents an entity in a graph schema. Every time a node table is created, along with the user-defined columns, an implicit `$node_id` column is created, which uniquely identifies a given node in the database. The values in `$node_id` are automatically generated and are a combination of `object_id` of that node table and an internally generated bigint value. However, when the `$node_id` column is selected, a computed value in the form of a JSON string is displayed. Also, `$node_id` is a pseudo-column, that maps to an internal name with hex string in it. When you select `$node_id` from the table, the column name will appear as `$node_id_<hex_string>`.
32
+
A node table represents an entity in a graph schema. Every time a node table is created, along with the user-defined columns, an implicit `$node_id` column is created, which uniquely identifies a given node in the database. The values in `$node_id` are automatically generated and are a combination of object ID for the graph table of that node table and an internally generated bigint value. However, when the `$node_id` column is selected, a computed value in the form of a JSON string is displayed. Also, `$node_id` is a pseudo-column, that maps to an internal name with hex string in it. When you select `$node_id` from the table, the column name will appear as `$node_id_<hex_string>`.
33
33
34
34
> [!NOTE]
35
35
> Using the pseudo-columns in queries is the only supported and recommended way of querying the internal `$node_id` column. You should not directly use the `$node_id_<hex_string>` columns in any queries.
Use these metadata views to see attributes of a node or edge table.
62
62
63
63
### sys.tables
64
-
The following new, bit type, columns will be added to SYS.TABLES. If `is_node` is set to 1, that indicates that the table is a node table and if `is_edge` is set to 1, that indicates that the table is an edge table.
64
+
The following `bit`columns in [sys.tables](../../relational-databases/system-catalog-views/sys-tables-transact-sql.md) can be used to identify graph tables. If `is_node` is set to 1, that indicates that the table is a node table and if `is_edge` is set to 1, that indicates that the table is an edge table.
65
65
66
66
|Column Name |Data Type |Description |
67
67
|--- |---|--- |
@@ -95,33 +95,33 @@ The implicit columns in a node table are:
|from_obj_id_\<hex_string> |INT |1 |internal from node `object_id`|
108
-
|from_id_\<hex_string> |BIGINT |1 |Internal from node `graph_id`|
108
+
|from_id_\<hex_string> |BIGINT |1 |Internal from node graph ID value|
109
109
|$from_id_\<hex_string> |NVARCHAR |0 |external from node `node_id`|
110
110
|to_obj_id_\<hex_string> |INT |1 |internal to node `object_id`|
111
-
|to_id_\<hex_string> |BIGINT |1 |Internal to node `graph_id`|
111
+
|to_id_\<hex_string> |BIGINT |1 |Internal to node graph ID value|
112
112
|$to_id_\<hex_string> |NVARCHAR |0 |external to node `node_id`|
113
113
114
114
### <aname="SystemFunctions"></a>System Functions
115
-
The following built-in functions are added. These will help users extract information from the generated columns. Note that, these methods will not validate the input from the user. If the user specifies an invalid `sys.node_id` the method will extract the appropriate part and return it. For example, OBJECT_ID_FROM_NODE_ID will take a `$node_id` as input and will return the object_id of the table, this node belongs to.
115
+
The following built-in functions allow users to interact with the pseudo-columns in graph tables. Detailed references are provided for each of these functions in the respective T-SQL function references.
116
116
117
117
|Built-in |Description |
118
118
|--- |--- |
119
-
|OBJECT_ID_FROM_NODE_ID |Extract the object_id from a `node_id`|
120
-
|GRAPH_ID_FROM_NODE_ID |Extract the graph_id from a `node_id`|
121
-
|NODE_ID_FROM_PARTS |Construct a node_id from an `object_id`and a `graph_id`|
122
-
|OBJECT_ID_FROM_EDGE_ID |Extract `object_id` from `edge_id`|
|EDGE_ID_FROM_PARTS |Construct `edge_id` from `object_id`and identity|
119
+
|[OBJECT_ID_FROM_NODE_ID](../../t-sql/functions/object-id-from-node-id-transact-sql.md)|Extract the object ID for the graph table from a `node_id`|
120
+
|[GRAPH_ID_FROM_NODE_ID](../../t-sql/functions/graph-id-from-node-id-transact-sql.md)|Extract the graph ID value from a `node_id`|
121
+
|[NODE_ID_FROM_PARTS](../../t-sql/functions/node-id-from-parts-transact-sql.md)|Construct a node_id from an object ID for the graph table and a graph ID value|
122
+
|[OBJECT_ID_FROM_EDGE_ID](../../t-sql/functions/object-id-from-edge-id-transact-sql.md)|Extract object ID for the graph table from `edge_id`|
123
+
|[GRAPH_ID_FROM_EDGE_ID](../../t-sql/functions/graph-id-from-edge-id-transact-sql.md)|Extract the graph ID value for a given`edge_id`|
124
+
|[EDGE_ID_FROM_PARTS](../../t-sql/functions/node-id-from-parts-transact-sql.md)|Construct `edge_id` from object ID for the graph table and graph ID value|
125
125
126
126
## Transact-SQL reference
127
127
Learn the [!INCLUDE[tsql-md](../../includes/tsql-md.md)] extensions introduced in SQL Server and Azure SQL Database, that enable creating and querying graph objects. The query language extensions help query and traverse the graph using ASCII art syntax.
@@ -151,7 +151,7 @@ Learn the [!INCLUDE[tsql-md](../../includes/tsql-md.md)] extensions introduced i
151
151
|MATCH |[MATCH (Transact-SQL)](../../t-sql/queries/match-sql-graph.md)|MATCH built-in is introduced to support pattern matching and traversal through the graph. |
152
152
153
153
## Limitations and known issues
154
-
There are certain limitations on node and edge tables in this release:
154
+
There are certain limitations on node and edge tables:
155
155
* Local or global temporary tables cannot be node or edge tables.
156
156
* Table types and table variables cannot be declared as a node or edge table.
157
157
* Node and edge tables cannot be created as system-versioned temporal tables.
@@ -160,4 +160,4 @@ There are certain limitations on node and edge tables in this release:
160
160
* Cross database queries on graph objects are not supported.
161
161
162
162
## Next Steps
163
-
To get started with the new syntax, see [SQL Graph Database - Sample](./sql-graph-sample.md)
163
+
To get started with SQL Graph, see [SQL Graph Database - Sample](./sql-graph-sample.md)
[!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sqlserver2017-asdb-asdbmi.md)]
22
+
23
+
Returns the character representation of the edge ID for a given an object ID and graph ID.
24
+
25
+
## Syntax
26
+
27
+
```syntaxsql
28
+
EDGE_ID_FROM_PARTS ( object_id, graph_id )
29
+
```
30
+
31
+
## Arguments
32
+
33
+
*object_id*
34
+
Is an int representing the object ID of the corresponding graph edge table.
35
+
36
+
*graph_id*
37
+
Is a bigint value for the graph ID for an edge.
38
+
39
+
## Return value
40
+
41
+
Returns an NVARCHAR(1000) character representation of the edge ID, provided the object_id supplied corresponds to an edge table. In other cases, a NULL is returned.
42
+
43
+
## Remarks
44
+
45
+
The character representation of the edge ID is an implementation specific detail. EDGE_ID_FROM_PARTS is the only supported way to construct a suitable character representation of the node ID. EDGE_ID_FROM_PARTS is typically useful in cases involving bulk insert of graph nodes into a node table, and is usually used along with [NODE_ID_FROM_PARTS](./node-id-from-parts-transact-sql.md).
46
+
47
+
## Examples
48
+
49
+
### Example 1
50
+
51
+
The following example uses the [OPENROWSET Bulk Rowset Provider](../../relational-databases/import-export/bulk-import-large-object-data-with-openrowset-bulk-rowset-provider.md) to retrieve the `dataset_key` and `rating` columns from a CSV file stored on an Azure Storage acount. It then uses EDGE_ID_FROM_PARTS to create the character representation of $edge_id, using the `dataset_key` from the CSV file. It also uses [NODE_ID_FROM_PARTS](./node-id-from-parts-transact-sql.md) twice to create the appropriate character representations of $from_id (for the Person node table) and $to_id values (for the Restaurant node table) respectively. This data is then (bulk) inserted into the `likes` edge table. This approach can be very efficient to populate an edge table when the source data already has integers as natural or surrogate keys.
52
+
53
+
```sql
54
+
INSERT INTO likes($edge_id, $from_id, $to_id, rating)
55
+
SELECT EDGE_ID_FROM_PARTS(OBJECT_ID('likes'), dataset_key) as from_id
56
+
, NODE_ID_FROM_PARTS(OBJECT_ID('Person'), ID) as from_id
57
+
, NODE_ID_FROM_PARTS(OBJECT_ID('Restaurant'), ID) as to_id
58
+
, rating
59
+
FROM OPENROWSET (BULK 'person_likes_restaurant.csv', DATA_SOURCE ='staging_data_source', FORMATFILE ='format-files/likes.xml', FORMATFILE_DATA_SOURCE ='format_files_source', FIRSTROW =2) AS staging_data;
Copy file name to clipboardExpand all lines: docs/t-sql/functions/functions.md
+1Lines changed: 1 addition & 0 deletions
Display the source diff
Display the rich diff
Original file line number
Diff line number
Diff line change
@@ -62,6 +62,7 @@ Operate on a single value and then return a single value. Scalar functions can b
62
62
|[Conversion Functions](conversion-functions-transact-sql.md)|Support data type casting and converting.|
63
63
|[Cursor Functions](cursor-functions-transact-sql.md)|Return information about cursors.|
64
64
|[Date and Time Data Types and Functions](date-and-time-data-types-and-functions-transact-sql.md)|Perform operations on a date and time input values and return string, numeric, or date and time values.|
65
+
|[Graph Functions](graph-functions-transact-sql.md)|Perform operations to convert between integer values and graph internal node and edge IDs.|
65
66
|[JSON Functions](json-functions-transact-sql.md)|Validate, query, or change JSON data.|
|[Mathematical Functions](mathematical-functions-transact-sql.md)|Perform calculations based on input values provided as parameters to the functions, and return numeric values.|
[!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sqlserver2017-asdb-asdbmi.md)]
22
+
23
+
Returns the internal graph ID value for a given graph edge ID.
24
+
25
+
## Syntax
26
+
27
+
```syntaxsql
28
+
GRAPH_ID_FROM_EDGE_ID ( edge_id )
29
+
```
30
+
31
+
## Arguments
32
+
33
+
*edge_id*
34
+
Is the $edge_id pseudo-column in a graph edge table.
35
+
36
+
## Return value
37
+
38
+
Returns the internal graph ID value, which is currently a `bigint`.
39
+
40
+
## Remarks
41
+
42
+
Graph IDs are an implementation specific detail. The data type and behavior of graph IDs are subject to change. For example, you shouldn't assume that graph IDs in a given edge table are sequential.
43
+
44
+
## Examples
45
+
46
+
### Example 1
47
+
48
+
The following example returns the internal graph ID value for all edges in the `friendOf` graph edge table.
[!INCLUDE [SQL Server Azure SQL Database Azure SQL Managed Instance](../../includes/applies-to-version/sqlserver2017-asdb-asdbmi.md)]
22
+
23
+
Returns the internal graph ID value for a given graph node ID.
24
+
25
+
## Syntax
26
+
27
+
```syntaxsql
28
+
GRAPH_ID_FROM_NODE_ID ( node_id )
29
+
```
30
+
31
+
## Arguments
32
+
33
+
*node_id*
34
+
Is either the $node_id pseudo-column in a graph node table, or the $from_id pseudo-column for a graph edge table, or the $to_id column for a graph edge table.
35
+
36
+
## Return value
37
+
38
+
Returns the internal graph ID value, which is currently a `bigint`.
39
+
40
+
## Remarks
41
+
42
+
Graph IDs are an implementation specific detail. The data type and behavior of graph IDs are subject to change. For example, you shouldn't assume that graph IDs in a given node table are sequential.
43
+
44
+
## Examples
45
+
46
+
### Example 1
47
+
48
+
The following example returns the internal graph ID value for all nodes in the `Person` graph node table.
0 commit comments