2026-02-22

meetup app osaka@10 で SQL Server 2025 の話しをしてきました

meetupapp.connpass.com

デモ中心だったので、デモのクエリを

-- regexp_like
select * from 
(values (0, N'meetup app osaka@10')
  , (1, N'meetup'), (2, N'app')
  , (3, N'osaka'), (4, N'@10')
  ) as t (c1, c2)
where regexp_like(c2, '[a-zA-Z]+')
;

--regexp_replace
select N'meetup app osaka@10' as c1
, regexp_replace(
    N'meetup app osaka@10'
    , '([a-z]*)([a-z]*)([a-z]*)([@0-9]+)'
    , '\1\2\3万博') as c2

-- regexp_substr
select N'meetup app osaka@10' as c1
, regexp_substr(
    N'meetup app osaka@10'
    , '( )([a-z]+)(@)', 1, 1, 'i', 2) as c2

-- regexp_instr(開始位置/終了位置)
select N'meetup app osaka@10' as c1
, regexp_instr(
    N'meetup app osaka@10'
    , '( )([a-z]+)( )', 1, 1, 0, 'i', 2) as c2
, regexp_instr(
    N'meetup app osaka@10'
    , '( )([a-z]+)( )', 1, 1, 1, 'i', 2) as c3


--regexp_count(パターンに一致する件数)
select N'meetup app osaka@10' as c1
, regexp_count(
    N'meetup app osaka@10'
    , '[0-9a-z]') as c2
, len(replace(replace(N'meetup app osaka@10', ' ', ''), '@', '')) as c3


--regexp_matches (Group or Capture 情報みたいなの (表形式))
select *
from regexp_matches(
    N'meetup app osaka@10'
    , '.a')
select *
from regexp_matches(
    N'meetup app osaka@10'
    , '([a-z]+)(@)([0-9]+)')

-- regexp_split_to_table
select * 
from regexp_split_to_table(N'meetup app osaka@10', ' ')

-- 最適化されたロック
alter database [database_name] set accelerated_database_recovery = on
;
alter database [database_name] set read_committed_snapshot on
;
alter database [database_name] set optimized_locking = on
;

/* 
   最適化されたロック無しだと、session1 の commit を待って、session2 が動くので、
   session2 の更新がされて、(2, 200) => (100, 250) のデータが出来る
   最適化されたロックありだと、session2 が session1 の更新ロックを待たないので、
   session1 の commit 前に session2 の更新が走り、該当データなしで、(2, 200) => (2, 250) のデータが出来る。
   最適化されたロックの有無で動作が変わるので要注意！
*/ 
drop table if exists [テスト] 
;
create table [テスト] (
  [Id] int not null
  , [適当な列] int
)
;

insert into [テスト] ([Id], [適当な列]) values 
(1, 100), (2, 200), (3, 300)
;

-- session1
begin tran

update [テスト] set [適当な列] = 250
where [Id] = 2
;

-- commit

-- session2
begin tran

update [テスト] set [Id] = 100
where [適当な列] = 250
;

-- commit

-- ロックの確認
select
  resource_type
  , resource_associated_entity_id
  , db_name(resource_database_id)
  , object_name(
      case 
        when resource_type = 'OBJECT' then resource_associated_entity_id 
        else null 
      end)
  , request_mode --ロックの種類
  , request_type --要求の種類
  , request_status  --状態
from 
  sys.dm_tran_locks
where
  resource_type <> 'DATABASE'
order by
  resource_type
;
/*
   最適化されたロック無しだと、session1 の commit を待って、session2 が動く
   最適化されたロックありだと、session2 は session1 の commit を待たずに更新出来る。
*/
drop table if exists [テスト] 
;
create table [テスト] (
  [Id] int not null
  , [適当な列] int
)
;

insert into [テスト] ([Id], [適当な列]) values 
(1, 100), (2, 200), (3, 300)
;

-- session1
begin tran

update [テスト] set [適当な列] = 250
where [Id] = 2
;

-- commit

-- session2
begin tran

update [テスト] set [適当な列] = 150
where [Id] = 1
;

