meetup app osaka@8 で SQL Server 2022 の話しました。 #meetupapp

01/20(土) meetup app osaka@8 で SQL Server 2022 の話しをちょっとします。 #meetupapp - お だ のスペース

結構便利になってます!
スライドは大したこと書いてないので、デモだけ載せときます。

select ... windows

window 関数 楽に書けるよ
over 句以降を別名(window)として、参照出来るよ
PostgreSQL にはあるみたいですが、SQL Server 2022 からサポート。

create table [成績] (
  [回数] int
  , [氏名] nvarchar(20)
  , [教科] nvarchar(10)
  , [点数] int
)

insert into [成績] ([回数], [氏名], [教科], [点数]) values 
(1, N'伊藤', N'国語', 65)
, (1, N'伊藤', N'算数', 80)
, (1, N'鈴木', N'国語', 47)
, (1, N'鈴木', N'算数', 52)
, (1, N'佐藤', N'国語', 92)
, (1, N'佐藤', N'算数', 34)
, (1, N'田中', N'国語', 88)
, (1, N'田中', N'算数', 75)
, (2, N'伊藤', N'国語', 68)
, (2, N'伊藤', N'算数', 72)
, (2, N'鈴木', N'国語', 50)
, (2, N'鈴木', N'算数', 25)
, (2, N'佐藤', N'国語', 88)
, (2, N'佐藤', N'算数', 42)
, (2, N'田中', N'国語', 78)
, (2, N'田中', N'算数', 66)
, (3, N'伊藤', N'国語', 61)
, (3, N'伊藤', N'算数', 96)
, (3, N'鈴木', N'国語', 47)
, (3, N'鈴木', N'算数', 52)
, (3, N'佐藤', N'国語', 91)
, (3, N'佐藤', N'算数', 51)
, (3, N'田中', N'国語', 83)
, (3, N'田中', N'算数', 89)
select 
  [回数]
  , [氏名]
  , [教科]
  , [点数]
  , max([点数]) over (partition by [回数], [教科]) as [最高点]
  , [点数] - max([点数]) over (partition by [回数], [教科]) as [最高点との差]
  , avg([点数]) over (partition by [回数], [教科]) as [平均点]
  , [点数] - avg([点数]) over (partition by [回数], [教科]) as [平均点との差]
  , sum([点数]) over (partition by [氏名], [教科] order by [回数]) as [教科累計点(回数時点)]
  , sum([点数]) over (partition by [氏名], [教科]) as [教科累計点(全体)]
from 
  [成績]
order by
  [回数]
  , [氏名]
  , [教科]
select 
  [回数]
  , [氏名]
  , [教科]
  , [点数]
  , max([点数]) over [回数教科win] as [最高点]
  , [点数] - max([点数]) over [回数教科win] as [最高点との差]
  , avg([点数]) over [回数教科win] as [平均点]
  , [点数] - avg([点数]) over [回数教科win] as [平均点との差]
  , sum([点数]) over [氏名教科_累計win] as [教科累計点(回数時点)]
  , sum([点数]) over [氏名教科win] as [教科累計点(全体)]
from 
  [成績]
window 
  [回数教科win] as (partition by [回数], [教科])
  , [氏名教科win] as (partition by [氏名], [教科])
  , [氏名教科_累計win] as ([氏名教科win] order by [回数])
/* 参照する window で定義されていない物だけを追加出来る partition by, order by , rows or range */
/* OK
  , [回数累計win] as (order by [回数])
  , [氏名教科_累計win2] as ([回数累計win] partition by [氏名], [教科])
*/
/* NG
  [教科win] as (partition by [教科])
  , [回数教科win] as ([教科win] partition by [回数]
*/
order by
  [回数]
  , [氏名]
  , [教科]

is [not] distinct from

null 気にせずに等価比較出来るやつ

create table [Null気にしない!] (
  [Id] int not null
  , [値] int
)
insert into [Null気にしない!] ([Id], [値]) values 
(1, 100)
, (2, null)
, (3, 90)
, (4, 90)
, (5, 100)
, (6, 85)

