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

SQL Server 勉強会

今回で8回目(9年目)の受賞になりました。
今後もより一層のコミュニティ活動/情報発信していきますので、SqlWorld :: ホーム 共々宜しくお願いします。
どんどん新しい製品、サービスが出てますがおいてかれないように頑張りま～。

メモ：SQL Server 共通テーブル式でリードが減るかの確認 (減らない)

SQL Server

WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs

試したバージョンは

Microsoft SQL Server 2019 (RTM-GDR) (KB4517790) - 15.0.2070.41 (X64)   Oct 28 2019 19:56:59   Copyright (C) 2019 Microsoft Corporation  Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: ) (Hypervisor)

共通テーブル式で用意した名前付き結果セットを複数回呼んだ時に、実テーブルのリードは1回で済むのか呼んだ回数リードするのかの確認。
取り合えず適当にデータを用意

create table [注文明細] (
  [Id] bigint not null,
  [商品Id] bigint not null
)
create table [商品] (
  [Id] bigint not null,
  [名前] varchar(20) not null
)
;
begin tran
;
with [cte] ([seq]) as (select 1 as [seq] union all select [seq] + 1 from [cte] where [seq] < 2000 )

insert into [商品] 
select
  [seq]
  , cast([seq] as char)
from [cte]
where [seq] < 2000
option (maxrecursion 0)
;

with [cte] ([seq]) as (select 1 as [seq] union all select [seq] + 1 from [cte] where [seq] < 20000 )

insert into [注文明細] 
select
  [seq]
  , cast(([seq] * (rand() * 1000)) as int) % 2000
from [cte]
where [seq] < 20000
option (maxrecursion 0)

commit

んで、SET STATISTICS IO (Transact-SQL) - SQL Server | Microsoft Docs で確認

print(N'2回')
set statistics io on;

with cte (Id) as (select Id from 商品 where 名前 like '%8%')
select * from 注文明細 where 商品Id in (select Id from cte) or 商品Id in (select Id from cte)

set statistics io off

print(N'1回')

set statistics io on;

with cte (Id) as (select Id from 商品 where 名前 like '%8%')
select * from 注文明細 where 商品Id in (select Id from cte)

set statistics io off

結果

2回
テーブル 'Workfile'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル '注文明細'。スキャン数 1、論理読み取り数 62、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル '商品'。スキャン数 2、論理読み取り数 22、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
1回
テーブル 'Workfile'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル '注文明細'。スキャン数 1、論理読み取り数 62、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル '商品'。スキャン数 1、論理読み取り数 11、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

完了時刻: 2020-06-25T13:18:57.5887353+09:00

減らなさそうっすね。

メモ：Aテーブルに1行も無い時だけBテーブルから取得するクエリ

SQL Server

メモ

通常ケースのテーブルがあって、特定の時は上書きしたデータが欲しいってケースのクエリ。

通常、特定ともにmax1行の時は、単に両方 left join でつないで、coalesce(isnull) でOK。

SQL Server の例だと

-- max1件のケース
create table [通常] (
  [種別] int not null primary key,
  [数量] int,   
)
create table [上書き] (
  [種別] int not null primary key,
  [数量] int,
)

create table [データ] (
  [Id] bigint not null primary key,
  [種別] int not null,
)

insert into [通常] values 
(1, 10),
(2, 100),
(3, 1000)
insert into [上書き] values 
(1, 5)
insert into [データ] values 
(1, 1),
(2, 2),
(3, 3)

select
  [データ].[Id]
  , [データ].[種別]
  , coalesce([上書き].[数量], [通常].[数量]) as [数量]
from
  [データ] left join [上書き]
    on [データ].[種別] = [上書き].[種別]
  left join [通常] 
    on [データ].[種別] = [通常].[種別]

drop table [データ]
drop table [上書き]
drop table [通常]

f:id:odashinsuke:20200622183316p:plain

複数行ある場合は、上書きのを left join で繋いだあとに、通常を left join で繋ぐ条件に、上書きのデータが無いという条件を足せばOK。

-- 複数件あり得る
create table [通常] (
  [種別] int not null,
  [サブ種別] int not null,
  [数量] int, 
  primary key ([種別], [サブ種別])
)
create table [上書き] (
  [種別] int not null,
  [サブ種別] int not null,
  [取引先Id] bigint not null,
  [数量] int,
  primary key ([種別], [サブ種別], [取引先Id])
)

create table [データ] (
  [Id] bigint not null primary key,
  [種別] int not null,
  [取引先Id] bigint
)