-- commit

-- ロックの確認
select
  resource_type
  , resource_associated_entity_id
  , db_name(resource_database_id)
  , object_name(
      case 
        when resource_type = 'OBJECT' then resource_associated_entity_id 
        else null 
      end)
  , request_mode --ロックの種類
  , request_type --要求の種類
  , request_status  --状態
from 
  sys.dm_tran_locks
where
  resource_type <> 'DATABASE'
order by
  resource_type
;

-- substring
select substring(N'あいうえお', 2, 10) 
select substring(N'あいうえお', 2) -- 長さ省略可能になった

-- dateadd bigint までサポート
select dateadd(nanosecond, 9223372036854775807, cast('0001/01/01' as datetime2))

-- unistr
SELECT UNISTR(N'\307F\30FC\3068\3042\3063\3077\3042\3063\3077');

-- product
select [年], product([率] / 100.0) over (order by [年]) from 
(values (2020, 110), (2021, 95), (2022, 150), (2023, 80), (2024, 90), (2025, 50), (2026, 170)
  ) as [前年比売上率] ([年], [率])
;

-- current_date
select current_date -- cast(getdate() as date) と等価 ANSI準拠

-- base64_encode
select base64_encode(cast('meetup app osaka@10' as varbinary));
select base64_encode(cast('meetup app osaka@10' as varbinary), 1);

-- base64_decode
select convert(varchar(max), base64_decode('bWVldHVwIGFwcCBvc2FrYUAxMA=='), 0);

-- || 文字列結合 -- ANSI準拠
set concat_null_yields_null off;
select 'aaa' || '' || 'bbb', 'aaa' + '' + 'bbb'
select 'aaa' || null || 'bbb', 'aaa' + null + 'bbb'
set concat_null_yields_null on;

-- json化 (今までの)
select * from 
(values (0, N'meetup app osaka@10')
  , (1, N'meetup'), (2, N'app')
  , (3, N'osaka'), (4, N'@10')
  ) as t (c1, c2)
  for json path, root('demo')
;

-- json_arrayagg
select c3, json_arrayagg(c2 order by c1) as c from 
(values (0, N'meetup app osaka@10', 'full')
  , (1, N'meetup', 'parts'), (2, N'app', 'parts')
  , (3, N'osaka', 'parts'), (4, N'@10', 'parts')
  ) as t (c1, c2, c3)
group by c3
;

-- json_objectagg
select c3, json_objectagg(c1:c2) as c from 
(values (0, N'meetup app osaka@10', 'full')
  , (1, N'meetup', 'parts'), (2, N'app', 'parts')
  , (3, N'osaka', 'parts'), (4, N'@10', 'parts')
  ) as t (c1, c2, c3)
group by c3
;

-- json index
/* こんな json が入ってるとして...
{
    "過去売上金額集計": {
        "2023": { "金額": 10000 } ,
        "2024": { "金額": 8000 }
    }
}
*/

-- 今まで
create table [商品] (
  [Id] bigint not null primary key
  , [名称] nvarchar(200)
  , [メモ] nvarchar(max)
)
;
-- たくさんデータ投入

alter table [商品] add [過去売上金額_2023] as json_value([メモ], '$."過去売上金額集計"."2023"."金額"')
;
create index [idx_商品_過去売上金額_2023] on [商品] ([過去売上金額_2023])
;
alter table [商品] add [過去売上金額_2024] as json_value([メモ], '$."過去売上金額集計"."2024"."金額"')
;
create index [idx_商品_過去売上金額_2024] on [商品] ([過去売上金額_2024])
;

-- json_value は returning type を指定しない場合、文字列で返すので文字列で比較する
-- returning type は 2027 から対応なので、2022 まではこうなる
select * from 商品 where 過去売上金額_2023 = '4000'
;

create table [商品2] (
  [Id] bigint not null primary key
  , [名称] nvarchar(200)
  , [メモ] nvarchar(max)
)
;
-- たくさんデータ投入
create json index [idx_商品2_メモ] 
  on [商品2] ([メモ]) for ('$."過去売上金額集計"."2023"."金額"', '$."過去売上金額集計"."2024"."金額"')