値が 100 以外のデータを抽出

select *
from [Null気にしない!]
where [値] <> 100 -- null 省かれる
  or [値] is null
select *
from [Null気にしない!]
where [値] is distinct from 100

値 が @v 以外のデータを抽出

declare @v int = 100
select *
from [Null気にしない!]
where [値] <> @v -- null 省かれる
  or [値] is null
-- @v が null だったら?
set @v = null
/* null 以外全てになるので、↓だと意図した結果にならない。
select *
from [Null気にしない!]
where [値] <> @v -- null 省かれる
  or [値] is null
*/

select *
from [Null気にしない!]
where (
  @v is not null
  and (
    [値] <> @v
    or [値] is null
  )
) or (
  @v is null 
  and [値] is not null
)
declare @v int = 100
select *
from [Null気にしない!]
where [値]  is distinct from @v
-- @v が null だったら?
set @v = null
select *
from [Null気にしない!]
where [値]  is distinct from @v

値が @v のデータを抽出

declare @v int = 100
select *
from [Null気にしない!]
where [値] = @v -- OK

set @v = null
select *
from [Null気にしない!]
where [値] = @v -- NG

-- 両方対応しようとすると
select *
from [Null気にしない!]
where (
  @v is not null
  and [値] = @v
) or (
  @v is null
  and [値] is null
)
declare @v int = 100
select * 
from [Null気にしない!]
where [値] is not distinct from @v

date_bucket

ここからしばらく時系列データ絡み

日時をバケットに分けてバケットの開始日時を返す

declare 
    @date date = '2024-01-20',
    @origin date = '2024-01-01';
select 
    date_bucket(day, 1, @date, @origin) as "1 day", -- 1/1, 1/2, 1/3, ..., 1/20
    date_bucket(day, 2, @date, @origin) as "2 days", -- 1/1, 1/3, 1/5, 1/7, ..., 1/19, 1/21
    date_bucket(day, 3, @date, @origin) as "3 days", -- 1/1, 1/4, 1/7, ..., 1/19, 1/22
    date_bucket(day, 4, @date, @origin) as "4 days"; -- 1/1, 1/5, 1/9, ..., 1/17, 1/21
select 
    date_bucket(week, 1, @date, @origin) as "1 week", -- 1/1, 1/8, 1/15, 1/22
    date_bucket(week, 2, @date, @origin) as "2 weeks", -- 1/15, 1/29
    date_bucket(week, 3, @date, @origin) as "3 weeks", -- 1/22
    date_bucket(week, 4, @date, @origin) as "4 weeks"; -- 1/29
declare @datetime datetime2 = '2024-01-20 16:32:22'
declare @originDateTime datetime2 = '2024-01-20 13:00:00'
select 
    date_bucket(hour, 1, @datetime, @originDateTime) as "1 hour", -- 13:00, 14:00, 15:00, 16:00, 17:00
    date_bucket(hour, 2, @datetime, @originDateTime) as "2 hours", -- 13:00, 15:00, 17:00
    date_bucket(hour, 3, @datetime, @originDateTime) as "3 hours", -- 13:00, 16:00, 19:00
    date_bucket(hour, 4, @datetime, @originDateTime) as "4 hours"; -- 13:00, 17:00, 21:00

実例

create table [受注] (
  [受注日] date
  , [受注金額] int
)
insert into [受注] ([受注日], [受注金額]) values
('2024-01-01', 12000)
, ('2024-01-01', 8000)
, ('2024-01-02', 6000)
, ('2024-01-02', 6700)
, ('2024-01-03', 16000)
, ('2024-01-03', 16700)
, ('2024-01-04', 20000)
, ('2024-01-05', 18000)
, ('2024-01-11', 8000)
, ('2024-01-12', 6000)
, ('2024-01-12', 6700)
, ('2024-01-13', 16000)
, ('2024-01-13', 16700)
, ('2024-01-14', 20000)
, ('2024-01-15', 18000)

-- 受注日毎の合計金額
select
  [受注日]
  , sum([受注金額])
