DO's&DONT's リンク

Microsoft SQL Server Japan Support Team Blog が無くなって、Technet フォーラムに移ってますが DO's&DONT's を人に紹介する機会があったのでついでに直リンメモ。

空き番ありますが、投稿見つからないので移行されなかった?

DO's&DONT's #1: やらない方がいいこと – 運用環境で、Profiler GUI を使用してトレースする
DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる 前編
DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる 後編
DO's&DONT's #3: やらなければいけないこと - 非典型的パラメータ値が存在する場合の再コンパイル
DO's&DONT's #4: やらない方がいいこと - クエリの 条件句 (WHERE や JOIN ON 等) で参照されている列の加工
DO's&DONT's #6: 絶対にやってはいけないこと – ストアドプロシージャ内でのパラメータ値の変更
DO's&DONT's #7: やらない方がいいこと - インデックス再構築 (REBUILD) 後のデータファイル圧縮 (SHRINK)
DO's&DONT's #8: やってはいけないこと - インデックス再構築 (REBUILD) 後のインデックス統計情報更新 (UPDATE STATISTICS)
DO’s&DONT’s #10: やらない方がいいこと - クエリの条件句で変数を参照する
DO’s&DONT’s #12: やった方がいいこと - max server memory を設定する
DO’s&DONT’s #14: 絶対にやってはいけないこと - ひとつの CPU に対して affinity mask と affinity I/O mask の両方を ON にする
DO’s&DONT’s #16: やってはいけないこと - ログ配布プライマリデータベースのログバックアップ
DO’s&DONT’s #17: やっておいた方がいいこと - tempdb データファイル数を CPU 数に一致させる

08/29(土) OSC2020 Online/Kyoto で データベース座談会 をします!

OSC2020 Online/Kyoto で 関西DB勉強会として1枠頂き、データベース座談会 をさせていただきます。
データベース座談会 - セミナープログラム - オープンソースカンファレンス2020 Online/Kyoto

MySQLPostgreSQLSQL Server とでちょっとした比較みたいなお話しが出来たら~と準備していますので、ぜひお気軽にご参加ください~。

オンラインでの発表は初めてなのでちょい不安。。

SQLWorld はオンラインでの勉強会開催は計画していませんが、
関西DB勉強会はオンラインでも開催していきそうな方向になっていますので、
今後ともよろしくお願いします~。

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

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

メモ: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テーブルから取得するクエリ

メモ

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

通常、特定ともに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)指定無しの時のオブジェクトの検索順

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

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)無しでの呼出しでどのスキーマのものが使われるのか