;

select * from [商品2] where json_contains([メモ], 4000, '$."過去売上金額集計"."2023"."金額"') = 1
;
-- json_value はなんでかインデックス効かなかった
select * from [商品2] where json_value([メモ], '$."過去売上金額集計"."2023"."金額"') = '4000';
select * from [商品2] where json_value([メモ], '$."過去売上金額集計"."2023"."金額"' returning int) = 4000;

2025-12-13

第15回関西DB勉強会で LT してきました

SQL Server 勉強会

kansaidbstudy.connpass.com

資料はこちら speakerdeck.com

デモ

--product
select product(c1) from 
(values (1), (2), (3), (4)) as t (c1)
;
select c1, product(c1) over (order by c1) from 
(values (1), (2), (3), (4)) as t (c1)
;

-- regexp_like
select * from 
(values (0, N'第15回 関西DB勉強会')
  , (1, N'第15回'), (2, N'関西')
  , (3, N'DB'), (4, N'勉強会')
  ) as t (c1, c2)
where regexp_like(c2, '[a-zA-Z]+')
;

--regexp_replace
select N'第15回 関西DB勉強会' as c1
, regexp_replace(
    N'第15回 関西DB勉強会'
    , '([^A-Z]*)([A-Z]+)([^A-Z]*)'
    , '\1万博\3') as c2

-- regexp_substr
select N'第15回 関西DB勉強会' as c1
, regexp_substr(
    N'第15回 関西DB勉強会'
    , '(第)([0-9]+)(回)', 1, 1, 'i', 2) as c2

-- regexp_instr(開始位置/終了位置)
select N'第15回 関西DB勉強会' as c1
, regexp_instr(
    N'第15回 関西DB勉強会'
    , '(第)([0-9]+)(回)', 1, 1, 0, 'i', 2) as c2
, regexp_instr(
    N'第15回 関西DB勉強会'
    , '(第)([0-9]+)(回)', 1, 1, 1, 'i', 2) as c2

--regexp_count
select N'第15回 関西DB勉強会' as c1
, regexp_count(
    N'第15回 関西DB勉強会'
    , '[0-9a-zA-Z]') as c2

--regexp_matches
select *
from regexp_matches(
    N'第15回 関西DB勉強会'
    , '[0-9a-zA-Z]')
select *
from regexp_matches(
    N'第15回 関西DB勉強会'
    , '([^A-Z]*)([A-Z]+)([^A-Z]*)')

-- regexp_split_to_table
select * 
from regexp_split_to_table(N'・作ったもの紹介 ・最近もやもやしてること ・凝り始めたテクノロジー ・このデータベースの好きなところ', '・')


-- json化 (今までの)
select * from 
(values (0, N'第15回 関西DB勉強会')
  , (1, N'第15回'), (2, N'関西')
  , (3, N'DB'), (4, N'勉強会')
  ) as t (c1, c2)
  for json path, root('demo')

-- json_arrayagg
select c3, json_arrayagg(c2 order by c1) as c from 
(values (0, N'第15回 関西DB勉強会', 'full')
  , (1, N'第15回', 'parts'), (2, N'関西', 'parts')
  , (3, N'DB', 'parts'), (4, N'勉強会', 'parts')
  ) as t (c1, c2, c3)
group by c3

-- json_objectagg
select c3, json_objectagg(c1:c2) as c from 
(values (0, N'第15回 関西DB勉強会', 'full')
  , (1, N'第15回', 'parts'), (2, N'関西', 'parts')
  , (3, N'DB', 'parts'), (4, N'勉強会', 'parts')
  ) as t (c1, c2, c3)
group by c3

2025-11-23

12/13(土) 第15回関西DB勉強会やります #dbkan

SQL Server 勉強会

kansaidbstudy.connpass.com

今回は、LT ばかりの勉強会になります。
私も SQL Server 2025 あたりの LT をする予定です。
開始時間は昼一ではなく、15:30 ～ですのでお間違えなく！

