T-SQL テーブル定義の取得 (Temporal Table / Graph Table 対応版)

【訂正版】 テーブル定義の取得 - お だ のスペース の改訂版

Temporal Table と Graph Table を考慮したテーブル定義の取得クエリ。

SELECT 
  schemas.name as SCHEMA_NAME,
  object_name(cols.object_id) as TABLE_NAME, 
  case 
    when cols.graph_type in (2, 5, 8) then SUBSTRING(cols.name, 0, PATINDEX('%[_]%', SUBSTRING(cols.name, PATINDEX('%[_]%', cols.name) + 1, 100)) + PATINDEX('%[_]%', cols.name))
    else cols.name 
  end as COLUMN_NAME, 
  cols.is_nullable AS IS_NULLABLE, 
  types.name as DATA_TYPE, 
  case 
    when keys.column_id is not null then CONVERT(BIT, 1)
    else convert(bit, 0)
  end as IS_KEY, 
  case 
    when types.name in ('char', 'varchar') then cols.max_length 
    when types.name in ('nchar', 'nvarchar') and cols.max_length > 0 then cols.max_length / 2
    else -1 end as MAX_LENGTH
FROM 
  sys.columns AS cols inner join sys.tables tables on (
    cols.object_id = tables.object_id
    and tables.type = 'U'
  ) inner join sys.schemas schemas on (
    tables.schema_id = schemas.schema_id
  ) join sys.types types on ( 
    cols.system_type_id = types.system_type_id
    and cols.user_type_id = types.user_type_id
  ) LEFT OUTER JOIN ( 
    select ix.object_id, ic.column_id
    from 
      sys.objects o 
      inner join sys.indexes ix on o.parent_object_id = ix.object_id
      inner join sys.index_columns ic on ix.object_id = ic.object_id and ix.index_id = ic.index_id
    where o.type = 'PK'
  ) AS keys ON ( 
    cols.object_id = keys.object_id
    and cols.column_id  = keys.column_id
  ) 
WHERE 
  cols.is_hidden = 0
  and tables.temporal_type <> '1'
ORDER BY 
  schemas.name,
  tables.name, 
  cols.column_id

SSMS スクリプト作成の詳細オプション で指定可能なエディションがいつの間にか増えてた

久々に SQL Server Management Studio(SSMS) からオブジェクトのスクリプトを作成する機会があったので見てたら、いつのまにか Edition 毎にスクリプトを生成出来る様になってました。

記憶にあるのは、スタンドアロン(SQL Server) か Azure SQL Database かどちらかを選ぶだけだったんですけどねー。

*1は、

です。

Azure 用は、SQL DB、SQL DW と選べるように。
スタンドアロンは、Enterprise、Express、Standard は良いとして、Personal って SQL Server 2000 のやつ?

Microsoft SQL Server Stretch Database Edition は、Stretch Database | Microsoft Docs はこれ用?
SqlServer ManagedInstanceEdition は、名前の区切りもあれだしまだこれからなんでしょうけど、Azure SQL Database Managed Instance のことかなー?
Put your databases on autopilot with a lift and shift to Azure SQL Database | Blog | Microsoft Azure

*1:SSMS 17.4

メモ T-SQL で Function の名前一覧を取る

select name from sys.objects where 
type in ('TF', 'FN', 'IF')
order by name

sys.objects (Transact-SQL) | Microsoft Docs

メモ:xUnit.net で output

Console.WriteLine や Debug.WriteLine では出力されない。

Capturing Output > xUnit.net

メモ:xUnit.net でテストを並列で動かすのを辞める (not run in parallel)

DB を使うテストでDBの初期化と他のテストでのDBアクセスがバッティングして初期化に失敗してハマったのでメモ。

デフォルトだと、テストクラス毎に並列 (in Parallel) で動く。
Running Tests in Parallel > xUnit.net
↑の中身をざっくりと書くと、

  • Test Collection 単位で並列にテストを実行。
    • 同じ Test Collection 内のテストは直列 (not run in parallel) になる。
  • デフォルトでは、1テストクラス = 1 Test Collection
  • アセンブリに CollectionBehavior を付けることで、Test Collection の単位や並列の動作を変更出来る
  • テストクラス に CollectionAttribute を付けることで、自由に Test Collection を指定出来る。
    • name が同じなら同じ Test Collection

SQL Server のグラフでレコメンドをやってみる

SQL Server のグラフを色々試していくシリーズ。

3回目はレコメンド検索です。
この商品を買った人はこんな商品も買っていますっていうよくあるやつです。

