Microsoft MVP アワード (Data Platform) を受賞させていただきました

SQL Server

今回で6回目(7年目)の受賞になりました。

今後もより一層のコミュニティ活動/情報発信していきますので、SqlWorld :: ホーム 共々宜しくお願いします。　　

最近もうちょい情報発信しないとなーと思いつつも出来てないので、次はどうなることやら。。

07/24(火) SQLWorld★大阪#47 開催します

SQL Server 勉強会

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

【日時】
2018年07月24日（火曜日）　19:00~21:00
 

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

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

【参加費】
無料
 

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

【参加可能人数】
13 人
 

お題に沿って、SQL を書いてみようという勉強会です。是非ご参加を～。
今回は初心者向けの優しめの問題を考え中です。

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

ASP.NET Core 2.1 を IIS でホストする時にハマったメモ

.NET IIS

Host ASP.NET Core on Windows with IIS | Microsoft Docs
これの通りにやっとけば問題無いです。

検証した環境は、Windows Server 2008 R2 SP1 です。

ミスったのは install-the-net-core-hosting-bundle のとこ。
Hosting Bundle をインストールする時に、OPT_NO_X86=1 を指定していなくて x86版もインストールしていた事。

これのせいで、
Dreaded ErrorCode = '0x80070002 on IIS · Issue #1234 · aspnet/Hosting · GitHub
と同じ事象になりました。

↓のように web.config で dotnot のパスを直書きしたら直るのですが、x86版の .NET Core をアンインストールしても直りました。
How do I fix HTTP Error 502.5 - Process Failure when... - Gordon Beeming

再検証したけど、インストールする時にコマンドから OPT_NO_X86 を指定したら大丈夫でした。
dotnet-hosting-2.1.0-win.exe OPT_NO_X86=1

ちなみに Core 2.0 を Core 2.0.8 の Hosting Bundle で動かした時は、x86 インストールしててもデフォルトのままで動きました。

Azure SQL DB の FileStream サポートが unplanned に

SQL Server Microsoft Azure

Implement FILESTREAM for blobs into Azure Blob Store – Customer Feedback for Microsoft Azure

まだ閉じてはないので、もっと Vote したら変わるかもー。

One of the main benefits of using FileStream is that your blob data and the corresponding database record don't get out of sync. If you have to use two separate systems to store blobs with related data, you have to write a lot of code just to keep them in sync, and deal with all the possible scenarios where one or the other might have an issue.

ほんこれ。。

VSTS の ビルドで SQL Server LocalDB を使ってテストしたい

Microsoft Azure SQL Server .NET

わざわざ SQL DB 立てたくなかったので LocalDB 使えるか調べたメモ

こんなのを見つけたので出来そうな感じでしたが、
Database Integration Tests in Visual Studio Team Services and Cake Build | Trailmax Tech


で教えてくれたので特に何もせず接続文字列だけ LocalDB にして動かす形にしてます。

VSTS の Hosted VSTS2017 agent で動かすので、LocalDB は既定のインスタンス(MSSQLLocalDB)で問題無しですね。*1
ちなみにバージョンは 2018/05/07 時点で SQL Server 2016 SP1 でした。

Database の作成や初期データは、ビルド実行前に *.bacpac で投入する方法にしてます。
※個別のテスト用のデータは、テスト毎に投入しますが不変なデータも毎回入れるのは面倒なので事前に用意する形です。

bacpac は、ざっくりいうと スキーマとデータをパッケージングしたファイルです。
Data-tier Applications | Microsoft Docs
これがあれば、SQL DB / SQL Server にデータベース(スキーマとデータ)丸ごとインポートすることが出来ます。

bacpac のインポートは、PowerShell でさくっと出来ます。
このスクリプトを VSTS のビルド定義 PowerShell から呼び出すだけ。

$dir = [System.IO.Path]::GetDirectoryName($myInvocation.MyCommand.Definition)

Add-Type -Path "$dir\Microsoft.SqlServer.Dac.dll"
$srv = New-Object Microsoft.SqlServer.Dac.DacServices "Data Source=(localdb)\MSSQLLocalDB;Pooling=False"
$package = [Microsoft.SqlServer.Dac.BacPackage]::Load("$dir\Sample.bacpac")
$srv.ImportBacpac($package, "Sample")

気を付ける点は、Microsoft.SqlServer.Dac.dll 読むのには、依存関係にある dll も読み込める場所に配置しとく必要がある位ですかね。
今回は、
NuGet Gallery | Microsoft.SqlServer.DacFx.x64 140.3881.1
を使いましたが、Microsoft.SqlServer.Dac.dll を含む6つの dll を *.ps1 と同じ場所に置いてます。

接続文字列は 環境変数 にあればそこから取る形にしてます。*2
VSTS で実行する時は、ビルド定義で接続文字列埋め込む形です。

using System;
using System.Data.SqlClient;
using Xunit;
using Dapper;
using Xunit.Abstractions;

public class UnitTest1 {
  [Fact]
  public void Test1() {
    var connstr = Environment.GetEnvironmentVariable("VSTS_CONNSTR")
      ?? @"普段使う接続文字列";
    using (var conn = new SqlConnection(connstr)) {
      Assert.Equal("Test", conn.ExecuteScalar<string>(
          @"select Name from Table1 where Id = 1"));
    }
  }
}

VSTS の ビルドで 環境変数の設定はこちらを参考に。
Build variables | Microsoft Docs
指定する接続文字列はこんな感じで。