insert into [通常] values 
(1, 1, 1),
(1, 2, 5),
(1, 3, 10),
(2, 1, 50),
(2, 2, 100),
(3, 1, 1000),
(3, 2, 10000)
insert into [上書き] values
(1, 1, 1, 30),
(1, 2, 1, 50),
(3, 1, 2, 20)
insert into [データ] values 
(1, 1, null),
(2, 1, 1),
(3, 1, 2),
(4, 2, null),
(5, 2, 1),
(6, 2, 2),
(7, 3, null),
(8, 3, 1),
(9, 3, 2)

select
  [データ].[Id]
  , [データ].[種別]
  , coalesce([上書き].[サブ種別], [通常].[サブ種別]) as [サブ種別]
  , coalesce([上書き].[数量], [通常].[数量]) as [数量]
from
  [データ] left join [上書き]
    on [データ].[種別] = [上書き].[種別]
    and [データ].[取引先Id] = [上書き].[取引先Id]
  left join [通常] 
    on [上書き].[種別] is null
    and [データ].[種別] = [通常].[種別]


drop table [データ]
drop table [上書き]
drop table [通常]

f:id:odashinsuke:20200622183521p:plain

SQL Server スキーマ(Schema)指定無しの時のオブジェクトの検索順

SQL Server

調べる機会があったので。

On Default Schemas and "Search Paths" - SQL Server Science
sql server - SQL default schema resolution in Stored Procedure - Stack Overflow
Dude, where's my table? - Bob Beauchemin

幾つか記事はありますが、足りなかったり、古かったりしたので 2019 の環境で検証かねて試しました。

試した感じ概ね Dude, where's my table? - Bob Beauchemin に書いてある順番通りでした。

  • クエリ
    1. ユーザーのデフォルトスキーマ
    2. dbo
  • ファンクション、ストアド
    1. ファンクション、ストアドのスキーマ
    2. dbo ※sys スキーマについては未検証。

ただ 最初に sys を探すってのが謎。 2005 の時はそうだったんかな？

use [master]
go
create database [hoge]
go

use [hoge]
go
select * from sys.tables
select * from tables -- これで sys.tables を見つけてくれない
go

何か読み間違えてる？

検証クエリは gist に貼ってます。
ログイン作るの面倒なので包含データベース使ってます。
包含データベースへの包含ユーザー アクセス - SQL Server | Microsoft Docs
包含データベース許可してない場合は↓しといてください。

sp_configure 'contained database authentication', 1;  
GO  
RECONFIGURE;  
GO

T-SQL スキーマ(Schema)無しでの呼出しでどのスキーマのものが使われるのか

メモ：SSMS でクエリの結果をカンマ区切りで出力する

SQL Server

CSVファイルに吐く場合は、
デフォルトの結果をグリッドに表示(Ctrl + D)で出力された結果を
右クリック => 結果に名前を付けて保存 で CSV に。
右クリックが面倒な人は、

メニューの ツール => オプション => クエリ結果 => SQL Server => 
結果をテキストで表示 => 出力形式：コンマ区切り

に変更して、SSMS を再起動した後に、結果をファイルに出力(Ctrl + Shift + F)でカンマ区切りされた *.rpt ファイルが吐かれる。

.rpt に出力件数と完了時刻が表示される？

出力件数は、set nocount on で。

set nocount on
select ～ -- 結果を出力したいクエリ

set nocount on 書くの面倒なら、オプションで設定。

メニューの ツール => オプション => クエリ実行 => SQL Server => 
詳細設定 => SET NOCOUNT をチェックする

完了時刻は SSMS18.3.1 以降なら設定出来そう。
Release notes for (SSMS) - SQL Server Management Studio (SSMS) | Microsoft Docs

The Completion time message visible in the Result Grid/File (introduced in SSMS 18.2) is now configurable under Tools > Options > Query Execution > SQL Server > Advanced > Show completion time.
メニューの ツール => オプション => クエリ実行 => SQL Server => 
詳細設定 => 完了時刻の表示のチェックを外す

これで *.rpt に件数と完了時刻は入らない。*1

*1:余分な改行は残ってるけど…

メモ：全 Temporal Table を無効化するクエリ

SQL Server

Visual Studio の Database Project で Temporal Table 使ってると公開が失敗することがある - お だ のスペース
で、度々引っかかるのでウンザリしてクエリ書いた。

declare @sql nvarchar(max) 

select @sql = concat(@sql, N'alter table ', [sys].[tables].[name] collate Japanese_CI_AS, N' set (system_versioning = off);')
from [sys].[tables] 
where [sys].[tables].[temporal_type] = 2

-- 一応クエリ確認
select @sql

exec (@sql)

テーブル名に指定してる collate は不要な環境なら外して使ってください。