今回はイベントのセッションを対象にして、このセッションを受けた人はこんなセッションも受けていますっていうので試します。

いつものように比較用に通常のテーブルでも試します。
可視化は Neo4j ですが、見難いですね。
f:id:odashinsuke:20180108125814p:plain

ノードが、セッション、参加者でエッジがセッション参加者です。

drop table G_セッション
drop table G_参加者
drop table G_セッション参加者

create table G_セッション (
  Id int not null primary key,
  セッション名 nvarchar(20) not null,
  ルーム char(1) not null,
  コマ int not null 
) as Node
create table G_参加者 (
  Id int not null primary key,
  名前 nvarchar(20) not null
) as Node
create table G_セッション参加者 as Edge

insert into G_セッション values
(1, N'セッションA-1', 'A', 1),
(2, N'セッションA-2', 'A', 2),
(3, N'セッションA-3', 'A', 3),
(4, N'セッションA-4', 'A', 4),
(5, N'セッションB-1', 'B', 1),
(6, N'セッションB-2', 'B', 2),
(7, N'セッションB-3', 'B', 3),
(8, N'セッションB-4', 'B', 4)

insert into G_参加者 values
(1, N'参加者1'),
(2, N'参加者2'),
(3, N'参加者3'),
(4, N'参加者4'),
(5, N'参加者5'),
(6, N'参加者6'),
(7, N'参加者7'),
(8, N'参加者8'),
(9, N'参加者9'),
(10, N'参加者10'),
(11, N'参加者11'),
(12, N'参加者12'),
(13, N'参加者13'),
(14, N'参加者14'),
(15, N'参加者15'),
(16, N'参加者16'),
(17, N'参加者17'),
(18, N'参加者18'),
(19, N'参加者19'),
(20, N'参加者20')

declare @s1 nvarchar(max) = (select $node_id from G_セッション where Id = 1)
declare @s2 nvarchar(max) = (select $node_id from G_セッション where Id = 2)
declare @s3 nvarchar(max) = (select $node_id from G_セッション where Id = 3)
declare @s4 nvarchar(max) = (select $node_id from G_セッション where Id = 4)
declare @s5 nvarchar(max) = (select $node_id from G_セッション where Id = 5)
declare @s6 nvarchar(max) = (select $node_id from G_セッション where Id = 6)
declare @s7 nvarchar(max) = (select $node_id from G_セッション where Id = 7)
declare @s8 nvarchar(max) = (select $node_id from G_セッション where Id = 8)

declare @u1 nvarchar(max) = (select $node_id from G_参加者 where Id = 1)
declare @u2 nvarchar(max) = (select $node_id from G_参加者 where Id = 2)
declare @u3 nvarchar(max) = (select $node_id from G_参加者 where Id = 3)
declare @u4 nvarchar(max) = (select $node_id from G_参加者 where Id = 4)
declare @u5 nvarchar(max) = (select $node_id from G_参加者 where Id = 5)
declare @u6 nvarchar(max) = (select $node_id from G_参加者 where Id = 6)
declare @u7 nvarchar(max) = (select $node_id from G_参加者 where Id = 7)
declare @u8 nvarchar(max) = (select $node_id from G_参加者 where Id = 8)
declare @u9 nvarchar(max) = (select $node_id from G_参加者 where Id = 9)
declare @u10 nvarchar(max) = (select $node_id from G_参加者 where Id = 10)
declare @u11 nvarchar(max) = (select $node_id from G_参加者 where Id = 11)
declare @u12 nvarchar(max) = (select $node_id from G_参加者 where Id = 12)
declare @u13 nvarchar(max) = (select $node_id from G_参加者 where Id = 13)
declare @u14 nvarchar(max) = (select $node_id from G_参加者 where Id = 14)
declare @u15 nvarchar(max) = (select $node_id from G_参加者 where Id = 15)
declare @u16 nvarchar(max) = (select $node_id from G_参加者 where Id = 16)
declare @u17 nvarchar(max) = (select $node_id from G_参加者 where Id = 17)
declare @u18 nvarchar(max) = (select $node_id from G_参加者 where Id = 18)
declare @u19 nvarchar(max) = (select $node_id from G_参加者 where Id = 19)
declare @u20 nvarchar(max) = (select $node_id from G_参加者 where Id = 20)