Server=(localdb)\MSSQLLocalDB;Initial Catalog=Sample;Pooling=false

*1:他から繋がないし

*2:開発環境でのテストは、普通の SQL Server 使いたい

SQL Server での JSON を表として扱う & インデックスのお話し #dekan

SQL Server 勉強会

この前の 第8回 関西DB勉強会 - connpass で、
ついにリリース！！ MySQL 8.0 最新情報
こちらのお話しをされてたときに、 JSON_TABLE と Generated Column で JSON にインデックスを作る話しの箇所で会場が盛り上がってたので、 SQL Server でも同じこと出来るよーっていうお話し。

クエリ書いて紹介していくので、JSON が入ったテーブルを用意しときます。
一応 JSON しか入らないように CHECK制約掛けときます。

drop table if exists Table1
create table Table1 (
  loto6json nvarchar(200) not null 
    constraint C_Table1_JSON 
      check(isjson(loto6json) > 0)
)

insert into Table1 values 
('{"no":"第1回","date":"2000年10月5日","numbers":["02","08","10","13","27","30"],"bonus":"39"}'),
('{"no":"第2回","date":"2000年10月12日","numbers":["01","09","16","20","21","43"],"bonus":"05"}'),
('{"no":"第3回","date":"2000年10月19日","numbers":["01","05","15","31","36","38"],"bonus":"13"}'),
('{"no":"第4回","date":"2000年10月26日","numbers":["16","18","26","27","34","40"],"bonus":"13"}'),
('{"no":"第5回","date":"2000年11月2日","numbers":["09","15","21","23","27","28"],"bonus":"43"}')

最初は JSON_TABLE の方からいきましょう。
MySQL の JSON_TABLE はこんなのです。
MySQL :: MySQL 8.0 Reference Manual :: 12.16.6 JSON Table Functions
JSON を表形式にしてくれる感じなんですかね。

これと同等？の動きをするのは
OPENJSON (Transact-SQL) | Microsoft Docs
かな。
OPENJSON は テーブル値関数なので CROSS APPLY と併用する形になるかと。

select [no], [date], numbers, bonus 
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  )

これの結果がこんなのになります。

no date numbers bonus
第1回 2000年10月5日 ["02","08","10","13","27","30"] 39
第2回 2000年10月12日 ["01","09","16","20","21","43"] 5
第3回 2000年10月19日 ["01","05","15","31","36","38"] 13
第4回 2000年10月26日 ["16","18","26","27","34","40"] 13
第5回 2000年11月2日 ["09","15","21","23","27","28"] 43

numbers も分解したい場合はもう一回 OPENJSON 使う感じで。
データ多くなるので条件絞っときます。

select [no], [date], [value], bonus
from Table1 cross apply openjson(Table1.loto6json) 
  with (
    [no] nvarchar(10)
    , [date] nvarchar(11)
    , numbers nvarchar(max) '$.numbers' as json
    , bonus int
  ) nest1 cross apply openjson(nest1.numbers) 
  with ( [value] int '$' )
where [no] = N'第1回'

結果はこんな感じ。ちゃんと取れてますね。

no date value bonus
第1回 2000年10月5日 2 39
第1回 2000年10月5日 8 39
第1回 2000年10月5日 10 39
第1回 2000年10月5日 13 39
第1回 2000年10月5日 27 39
第1回 2000年10月5日 30 39

次は Generated Column で JSON にインデックス の方にです。

MySQL でのお話しはここら辺っぽいです。
MySQL :: MySQL 8.0 Reference Manual :: 8.3.11 Optimizer Use of Generated Column Indexes

これと同等の動きをするのは 計算列でのインデックス ですね。
ドキュメントはこれかな？
Index JSON data | Microsoft Docs

SQL Server の計算列に対するインデックスについては、
Indexes on Computed Columns | Microsoft Docs
こちらをどうぞ。

では、計算列を作って index を貼ります。

alter table Table1 add 
  [date] as json_value(loto6json, '$.date')
create index idx_Table1_date on Table1([date])

実行すると警告出ますが無視します。

警告: 非クラスター化 インデックスの最大キー長は 1700 バイトです。インデックス 'idx_Table1_date' の最大長は 8000 バイトです。大きな値の組み合わせの一部では挿入操作または更新操作が失敗します。

計算列の結果が 8000 バイト超える場合は、エラーになってしまうよ！っていう警告です。今回は超えないのが分かっているので無視です。

データが少なすぎて index 使われないので適当にデータを増やしてからクエリを実行すると…

select loto6json from Table1 
where [date] = N'2000年10月5日'

こんな実行プランになりました。 Index Seek になってますね！
f:id:odashinsuke:20180506114736j:plain
ちなみに 計算列 まで取ってしまうと、ここで計算します。

select * from Table1
where [date] = N'2000年10月5日'

f:id:odashinsuke:20180506114805j:plain

ちなみに SQL Server の計算列には、PERSISTED オプションで永続化出来ますが、参照時に永続化の値を使うことは少なそうです。。
今回の例で試しましたが select で Compute Scalar してました。
調べると幾つか出てきますねー。
浮動小数点型の計算列でインデックスを貼りたい時と、パーティションテーブルで使う以外は PERSISTED は使わない感じかなー。
performance - Why does the Execution Plan include a user-defined function call for a computed column that is persisted? - Stack Overflow
Properly Persisted Computed Columns - SQLPerformance.com