2025-07-23

Microsoft.Data.SqlClient だと DbType.Date で SQL Server の Date 型にマッピングしてくれた

SQL Server .NET

Dapper で SQL Server の date 型の値をパラメータとして渡したい - おだのスペース

大昔に書いた記事ですが、久々に検証することがあったので。

Microsoft.Data.SqlClient は最新の ver 6.0.2 で試してます。
※古いのでも大丈夫かは古いの使う人が試してください。。

public class DbDate : ICustomQueryParameter
{
    public DateTime? Value { get; set; }
    public void AddParameter(IDbCommand command, string name)
    {
        var param = command.CreateParameter();
        param.ParameterName = name;
        param.Value = Value.HasValue ? (object)Value.Value.Date : DBNull.Value;
        param.DbType = DbType.Date;
        command.Parameters.Add(param);
    }
}

で OK です。
非推奨ですが、System.Data.SqlClient だと最新の ver 4.9.0 でも、
DbType.Date で DateTime 型扱いになります。
System.Data.SqlClient 使ってる人は、昔のように SqlDbType で指定する必要があります。

2025-03-29

meetup app osaka@9 で SQL Server ベクトル検索の話しをしてきました #meetupapp

勉強会 SQL Server

meetup app@osaka9 で話して来ました。
meetup app osaka@9 - connpass

資料はこちら。
speakerdeck.com

ベクトル検索の話しなので、ベクタライズで AI 使って～みたいなのは無し！
デモで使ったクエリはこちら。

自前でベクタライズのは、百人一首の歌を自前で3次元ベクトル化してます。

1次元：季節 (春:1, 夏:2, 秋:-1, 冬:-2)
2次元：自然 (山とか海とか歌にある毎に 1ずつ加算)
3次元：動物 (動物が出たら 1ずつ加算)

検索ワードをベクトル化は、決まった文言が入ってたら、
この次元で～としています。
AI 使わなくてもベクトル検索出来るよーってことで。

vector_distance の例

declare @v1 vector(2) = '[1, 0]';
declare @v2 vector(2) = '[0, 1]';

select 
  vector_distance('cosine', @v1, @v2) as [コサイン距離]
  , vector_distance('euclidean', @v1, @v2) as [ユークリッド距離]
  , vector_distance('dot', @v1, @v2) as [負のドット積]
;

declare @v1 vector(2) = '[0, 0]';
declare @v2 vector(2) = '[1, 1]';
declare @v3 vector(2) = '[2, 2]';
declare @v4 vector(2) = '[3, 3]';

-- 同じベクトルのコサイン距離なので、全て同一の 0 になるはず
select 
    cast(vector_distance('cosine', @v1, @v1) as decimal(38, 30)) as [コサイン1] -- 0, 0 はなんかダメ
    , cast(vector_distance('cosine', @v2, @v2) as decimal(38, 30)) as [コサイン2] -- 誤差出る
    , cast(vector_distance('cosine', @v3, @v3) as decimal(38, 30)) as [コサイン3] -- 誤差出る
    , cast(vector_distance('cosine', @v4, @v4) as decimal(38, 30)) as [コサイン4]

どの距離使うのがいい？

declare @v1 vector(2) = '[-0.5, -0.5]'
declare @v2 vector(2) = '[3, 3]'
declare @v3 vector(2) = '[0.5, 0.5]'

select 
  vector_distance('cosine', @v1, @v3) as [-0.5_コサイン距離]
  , vector_distance('euclidean', @v1, @v3) as [-0.5_ユークリッド距離]
  , vector_distance('dot', @v1, @v3) as [-0.5_負のドット積]
  , vector_distance('cosine', @v2, @v3) as [3_コサイン距離]
  , vector_distance('euclidean', @v2, @v3) as [3_ユークリッド距離]
  , vector_distance('dot', @v2, @v3) as [3_負のドット積]

自前ベクトル化のサンプル DDL

create table [百人一首] (
  [No] int not null
  , [歌] nvarchar(200) not null
  , [ベクトル] vector(3) not null
);