insert into G_セッション参加者 values
(@u1, @s1),
(@u5, @s1),
(@u10, @s1),
(@u11, @s1),
(@u2, @s5),
(@u3, @s5),
(@u4, @s5),
(@u6, @s5),
(@u7, @s5),
(@u8, @s5),
(@u9, @s5),
(@u12, @s5),
(@u13, @s5),
(@u15, @s5),
(@u17, @s5),
(@u20, @s5),
(@u1, @s2),
(@u2, @s2),
(@u3, @s2),
(@u5, @s2),
(@u7, @s2),
(@u9, @s2),
(@u10, @s2),
(@u11, @s2),
(@u13, @s2),
(@u16, @s2),
(@u18, @s2),
(@u4, @s6),
(@u6, @s6),
(@u8, @s6),
(@u12, @s6),
(@u15, @s6),
(@u17, @s6),
(@u19, @s6),
(@u1, @s3),
(@u3, @s3),
(@u6, @s3),
(@u7, @s3),
(@u9, @s3),
(@u10, @s3),
(@u12, @s3),
(@u14, @s3),
(@u2, @s7),
(@u4, @s7),
(@u8, @s7),
(@u11, @s7),
(@u15, @s7),
(@u16, @s7),
(@u17, @s7),
(@u18, @s7),
(@u19, @s7),
(@u20, @s7),
(@u1, @s4),
(@u2, @s4),
(@u3, @s4),
(@u4, @s4),
(@u5, @s4),
(@u6, @s4),
(@u7, @s4),
(@u8, @s4),
(@u9, @s4),
(@u10, @s4),
(@u11, @s8),
(@u12, @s8),
(@u13, @s8),
(@u14, @s8),
(@u15, @s8),
(@u16, @s8),
(@u17, @s8),
(@u18, @s8),
(@u19, @s8),
(@u20, @s8)

ついでに通常のテーブルの方も作ってしまいましょう。

drop table セッション
drop table 参加者
drop table セッション参加者

create table セッション (
  Id int not null primary key, 
  セッション名 nvarchar(20) not null, 
  ルーム char(1) not null,
  コマ int not null 
)
create table 参加者 (
  Id int not null primary key, 
  名前 nvarchar(20) not null
)
create table セッション参加者 (
  参加者Id int not null, 
  セッションId int not null, 
  primary key (参加者Id, セッションId)
)

insert into セッション values
(1, N'セッションA-1', 'A', 1), 
(2, N'セッションA-2', 'A', 2), 
(3, N'セッションA-3', 'A', 3), 
(4, N'セッションA-4', 'A', 4), 
(5, N'セッションB-1', 'B', 1), 
(6, N'セッションB-2', 'B', 2), 
(7, N'セッションB-3', 'B', 3), 
(8, N'セッションB-4', 'B', 4)

insert into 参加者 values
(1, N'参加者1'), 
(2, N'参加者2'), 
(3, N'参加者3'), 
(4, N'参加者4'), 
(5, N'参加者5'), 
(6, N'参加者6'), 
(7, N'参加者7'), 
(8, N'参加者8'), 
(9, N'参加者9'), 
(10, N'参加者10'), 
(11, N'参加者11'), 
(12, N'参加者12'), 
(13, N'参加者13'), 
(14, N'参加者14'), 
(15, N'参加者15'), 
(16, N'参加者16'), 
(17, N'参加者17'), 
(18, N'参加者18'), 
(19, N'参加者19'), 
(20, N'参加者20')

insert into セッション参加者 values
(1, 1), 
(5, 1), 
(10, 1), 
(11, 1), 
(2, 5), 
(3, 5), 
(4, 5), 
(6, 5), 
(7, 5), 
(8, 5), 
(9, 5), 
(12, 5), 
(13, 5), 
(15, 5), 
(17, 5), 
(20, 5),
(1, 2), 
(2, 2),
(3, 2),
(5, 2), 
(7, 2),
(9, 2), 
(10, 2), 
(11, 2),
(13, 2),
(16, 2),
(18, 2),
(4, 6), 
(6, 6), 
(8, 6), 
(12, 6),
(15, 6),
(17, 6),
(19, 6),
(1, 3), 
(3, 3), 
(6, 3), 
(7, 3),
(9, 3),
(10, 3),
(12, 3),
(14, 3),
(2, 7),
(4, 7),
(8, 7),
(11, 7),
(15, 7),
(16, 7),
(17, 7),
(18, 7),
(19, 7),
(20, 7),
(1, 4), 
(2, 4),
(3, 4),
(4, 4),
(5, 4),
(6, 4),
(7, 4),
(8, 4),
(9, 4),
(10, 4),
(11, 8),
(12, 8),
(13, 8),
(14, 8),
(15, 8),
(16, 8),
(17, 8),
(18, 8),
(19, 8),
(20, 8)