from [受注]
group by [受注日]
-- 1/1 から週単位の合計金額
select
  date_bucket(week, 1, [受注日], cast('2024-01-01' as date)) as [基準日]
  , sum([受注金額]) as [合計金額]
from [受注]
group by date_bucket(week, 1, [受注日], cast('2024-01-01' as date))
-- 使わずに書くと…
select
  cast(dateadd(day, (datediff(day, '2024-01-01', [受注日]) / 7) * 7, '2024-01-01') as date) as [基準日]
  , sum([受注金額]) as [合計金額]
from [受注]
group by datediff(day, '2024-01-01', [受注日]) / 7

generate_series

連番生成

-- 1 ~ 10 までの連番
select [value] 
from generate_series(1, 10)
;
--
with cte ([value]) as (
  select 1 as [value]
  union all
  select [value] + 1 
  from [cte]
  where [value] + 1 <= 10
)
select [value] from [cte]
;

-- 1 ~ 50 までで 5ずつ連番
select [value] 
from generate_series(1, 50, 5)
;
with cte ([value]) as (
  select 1 as [value]
  union all
  select [value] + 5 
  from [cte]
  where [value] + 5 <= 50
)
select [value] from [cte]
;

first_value / last_value

欠損値の補完用に 構文 追加

create table [時系列データ] (
  [日時] datetime2
  , [値1] int
  , [値2] int
)
insert into [時系列データ] ([日時], [値1], [値2]) values
('2023-01-16 13:00:00', 10, 8)
, ('2023-01-16 13:01:00', 5, 9)
, ('2023-01-16 13:02:00', null, 17)
, ('2023-01-16 13:03:00', null, null)
, ('2023-01-16 13:04:00', 8, 25)
, ('2023-01-16 13:05:00', 17, null)
, ('2023-01-16 13:06:00', null, null)
, ('2023-01-16 13:07:00', 13, 21)
;
-- null だったら直前のデータで補完する
select
  [日時]
  , [値1] as [元_1]
  , last_value([値1]) ignore nulls over (order by [日時]) as [補完後_1]
  , [値2] as [元_1]
  , last_value([値2]) ignore nulls over (order by [日時]) as [補完後_2]
from [時系列データ]
order by [日時]
-- ignore nulls が無い場合… 簡易的に1個前、2個前 取る?
select
  [日時]
  , [値1] as [元_1]
  , last_value([値1]) over (order by [日時]) as [補完失敗_1] -- 意図した用途では使えない
  , lag([値1]) over (order by [日時]) as [1個前_1] -- 1個前が null だったらダメ
  , lag([値1], 2) over (order by [日時]) as [2個前_1] -- 2個前まで取る?2個前も null だったら…
  , [値2] as [元_1]
  , last_value([値2]) over (order by [日時]) as [補完失敗_2] -- 意図した用途では使えない
  , lag([値2]) over (order by [日時]) as [1個前_2]
  , lag([値2], 2) over (order by [日時]) as [2個前_2]
from [時系列データ]
order by [日時]
-- ちゃんとやろうとするとサブクエリでやる必要がある
select
  [時系列データ].[日時]
  , [時系列データ].[値1] as [元_1]
  , case 
      when [時系列データ].[値1] is not null then [時系列データ].[値1]
      else (
        select top(1) [nest_].[値1] 
        from [時系列データ] [nest_] 
        where [nest_].[値1] is not null 
          and [nest_].[日時] < [時系列データ].[日時]
        order by [nest_].[日時] desc
      )
    end as [補完後_1]
  , [時系列データ].[値2] as [元_2]
  , case 
      when [時系列データ].[値2] is not null then [時系列データ].[値2]
      else (
        select top(1) [nest_].[値2] 
        from [時系列データ] [nest_] 
        where [nest_].[値2] is not null 
          and [nest_].[日時] < [時系列データ].[日時]
        order by [nest_].[日時] desc
      )
    end as [補完後_2]
from [時系列データ] 
order by [日時]

isjson

しばらく json 絡み
json_type_constraint 追加
value / array / object / scalar