insert into [百人一首] ([No], [歌], [ベクトル]) values 
(2, N'春過ぎて 夏来にけらし 白妙の 衣ほすてふ 天の香具山持統天皇（645年～702年）', '[2, 1, 0]')
, (3, N'あしびきの 山鳥の尾の しだり尾の ながながし夜を ひとりかも寝む柿本人麻呂（生没年不詳）', '[-1, 0, 0]')
, (4, N'田子の浦に うちいでてみれば 白妙の 富士の高嶺に 雪は降りつつ山部赤人（生没年不詳）', '[-2, 2, 0]')
, (5, N'奥山に 紅葉踏み分け 鳴く鹿の 声聞く時ぞ 秋はかなしき猿丸太夫（生没年不詳）', '[-1, 1, 1]')
, (6, N'かささぎの 渡せる橋に おく霜の 白きをみれば 夜ぞふけにける中納言家持（718年頃～785年）', '[-2, 0, 0]')
;

create table [季節] (
  [文言] nvarchar(10) not null
  , [ベクトル値] int
);
insert into [季節] ([文言], [ベクトル値]) values 
(N'春', 1), (N'夏', 2), (N'秋', -1), (N'冬', 2), (N'暑', 2), (N'寒', -2), (N'暖', 1), (N'冷', -2), (N'涼', -1);

create table [自然] (
  [文言] nvarchar(10) not null
  , [ベクトル値] int
);
insert into [自然] ([文言], [ベクトル値]) values 
(N'山', 1), (N'海', 1), (N'川', 1), (N'河', 1), (N'自然', 1);

create table [動物] (
  [文言] nvarchar(10) not null
  , [ベクトル値] int
);
insert into [動物] ([文言], [ベクトル値]) values 
(N'動物', 1)
;

create function [ベクトル化] (
  @v nvarchar(100)
) returns vector(3)
as 
begin
  declare @p1 int = isnull((select top(1) sum([ベクトル値]) from [季節] where @v like concat('%', [文言], '%')), 0);
  declare @p2 int = isnull((select top(1) sum([ベクトル値]) from [自然] where @v like concat('%', [文言], '%')), 0);
  declare @p3 int = isnull((select top(1) sum([ベクトル値]) from [動物] where @v like concat('%', [文言], '%')), 0);

  declare @ret vector(3) = concat('[', @p1, ',', @p2, ',', @p3,']')
  
  return @ret;
end

自前のベクトル化のサンプル

select * from [百人一首]
;

declare @searchWord nvarchar(100) = N'涼しい季節の歌'
;

select
  [No]
  , [歌]
  , vector_distance('cosine', [ベクトル], [dbo].[ベクトル化](@searchWord)) as [コサイン距離]
from [百人一首]
where 
  vector_distance('cosine', [ベクトル], [dbo].[ベクトル化](@searchWord)) < 0.1
order by 
  vector_distance('cosine', [ベクトル], [dbo].[ベクトル化](@searchWord))
;
select
  [No]
  , [歌]
  , vector_distance('euclidean', [ベクトル], [dbo].[ベクトル化](@searchWord)) as [ユークリッド距離]
from [百人一首]
where 
  vector_distance('euclidean', [ベクトル], [dbo].[ベクトル化](@searchWord)) < 1.5
order by
  vector_distance('euclidean', [ベクトル], [dbo].[ベクトル化](@searchWord))
;
select
  [No]
  , [歌]
  , vector_distance('dot', [ベクトル], [dbo].[ベクトル化](@searchWord)) as [負のドット積]
from [百人一首]
order by 
  vector_distance('dot', [ベクトル], [dbo].[ベクトル化](@searchWord))
;

2024-08-23

メモ：同じ値は同じグループにするけど、null は全部別扱いしたいクエリ (dense_rank)

タイトルの通りですが、

select * 
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

なデータがあった時に、Id が同じ物は同じグループにしたい場合に、
DENSE_RANK (Transact-SQL) - SQL Server | Microsoft Learn
ってのを使うんですが、null が複数あると null は同じグループになります。

select *
, dense_rank() over (order by [Id]) as [GroupId]
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