レコメンドなので普通に繋いでいきます。
お題は、
セッション.Id = 3 を受講している 参加者.Id = 1 以外の参加者が受講している他のセッションを受講者が多い順に並べる
でいきます。

select
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
  , count(*) as 受講者数
from
  G_参加者 対象参加者
  , G_セッション 対象セッション 
  , G_セッション参加者 対象セッション_E
  , G_参加者 対象セッション参加者_対象参加者除く 
  , G_セッション参加者 他の参加者が受けてるセッション
  , G_セッション レコメンドセッション
where
  対象参加者.Id = 1
  and 対象セッション.Id = 3
  and 対象セッション参加者_対象参加者除く.Id <> 対象参加者.Id
  and レコメンドセッション.Id <> 対象セッション.Id
  and match (対象セッション <- (対象セッション_E) - 対象セッション参加者_対象参加者除く 
             - (他の参加者が受けてるセッション) -> レコメンドセッション)
group by
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
order by 
  受講者数 desc

f:id:odashinsuke:20180108130217p:plain
そのままな感じの where の内容ですね。
match を使うことで繋がりが見やすくなってるのかな?

通常のテーブルはこちら。

select
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
  , count(*) as 受講者数
from
  参加者 対象参加者
  , セッション 対象セッション
  , セッション参加者 対象セッション参加者_対象参加者除く
  , セッション参加者 他の参加者が受けてるセッション
  , セッション レコメンドセッション
where
  対象参加者.Id = 1
  and 対象セッション.Id = 3
  and 対象セッション.Id = 対象セッション参加者_対象参加者除く.セッションId 
  and 対象参加者.Id <> 対象セッション参加者_対象参加者除く.参加者Id
  and 対象セッション参加者_対象参加者除く.参加者Id = 他の参加者が受けてるセッション.参加者Id
  and 他の参加者が受けてるセッション.セッションId <> 対象セッション.Id
  and 他の参加者が受けてるセッション.セッションId = レコメンドセッション.Id
group by
  レコメンドセッション.Id
  , レコメンドセッション.セッション名
  , レコメンドセッション.ルーム
  , レコメンドセッション.コマ
order by 
  受講者数 desc
and 対象セッション参加者_対象参加者除く.参加者Id = 他の参加者が受けてるセッション.参加者Id
and 他の参加者が受けてるセッション.セッションId = レコメンドセッション.Id

が match

match (対象セッション <- (対象セッション_E) - 対象セッション参加者_対象参加者除く - (他の参加者が受けてるセッション) -> レコメンドセッション)

の部分の代替ですが、そんな変わらないかな?

レコメンドの例は、Ignite
Graph extensions in Microsoft SQL Server 2017 and Azure SQL Database
でも紹介されていましたが、こちらは通常のテーブルを使ったクエリの書き方が「非常に悪い」ためグラフテーブルのクエリが見やすく感じる内容になってます。

ですが、実際に書いた感じだとそんな変わらないかな?
SQL Server のグラフで最短経路と取ってみる - お だ のスペース
SQL Server のグラフで巡回セールスマン問題をやってみる - お だ のスペース
前回、前々回と色々試してみましたが、クエリ書いた感じは通常のテーブルとそんな変わらないですが、パフォーマンスはどうなんでしょうねー?
グラフについては、今後の機能追加でクエリが書きやすくなるそうなので、そこに期待ですね。

今回も Neo4j の cypher 貼っときますが、そんな違いないかな?