今まで(引数省略)は、object or array しか対応してなかったはず
どんな json でもいいなら、value 指定

declare @json_object nvarchar(max) = N'{"prop": "value"}'
declare @json_array nvarchar(max) = N'[{"prop": "value"}, 1, "aaa"]'
declare @json_scalar nvarchar(max) = '123'
declare @json_value nvarchar(max) = 'true'
select 
  N'object' as [type]
  , @json_object as [json]
  , isjson(@json_object) as [指定なし]
  , isjson(N'{"prop"= "value"}') as [invalid]
  , isjson(@json_object, value) as [value指定]
  , isjson(@json_object, array) as [array指定]
  , isjson(@json_object, object) as [object指定]
  , isjson(@json_object, scalar) as [scalar指定]
union select 
  N'array' as [type]
  , @json_array as [json]
  , isjson(@json_array) as [指定なし]
  , isjson(N'[{"prop": "value"}, 1 "aaa"]') as [invalid]
  , isjson(@json_array, value) as [value指定]
  , isjson(@json_array, array) as [array指定]
  , isjson(@json_array, object) as [object指定]
  , isjson(@json_array, scalar) as [scalar指定]
union select 
  N'scalar' as [type]
  , @json_scalar as [json]
  , isjson(@json_scalar) as [指定なし]
  , isjson(N'a12', value) as [invalid]
  , isjson(@json_scalar, value) as [value指定]
  , isjson(@json_scalar, array) as [array指定]
  , isjson(@json_scalar, object) as [object指定]
  , isjson(@json_scalar, scalar) as [scalar指定]
union select 
  N'value' as [type]
  , @json_value as [json]
  , isjson(@json_value) as [指定なし]
  , isjson(N'False', value) as [invalid] -- true / false / null は全部小文字
  , isjson(@json_value, value) as [value指定]
  , isjson(@json_value, array) as [array指定]
  , isjson(@json_value, object) as [object指定]
  , isjson(@json_value, scalar) as [scalar指定]
order by [type]

json_path_exists

declare @json_object nvarchar(max) = N'{"プロパティ": "値", "nest": [1, "add", {"prop1": 123, "prop2": "abc" }]}'
select 
  json_path_exists(@json_object, N'$."プロパティ"') as 直下 -- 日本語はエラーでるから " で囲って!
  , json_path_exists(@json_object, N'$.nest[2].prop1') as ネスト
  , json_path_exists(@json_object, N'$.hoge') as 存在しない
  , json_path_exists(N'jsonじゃない', N'$.hoge') as エラーにはならない

json_object

select json_object() -- 空の json object

select json_object('id': [Id], '': [値])
from [Null気にしない!]
select json_object('id': [Id], '': [値] null on null) -- 指定なしと同じ動き
from [Null気にしない!]
select json_object('id': [Id], '': [値] absent on null)
from [Null気にしない!]
select json_object('': [値], 'id': [Id] absent on null)
from [Null気にしない!]

json_array

select json_array() -- 空の json array

select json_array(1, 'aaa', 2, 3, 'ddd')
select json_array(1, json_object('prop': 'value', 'items': json_array(1, 2, 3)))

approx_percentile_cont/disc

percentile_cont/disc の高速適当版

select 
  [回数]
  , [教科]
  , [点数]
  , percentile_cont(0.5) within group (order by [点数]) over (partition by [回数], [教科]) as [中央値_間の平均]
  , percentile_disc(0.5) within group (order by [点数]) over (partition by [回数], [教科]) as [中央値_cume_dist以上で最小]
  , cume_dist() over (partition by [回数], [教科] order by [点数]) as cume_dist
from [成績]
order by 
  [回数]
  , [教科]
-- approx
select 
  [回数]
  , [教科]
  , approx_percentile_cont(0.5) within group (order by [点数]) as [中央値_間の平均]
  , approx_percentile_disc(0.5) within group (order by [点数]) as [中央値_cume_dist以上で最小]
from [成績]
group by
  [回数]
  , [教科]

greatest / least

やっと来た、最大 or 最小

