SQL Server 2017 の Graph データベースのお話しをしました。

プログラミング生放送勉強会 第49回@大阪 + わんくま同盟 大阪勉強会 #71 : ATND で話したスライドです。

デモで使ったクエリとか
https://1drv.ms/f/s!Amu3DIZBpTxRsyyBYVenLayAxKNQ

11/18(土) プログラミング生放送勉強会 第49回@大阪 + わんくま同盟 大阪勉強会 #71 でお話しします

今週土曜の
11/18 プログラミング生放送勉強会 第49回@大阪 + わんくま同盟 大阪勉強会 #71 開催 – プログラミング生放送
SQL Server 2017 の Graph データ に焦点を当ててお話しします。

他にも、MvvmCross、メタプログラミング、Docker コンテナの話しと盛り沢山です!

申し込みサイトはこちら
プログラミング生放送勉強会 第49回@大阪 + わんくま同盟 大阪勉強会 #71 : ATND

プロ生今年初の大阪開催です!まだ空きありますので是非ご参加を~。

メモ $node_id 列に インデックス貼ると、内部では graph_id_<hexstring> に列になるか試してみた。

ドキュメントに書いてたから試しただけ。

SQL Graph Architecture | Microsoft Docs
Node Table より引用

That is, an index created on the $node_id column, will appear on the internal graph_id_<hex_string> column. 
create table Hoge (
  Name nvarchar(20) not null primary key
) as node
create index idx_Hoge_NodeId on Hoge ($node_id)

select idc.column_id, col.name
from 
  sys.indexes id inner join sys.index_columns idc on 
    id.object_id = idc.object_id and id.index_id = idc.index_id
  inner join sys.columns col on 
    idc.object_id = col.object_id and idc.column_id = col.column_id
where id.name = 'idx_Hoge_NodeId'
column_id name
1 graph_id_03ED99AB760344ACADAE09BE9D4478E4

おーほんとだ。

メモ:SQL Server 2017 Graph の NOT MATCH

ぼちぼち SQL Server 2017 の Graph 触ってます。
Graph processing with SQL Server and Azure SQL Database | Microsoft Docs

MATCH は、NOT 書けないようなので、代わりに NOT EXISTS を使うようです。
MATCH (SQL Graph) | Microsoft Docs

例:片思いの人を取得する。
f:id:odashinsuke:20171031231232p:plain
A さんと D さんが片思いなデータを作ってみます。

create table [人] ( [名前] nvarchar(20) ) as node
create table [好き] as edge

insert into [人] values (N'Aさん'), (N'Bさん'), (N'Cさん'), (N'Dさん'), (N'Eさん')
declare @id1 nvarchar(1000) = (select $node_id from [人] where [名前] = N'Aさん')
declare @id2 nvarchar(1000) = (select $node_id from [人] where [名前] = N'Bさん')
declare @id3 nvarchar(1000) = (select $node_id from [人] where [名前] = N'Cさん')
declare @id4 nvarchar(1000) = (select $node_id from [人] where [名前] = N'Dさん')
declare @id5 nvarchar(1000) = (select $node_id from [人] where [名前] = N'Eさん')

insert into [好き] values
(@id1, @id2)
, (@id2, @id3)
, (@id3, @id2)
, (@id4, @id5)

まず繋がってる人を取ってみます。

select [誰さん].[名前] as [誰々さんが], [好きな人].[名前] as [好きな人]
from [人] [誰さん], [好き], [人] [好きな人]
where match ([誰さん] - ([好き]) -> [好きな人])
誰々さんが 好きな人
Aさん Bさん
Bさん Cさん
Cさん Bさん
Dさん Eさん

ここから、両想いの Bさん、Cさんを除くと求めたい片思いの人が取れます。
not match (誰さん <- ([好き]) - [好きな人]) を追加したら良いのかなと試したところエラーが出ました。

select [誰さん].[名前] as [誰々さんが], [好きな人].[名前] as [好きな人]
from [人] [誰さん], [好き], [人] [好きな人]
where match ([誰さん] - ([好き]) -> [好きな人])
and not match ([誰さん] <- ([好き]) - [好きな人])
メッセージ 13903、レベル 16、状態 1、行 23
エッジ テーブル '好き' が複数の MATCH パターンで使用されています。
メッセージ 13905、レベル 16、状態 1、行 23
MATCH 句は、OR や NOT を使用して他の式と直接組み合わせることができません。

MATCH は、OR や NOT と一緒に使えないようです。
Graph extensions in Microsoft SQL Server 2017 and Azure SQL Database
にも記載がありました。
スライドの P32-34 辺りです。

というわけで書き直します。

select [誰さん].[名前] as [誰々さんが], [好きな人].[名前] as [好きな人]
from [人] [誰さん], [好き], [人] [好きな人]
where match ([誰さん] - ([好き]) -> [好きな人])
and not exists (
  select * from [好き] [nest] 
  where match ([誰さん] <- ([nest]) - [好きな人])
)
誰々さんが 好きな人
Aさん Bさん
Dさん Eさん

これで取れました!

もう1個のエラーの エッジは1個のマッチでしか使えないのも少し試します。
両想いを取るこれでもエラーが出ました。

select [誰さん].[名前] as [誰々さんが], [好きな人].[名前] as [好きな人]
from [人] [誰さん], [好き], [人] [好きな人]
where match ([誰さん] - ([好き]) -> [好きな人] 
and [誰さん] <- ([好き]) - [好きな人])