create (s1:Session{Id:1, Name:"セッションA-1", Room:"A", Schedule:1}), 
  (s2:Session{Id:2, Name:"セッションA-2", Room:"A", Schedule:2}),
  (s3:Session{Id:3, Name:"セッションA-3", Room:"A", Schedule:3}), 
  (s4:Session{Id:4, Name:"セッションA-4", Room:"A", Schedule:4}), 
  (s5:Session{Id:5, Name:"セッションB-1", Room:"B", Schedule:1}), 
  (s6:Session{Id:6, Name:"セッションB-2", Room:"B", Schedule:2}),
  (s7:Session{Id:7, Name:"セッションB-3", Room:"B", Schedule:3}), 
  (s8:Session{Id:8, Name:"セッションB-4", Room:"B", Schedule:4}),  
  (u1:Attendee{Id:1, Name:"参加者1"}),
  (u2:Attendee{Id:2, Name:"参加者2"}),
  (u3:Attendee{Id:3, Name:"参加者3"}),
  (u4:Attendee{Id:4, Name:"参加者4"}),
  (u5:Attendee{Id:5, Name:"参加者5"}),
  (u6:Attendee{Id:6, Name:"参加者6"}),
  (u7:Attendee{Id:7, Name:"参加者7"}),
  (u8:Attendee{Id:8, Name:"参加者8"}),
  (u9:Attendee{Id:9, Name:"参加者9"}),
  (u10:Attendee{Id:10, Name:"参加者10"}),
  (u11:Attendee{Id:11, Name:"参加者11"}),
  (u12:Attendee{Id:12, Name:"参加者12"}),
  (u13:Attendee{Id:13, Name:"参加者13"}),
  (u14:Attendee{Id:14, Name:"参加者14"}),
  (u15:Attendee{Id:15, Name:"参加者15"}),
  (u16:Attendee{Id:16, Name:"参加者16"}),
  (u17:Attendee{Id:17, Name:"参加者17"}),
  (u18:Attendee{Id:18, Name:"参加者18"}),
  (u19:Attendee{Id:19, Name:"参加者19"}),
  (u20:Attendee{Id:20, Name:"参加者20"}),
  (u1)-[:Attend]->(s1),
  (u5)-[:Attend]->(s1),
  (u10)-[:Attend]->(s1),
  (u11)-[:Attend]->(s1),
  (u2)-[:Attend]->(s5),
  (u3)-[:Attend]->(s5),
  (u4)-[:Attend]->(s5),
  (u6)-[:Attend]->(s5),
  (u7)-[:Attend]->(s5),
  (u8)-[:Attend]->(s5),
  (u9)-[:Attend]->(s5),
  (u12)-[:Attend]->(s5),
  (u13)-[:Attend]->(s5),
  (u15)-[:Attend]->(s5),
  (u17)-[:Attend]->(s5),
  (u20)-[:Attend]->(s5),
  (u1)-[:Attend]->(s2),
  (u2)-[:Attend]->(s2),
  (u3)-[:Attend]->(s2),
  (u5)-[:Attend]->(s2),
  (u7)-[:Attend]->(s2),
  (u9)-[:Attend]->(s2),
  (u10)-[:Attend]->(s2),
  (u11)-[:Attend]->(s2),
  (u13)-[:Attend]->(s2),
  (u16)-[:Attend]->(s2),
  (u18)-[:Attend]->(s2),
  (u4)-[:Attend]->(s6),
  (u6)-[:Attend]->(s6),
  (u8)-[:Attend]->(s6),
  (u12)-[:Attend]->(s6),
  (u15)-[:Attend]->(s6),
  (u17)-[:Attend]->(s6),
  (u19)-[:Attend]->(s6),
  (u1)-[:Attend]->(s3),
  (u3)-[:Attend]->(s3),
  (u6)-[:Attend]->(s3),
  (u7)-[:Attend]->(s3),
  (u9)-[:Attend]->(s3),
  (u10)-[:Attend]->(s3),
  (u12)-[:Attend]->(s3),
  (u14)-[:Attend]->(s3),
  (u2)-[:Attend]->(s7),
  (u4)-[:Attend]->(s7),
  (u8)-[:Attend]->(s7),
  (u11)-[:Attend]->(s7),
  (u15)-[:Attend]->(s7),
  (u16)-[:Attend]->(s7),
  (u17)-[:Attend]->(s7),
  (u18)-[:Attend]->(s7),
  (u19)-[:Attend]->(s7),
  (u20)-[:Attend]->(s7),
  (u1)-[:Attend]->(s4),
  (u2)-[:Attend]->(s4),
  (u3)-[:Attend]->(s4),
  (u4)-[:Attend]->(s4),
  (u5)-[:Attend]->(s4),
  (u6)-[:Attend]->(s4),
  (u7)-[:Attend]->(s4),
  (u8)-[:Attend]->(s4),
  (u9)-[:Attend]->(s4),
  (u10)-[:Attend]->(s4),
  (u11)-[:Attend]->(s8),
  (u12)-[:Attend]->(s8),
  (u13)-[:Attend]->(s8),
  (u14)-[:Attend]->(s8),
  (u15)-[:Attend]->(s8),
  (u16)-[:Attend]->(s8),
  (u17)-[:Attend]->(s8),
  (u18)-[:Attend]->(s8),
  (u19)-[:Attend]->(s8),
  (u20)-[:Attend]->(s8)
match (s:Session {Id:3})<-[:Attend]-(a:Attendee)-[:Attend]->(r:Session) 
where a.Id <> 1 
return r, count(*) as cnt 
order by cnt desc

01/23(火) SQLWorld★大阪#45 開催します

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

【日時】
2018年01月23日(火曜日) 19:00~21:00
 

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

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

【参加費】
無料
 

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

【参加可能人数】
13 人
 

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

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