select
  greatest(10, 45, null, 1, 3)
  , least(10, 45, null, 1, 3)

string_split

ordinal(序数) を付加

select *
from string_split('あいう,かきく,さしす,たちつ', ',') -- 前からあった

select * 
from string_split('あいう,かきく,さしす,たちつ', ',', 1) -- enable_ordinal 引数追加

datetrunc

やっと来た 日付系の切り捨て

declare @d datetime2 = '2023-09-18 17:45:21.1234567'
select 'Year', datetrunc(year, @d)
union select 'Quater', datetrunc(quarter, @d)
union select 'Month', datetrunc(month, @d)
union select 'Week', datetrunc(week, @d)
union select 'Iso_week', datetrunc(iso_week, @d)
union select 'Day', datetrunc(day, @d)
union select 'Hour', datetrunc(hour, @d)
union select 'Minute', datetrunc(minute, @d)
union select 'Second', datetrunc(second, @d)
union select 'Millisecond', datetrunc(millisecond, @d)
union select 'Microsecond', datetrunc(microsecond, @d)
order by 2

ltrim / rtrim

指定文字で削除出来るようになった

declare @s nvarchar(30) = N'   前は半角スペース  後ろは全角スペース   '
select 
  ltrim(@s) -- 前の半角スペース char(32) は消える
  , rtrim(@s) -- 後ろの全角スペースは消えない
select
  ltrim(@s, '  ') -- 追加出来るようになった引数に、半角スペース、全角スペース指定
  , rtrim(@s, '  ') -- 追加出来るようになった引数に、半角スペース、全角スペース指定

select 
  ltrim(N'123あいうえお321', '123') -- 空白以外もOK
  , rtrim(N'123あいうえお321', '123') -- 空白以外もOK

trim

削除する方向を指定出来るように

declare @s nvarchar(30) = N'123あいうえお321'
select trim('123' from @s) -- 両側から 123 を削除

select 
  trim(leading '123' from @s) -- 左だけ
  , trim(trailing '123' from @s) -- 右だけ
  , trim(both '123' from @s) -- 既定の動作

left_shift / right_shift

ここからビット操作関係

select 
  left_shift(10, 2) -- 10 = 001010 を 左に2個ずらすと 101000 = 32 + 8 = 40
  , right_shift(40, 2) -- 40 = 101000 を 右に2個ずらすと 001010 = 8 + 2 = 10
  , left_shift(0x0a, 1) -- 0x0a = 0000 1010 => 0001 0100 => 0x14
  , right_shift(0x25, 1) -- 0x25 = 0010 0101 => 0001 0010 => 0x12 (アンダーフロー)

bit_count

select 
  bit_count(11) -- 10 = 1011 3個ビットが立ってる
  , bit_count(0x25abc) -- 0x25abc = 0010 0101 1010 1011 1100 10個ビットが立ってる

get_bit

指定位置のビットを返す (右から0始まり)

-- 0x25abc = 0010 0101 1010 1011 1100
select 
  get_bit(0x25abc, 2) -- 右から 3個目は 1
  , get_bit(0x25abc, 10) -- 右から 11個目は 0
-- 10 = 1010
select
  get_bit(10, 0)
  , get_bit(10, 1)
  , get_bit(10, 2)
  , get_bit(10, 3)
  , get_bit(10, 4)
  , get_bit(10, 5)
  , get_bit(10, 31)
  -- , get_bit(10, 32) int 型なので、4バイト 32bit まで なので、 0 ~ 31 までしか指定出来ない

set_bit

指定位置のビットを設定 (右から0始まり、デフォルト値は 1)

select
  set_bit(0x0, 3) -- => 0x0 => 0000 0000 => 0000 1000 => 0x08
  , set_bit(10, 3, 0) -- => 10 => 1010 => 0010 => 2

01/20(土) meetup app osaka@8 で SQL Server 2022 の話しをちょっとします。 #meetupapp

