スカラー値を返すクエリを書いてて、bit 型の列が複数行返ってくるけど、true 優先で取りたい時に、bit 型そのままだと max、min 使えないので、order by で。
雑なクエリ例
select top(1) [flg] from ( select cast(0 as bit) as [flg] union all select cast(1 as bit) -- union all .... ) _t order by [flg] desc
bit (Transact-SQL) - SQL Server | Microsoft Docs
bit は、
1、0、または NULL の値をとる整数型です。
なので order by で問題なし。
雰囲気そうなんだけど、ちゃんとドキュメント見ようねっていうメモ。
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 数に一致させる
OSC2020 Online/Kyoto で 関西DB勉強会として1枠頂き、データベース座談会 をさせていただきます。
データベース座談会 - セミナープログラム - オープンソースカンファレンス2020 Online/Kyoto
MySQL、PostgreSQL、SQL Server とでちょっとした比較みたいなお話しが出来たら～と準備していますので、ぜひお気軽にご参加ください～。
オンラインでの発表は初めてなのでちょい不安。。
SQLWorld はオンラインでの勉強会開催は計画していませんが、
関西DB勉強会はオンラインでも開催していきそうな方向になっていますので、
今後ともよろしくお願いします～。
今回で8回目(9年目)の受賞になりました。
今後もより一層のコミュニティ活動/情報発信していきますので、SqlWorld :: ホーム 共々宜しくお願いします。
どんどん新しい製品、サービスが出てますがおいてかれないように頑張りま～。
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
減らなさそうっすね。
メモ
通常ケースのテーブルがあって、特定の時は上書きしたデータが欲しいってケースのクエリ。
通常、特定ともに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 [通常]
複数行ある場合は、上書きのを 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 [通常]
調べる機会があったので。
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 に書いてある順番通りでした。
ただ 最初に 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