要件的に同じで良い場合はこれでいいんですが、null は別扱いしたい！！って場合のメモです。

元データに一意になる値があれば、null の時に別物扱い出来ます。
今回はシンプルな例なので、一意の列が無いため、
ROW_NUMBER (Transact-SQL) - SQL Server | Microsoft Learn
で突っ込みます。*1

select *
, row_number() over (order by [Id]) as [rownum]
from (values 
  (10)
  , (20)
  , (null)
  , (null)
  , (10)
  , (30)
  , (40)
) as [t] ([Id])
order by [Id]

で、dense_rank の order by をちょっと工夫するだけです。

select
  [Id]
  , dense_rank() over (
      order by case when [Id] is null 
        then concat('NULL', [rownum])
        else concat('', [Id])
      end) as [GroupId]
from (
  select *
  , row_number() over (order by [Id]) as [rownum]
  from (values 
    (10)
    , (20)
    , (null)
    , (null)
    , (10)
    , (30)
    , (40)
  ) as [t] ([Id])
) [tt]
order by [Id]

null の時は、一意の列、そうじゃない時は、Id 列使う。
ただ、一意の列の値と Id 列の値が被る場合があるので、被らないような一工夫する感じです。
ここでは、文字列化して、null の場合は接頭語 'NULL' つけてます。

*1:既にある場合は不要

2024-06-05

SQL Server 階層データ (hierarchyid) のインデックス (深さ優先) が使われるかの確認

SQL Server

親子関係を hierarchyid で表して、自分含めて自分より下位のデータを検索することがあったので、ちょっと確認。

階層データ (SQL Server) - SQL Server | Microsoft Learn

とりあえずクエリはこんなの

drop table if exists [階層型テスト]
create table [階層型テスト] (
  [PK] bigint not null primary key
  , [階層PK] hierarchyid not null index [IX_階層型テスト_階層PK]
  , [備考] nvarchar(max)
)
;

begin tran
;
with [cte] as (
  select 0 as [seq] 
  union all select [seq] + 1 
  from [cte] where [seq] < 99999
)
insert into [階層型テスト] ([PK], [階層PK], [備考])
select 
  [seq]
  , case when [seq] < 10 then concat('/', [seq] ,'/')
         when [seq] < 100 then concat('/', [seq] / 10,'/', [seq], '/')
         when [seq] < 1000 then concat('/', [seq] / 100,'/', [seq] / 10, '/', [seq], '/')
         when [seq] < 10000 then concat('/', [seq] / 1000,'/', [seq] / 100, '/', [seq] / 10, '/', [seq], '/')
         else concat('/', [seq] / 10000,'/', [seq] / 1000,'/', [seq] / 100, '/', [seq] / 10, '/', [seq], '/')
    end
  , cast([seq] as nvarchar(max))
from [cte]
option (maxrecursion 0)
;

commit

select *, [階層PK].ToString() from [階層型テスト]

100000件 (0 ～ 99999 まで) のデータで、適当に階層化したテストデータ用意。

これに対して、hierarchyid の IsDescendantOf (データベースエンジン) - SQL Server | Microsoft Learn を使って検索する。

select [PK], [階層PK], [階層PK].ToString(), [備考] 
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/100/') = 1
-- covered query
select [PK], [階層PK], [階層PK].ToString()
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/100/') = 1

の実行計画がこれ。

結果は、共に 111 件で、ちゃんと作ったインデックス (IX_階層型テスト_階層PK) のシークになってるからOK。
covered じゃないクエリは、Key Lookup 発生するから、クラスター化インデックスも見てるのはしゃーなし。

子、孫が多すぎる場合は、covered じゃないクエリはインデックスを使わずに、クラスター化インデックスのスキャン (全件見る) になった。
このクエリは、1111 件返す。

select [PK], [階層PK], [階層PK].ToString(), [備考] 
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/') = 1
-- covered query
select [PK], [階層PK], [階層PK].ToString()
from [階層型テスト] 
where [階層PK].IsDescendantOf('/1/10/') = 1