久々投稿ですが、今週の土曜 01/20 に、
meetup app osaka@8 - connpass
で、SQL Server 2022 のお話しをちょっとします。
2022 が出て1年経ってるので、使ってる人も出てきてるのかなーとなので、
SQL 書く人のために、
SQL Server 2022 の新機能 - SQL Server | Microsoft Learn
ここの SQL のやつを実行しながら紹介していく感じです。
結構便利なの増えてますので、使える人は使ってみてください。

他にも色んなジャンルのお話しが聞けますので、気軽にご参加を~。

SQL Server Row Goal の話し

2023/03/03 にあるはずの meetup app osaka@7 のネタ。

色々賢くなってるようなので、ちょっと確認。

データは、Release AdventureWorks sample databases · microsoft/sql-server-samples · GitHub の AdventureWorksDW2019 使ってます。
SQL Server のバージョンは、 2022 使ってますが、もうちょい古くてもこの機能入ってます。

Row Goal は、クエリ内に特定のキーワード(top, offset fetch, exists, etc...) のような全部見なくても良さげ?みたいなのが入った時に、良しなにやってくれそうな感じやつです。

同じクエリで片方は Row Goal を無効にした場合の実行プランを見比べます。

select top(25) * from [FactProductInventory]
;
select top(25) * from [FactProductInventory]
option (use hint ('disable_optimizer_rowgoal') )
;

実行プランをみると

上のプランは、25行読む予定で25行読んだで。
下のプランは、776286行読む予定で25行で終わったで。
みたいな感じです。
何もしてないシンプルなクエリだとまあ全部読まなくて良かったねで済みそうですね。
ここのポイントは、何行読む予定(見積行)の数によって、実行プランが変わる場合があるよってのを次の例で試してみます。

試すクエリはこんなやつです。

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]

テーブルくっ付けて、並び替えて先頭 100 件取る感じ。
where 条件は無しで、join の結合箇所も [DimCustomer] が PK というくらいのインデックスしかないです。
そうすると、 [FactInternetSales] と [DimCustomer] を スキャン して結合、ソートして先頭100件というようなプランになります。
これは top があっても、ソートしないとダメでインデックスでソート済みでもないので全部見ないとダメーって感じになり、Row Goal が有効でも良しなになりません。

set statistics io on

select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
option (use hint ('disable_optimizer_rowgoal') )

;
select top(100) * 
from 
  [FactInternetSales] inner join [DimCustomer] on
    [FactInternetSales].[CustomerKey] = [DimCustomer].[CustomerKey]
order by 
  [DimCustomer].[EmailAddress]
  , [DimCustomer].[CustomerKey]
  , [FactInternetSales].[SalesOrderNumber]
  , [FactInternetSales].[SalesOrderLineNumber]
;

set statistics io off


プランも一緒なので、IO 量も変わらず。

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:23:48.6981772+09:00

ソートの先頭列は、[DimCustomer] テーブルを使ってるので、[DimCustomer] を読みながら [FactInternetSales] を繋いでみてくれたら Top が生きてきそうです。
ここで 1個インデックスを追加してみます。

create index [IX_FactInternetSales_CustomerKey] on [FactInternetSales] ([CustomerKey])

[FactInternetSales] で結合条件にしている [CustomerKey] にインデックスを作りました。
先ほどと同じクエリを実行すると、こんな感じのプランに変わります。

Row Goal 無効にしてるクエリは何も変わりませんが、無効にしてないクエリは、プランが変わりました。
[DimCustomer] を並び変えて順番に [FactInternetSales] を繋いでいって、100行になったら終わりみたいなプランですね。
IO 量をみると、

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 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。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:31:33.0897307+09:00

元のプランより、[FactInternetSales] の読み取り数が減っています。1249 => 401
これが Row Goal の効果で読む行減るんちゃうか?ってなってプランが変わって実際に読む量減ってやったーって感じです。

さらにインデックスを追加してみましょう。
今度は、[DimCustomer] の ソートに使用している列をインデックスにします。

create index [IX_DimCustomer_EmailAddress] on [DimCustomer] ([EmailAddress])

このインデックスを追加することで、さっきのプランで [DimCustomer] をスキャン(Clustered Index Scan)してソートしていたのが無くなり、このインデックスをスキャンだけに変わるはずです。
また同じクエリを実行してみましょう。