正しくはこうなります。

select [誰さん].[名前] as [誰々さんが], [好きな人].[名前] as [好きな人]
from [人] [誰さん], [好き] [好1], [人] [好きな人], [好き] [好2]
where match ([誰さん] - ([好1]) -> [好きな人] 
and [誰さん] <- ([好2]) - [好きな人])

10/24(火) SQLWorld★大阪#44 開催します

SqlWorld :: SQLWorld★大阪#44 開催します。26回目の平日夜開催で、前回同様 ハンズオン 形式行う予定です。

【日時】
2017年10月24日(火曜日) 19:00~21:00
 

【イベント概要】
SQLWorld 26回目の平日夜開催~。今回も、みんなで SQL を書いてみようというハンズオン企画です!ブラウザがあれば参加出来るようにしていますので、iPad 等のタブレットでも大丈夫です。
 

【会場】
フェンリル株式会社さま大阪本社 http://www.fenrir-inc.com/
〒530-0011 大阪府大阪市北区大深町 3番1号 グランフロント大阪タワーB(オフィス)
 

【参加費】
無料
 

【持ち物】
パソコン/タブレット (DB のインストールは不要です。)
 

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を~。

開催回数は増えていっていますが、続き物というわけでは無いので初めて参加される方でもお気軽にどぞー。

SQL Server 2017 の GA が発表されてました!

Ignite で発表があったようです。
10/2 GA みたい。
Microsoft for the Modern Data Estate | SQL Server Blog
後で読む。

T-SQL CREATE TABLE での FOREIGN KEY の指定で、ドキュメントに載ってない方法で出来た。。

CREATE TABLE (Transact-SQL) | Microsoft Docs

ドキュメントでは、CREATE TABLE での FOREIGN KEY 指定はテーブルで指定する方法と列で指定する方法の2パターンあります。

テーブル

< table_constraint > ::=  
 [ CONSTRAINT constraint_name ]  
{    
   { PRIMARY KEY | UNIQUE }  
     {   
       NONCLUSTERED (column [ ASC | DESC ] [ ,... n ])  
       | NONCLUSTERED HASH (column [ ,... n ] ) WITH ( BUCKET_COUNT = bucket_count )   
                    }   
    | FOREIGN KEY   
        ( column [ ,...n ] )   
        REFERENCES referenced_table_name [ ( ref_column [ ,...n ] ) ]   
    | CHECK ( logical_expression )   
}  

列(通常の列と計算列)

<column_constraint> ::=   
[ CONSTRAINT constraint_name ]   
{     { PRIMARY KEY | UNIQUE }   
        [ CLUSTERED | NONCLUSTERED ]   
        [   
            WITH FILLFACTOR = fillfactor    
          | WITH ( < index_option > [ , ...n ] )   
        ]   
        [ ON { partition_scheme_name ( partition_column_name )   
            | filegroup | "default" } ]  

  | [ FOREIGN KEY ]   
        REFERENCES [ schema_name . ] referenced_table_name [ ( ref_column ) ]   
        [ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ ON UPDATE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]   
        [ NOT FOR REPLICATION ]   

  | CHECK [ NOT FOR REPLICATION ] ( logical_expression )   
}

<computed_column_definition> ::=  
column_name AS computed_column_expression   
[ PERSISTED [ NOT NULL ] ]  
[   
    [ CONSTRAINT constraint_name ]  
    { PRIMARY KEY | UNIQUE }  
        [ CLUSTERED | NONCLUSTERED ]  
        [   
            WITH FILLFACTOR = fillfactor   
          | WITH ( <index_option> [ , ...n ] )  
        ]  
        [ ON { partition_scheme_name ( partition_column_name )   
        | filegroup | "default" } ]  

    | [ FOREIGN KEY ]   
        REFERENCES referenced_table_name [ ( ref_column ) ]   
        [ ON DELETE { NO ACTION | CASCADE } ]   
        [ ON UPDATE { NO ACTION } ]   
        [ NOT FOR REPLICATION ]   

    | CHECK [ NOT FOR REPLICATION ] ( logical_expression )   
]   

テーブルで指定するときは、自分の列と相手の列を指定する、
列で指定するときは、相手の列を指定する感じです。

ところで次の3つのクエリは全部同じ結果になります。

1: table_constraint で指定した場合

drop table if exists [Child]
drop table if exists [Parent]

create table [Parent] (
  [Id] int not null primary key
)
create table [Child] (
  [Id] int not null primary key, 
  [ParentId] int not null, 
  [Remark] nvarchar(max), 
  foreign key ([ParentId]) references [Parent]([Id])
)

2: column_constraint で指定した場合

drop table if exists [Child]
drop table if exists [Parent]

create table [Parent] (
  [Id] int not null primary key
)
create table [Child] (
  [Id] int not null primary key, 
  [ParentId] int not null foreign key references [Parent]([Id]),  
  [Remark] nvarchar(max)
)

3: column_constraint で指定した場合(他の列で指定している!)

drop table if exists [Child]
drop table if exists [Parent]

create table [Parent] (
  [Id] int not null primary key
)
create table [Child] (
  [Id] int not null primary key foreign key([ParentId]) references [Parent]([Id]), 
  [ParentId] int not null,   
  [Remark] nvarchar(max)
)

3: のパターンは、ドキュメントでは指定出来ないはずなんだけどどうなんですかね?
バグだったとしても実害はないでしょうけど。