予想通りソート操作が無くなりました。*1
IO 量は

(100 rows affected)
テーブル 'Worktable'。スキャン数 0、論理読み取り数 0、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'FactInternetSales'。スキャン数 1、論理読み取り数 1249、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 984、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

(100 rows affected)
テーブル 'FactInternetSales'。スキャン数 32、論理読み取り数 401、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。
テーブル 'DimCustomer'。スキャン数 1、論理読み取り数 110、物理読み取り数 0、ページ サーバー読み取り数 0、先読みの読み取り数 0、ページ サーバー先読みの読み取り数 0、LOB 論理読み取り数 0、LOB 物理読み取り数 0、LOB ページ サーバー読み取り数 0、LOB 先読みの読み取り数 0、LOB ページ サーバー先読みの読み取り数 0。

(1 row affected)

Completion time: 2023-02-25T13:38:44.6290791+09:00

先ほどと比べると、[DimCustomer] の読み取り数が、984 => 110 と減りました。

気にせず恩恵受けてますが、賢くなっていってますねー。
ただ、何らかの原因で有効化した場合に遅いプランになる時があったら、その時は無効化してねとのことで。
SQL Server 2014、2016、2017に追加されたクエリ実行プランの KB4051361-オプティマイザー行の目標情報 - Microsoft サポート

*1:DimCustomer の Key Lookup は増えてますが

メモ:全DB、全ユーザーテーブルに対しての雑な sp_spaceused もどき

SQL Server で、クエリ1発だけで全てのデータベースの全てのユーザーテーブル に sp_spaceused の結果が欲しい + 取得日時とかDB名も欲しいなーなんて時のメモ。
雑なので、色々端折ってます。
ちゃんとしたの欲しい人は、クエリ1発諦めてカーソル + 動的SQL(sp_spaceused 呼んだ結果を一時テーブル入れてとかなんやら)とかでやるか、sp_spaceused のソース見てちゃんとやるように改造するかしたらいーんじゃないかな?

exec sp_MSforeachdb 'use [?];
select getdate() as [collect_date]
  , db_name() as [database_name]
  , object_name([object_id]) as [table_name]
  , sum([rows]) as [rows]
  , sum([reserved]) as [reserved_kb]
  , sum([pages]) as [data_kb]
  , sum([used_page]) - sum([pages]) as [index_size_kb]
  , sum([reserved]) - sum([used_page]) as [unused_kb]
from (
  select [object_id]
    , sum([reserved_page_count]) * 8 as [reserved]
    , sum([used_page_count]) * 8 as [used_page]
    , sum(case
          when ([index_id] < 2) then ([in_row_data_page_count] + [lob_used_page_count] + [row_overflow_used_page_count])
          else 0
        end
    ) * 8 as [pages]
    , sum(case
          when ([index_id] < 2) then [row_count]
          else 0
        end
    ) as [rows]
  from [sys].[dm_db_partition_stats]
  where [object_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'')
  group by [object_id]
  union
  select [it].[parent_id]
    , sum([p].[reserved_page_count])
    , sum([p].[used_page_count])
    , 0
    , 0
  from [sys].[dm_db_partition_stats] [p] inner join [sys].[internal_tables] [it]
    on [p].[object_id] = [it].[object_id]
  where [it].[parent_id] in (select [objects].[object_id] from [sys].[objects] where [type] = ''U'') 
    and [it].[internal_type] IN (202,204,207,211,212,213,214,215,216,221,222,236) 
  group by [it].[parent_id]
) [_target]
group by [object_id]
'

tempdb はじくとかてきとーにやってください。

Microsoft MVP じゃなくなりました

最近何もしてないのでまあそうだろうなぁと。

オンラインはあんまりやる気が出ないので、
オフラインでイベント開催しても良いかなーと思えるようになったら、
SQL World はぼちぼちやりたいなーとは思ってます。

メモ:ある列の値が上限を超えたら新しい行に分割するクエリ

SQL Server でのメモ

id qty
1 47
2 14
3 112

id qty
1 25
1 22
2 14
3 25
3 25
3 25
3 25
3 12

としたい。
サンプルのクエリはテーブル作るのメンドイので、テーブル値コンストラクターで。
テーブル値コンストラクター (Transact-SQL) - SQL Server | Microsoft Docs

with [cte] as (
  select 
    [id]
    , qty
    , case when [qty] > 25 then 25 else [qty] end as [splitted_qty]
    , 1 as [now]
    , [qty] / 25 + case when [qty] % 25 = 0 then 0 else 1 end as [split_count] 
  from (values (1, 47), (2, 14), (3, 112)) as b([id], [qty])
  union all
  select 
    [id]
    , qty
    , case when [now] + 1 < [split_count] then 25 else [qty] % 25 end as [splitted_qty]
    , [now] + 1 as [now]
    , [split_count]
  from cte
  where [now] + 1 <= [split_count]
)
select * from [cte] order by [id], [now]

再帰でやってるので上限超える場合は maxrecursion でよしなに。。
WITH common_table_expression (Transact-SQL) - SQL Server | Microsoft Docs

SQL Server 実行プランのスキーマを見比べてみる (2017 - 2019)

実行プランのスキーマを見る機会があったので、久々に blog を…。

ここから xsd をダウンロードできます。
Showplan Schema

単純に diff を取ると、2019 では、PDW や Spill、UDF、Page Server 等の情報が詳しく取れるようになってました。
サクッと検証出来る UDF で見てみましょう。

<xsd:complexType name="QueryExecTimeType">
    <xsd:annotation>
        <xsd:documentation>
            Shows time statistics for single query execution.
            CpuTime: CPU time in milliseconds
            ElapsedTime: elapsed time in milliseconds
            UdfCpuTime: Cpu time of UDF in milliseconds
            UdfElapsedTime: Elapsed time of UDF in milliseconds
        </xsd:documentation>
    </xsd:annotation>
    <xsd:attribute name="CpuTime" type="xsd:unsignedLong" use="required" />
    <xsd:attribute name="ElapsedTime" type="xsd:unsignedLong" use="required" />
    <xsd:attribute name="UdfCpuTime" type="xsd:unsignedLong" use="optional" />
    <xsd:attribute name="UdfElapsedTime" type="xsd:unsignedLong" use="optional" />
</xsd:complexType>

UDF (ユーザー定義関数) の CPU 時間と経過時間が追加されていました。
という訳で、次のような関数を作って、それぞれ実行プランを取ってみます。

create function dbo.SlowUdf()
returns int
as
begin
    declare @r int;
    with cte as (
      select 1 as seq 
      union all select seq + 1 from cte where seq < 100000
    )
    select top(1) @r = seq from cte 
    order by seq desc option (maxrecursion 0)
    return @r
end
go

create function dbo.FastUdf()
returns int
as
begin
    return 0
end
go
select dbo.SlowUdf()

の実行プランから該当部分を抜粋すると、

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="530" ElapsedTime="721" UdfCpuTime="530" UdfElapsedTime="721" />    
  ...<省略>
select dbo.FastUdf()

の実行プランから該当部分を抜粋すると、

<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="80" ContainsInlineScalarTsqlUdfs="true">
  ...<省略>
  <QueryTimeStats CpuTime="0" ElapsedTime="0" />      
  ...<省略>

SlowUdf は、UdfCpuTime、UdfElapsedTime が追加されています。
FastUdf は、UdfCpuTime、UdfElapsedTime が 0なのでしょう、省略されています。*1

同じ内容を 2017 で実行すると、 SlowUdf()

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="505" ElapsedTime="697" />
  ...<省略>

FastUdf()

<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="72">
  ...<省略>
  <QueryTimeStats CpuTime="0" ElapsedTime="0" />
  ...<省略>

UDF の情報はありませんね!

ちなみに 2016 => 2016sp1 では、クエリ中のWaitStats(待ち事象)が取れるようになってました。
知らないうちに色々増えているので確認しとかないとダメっすねー。

*1:use="optional"