第16回 関西DB勉強会 で 最適化されたロックの話しをしてきました #dbkan

スライドはこちら
speakerdeck.com

口頭での補足が多かったので、スライドはびみょいかも。

デモはこちら

/*==============================================================
  00_セットアップ.sql
  デモ用データベースの作成と前提条件の設定
  - ADR (Accelerated Database Recovery: 高速データベース復旧) … 必須
  - RCSI (Read Committed Snapshot Isolation) … LAQ に必須
  - OPTIMIZED_LOCKING はデモの中で ON/OFF を切り替えるので、
    ここでは OFF のまま
==============================================================*/

USE master;
GO

IF DB_ID(N'OptimizedLockingDemo') IS NOT NULL
BEGIN
    ALTER DATABASE OptimizedLockingDemo
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OptimizedLockingDemo;
END
GO

CREATE DATABASE OptimizedLockingDemo;
GO

-- ADR を有効化 (最適化されたロックの必須条件)
ALTER DATABASE OptimizedLockingDemo
    SET ACCELERATED_DATABASE_RECOVERY = ON;

-- RCSI を有効化 (LAQ の必須条件)
ALTER DATABASE OptimizedLockingDemo
    SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE;
GO

/*--------------------------------------------------------------
  設定の確認
  is_accelerated_database_recovery_on = 1
  is_read_committed_snapshot_on       = 1
  is_optimized_locking_on             = 0 (まだ OFF)
--------------------------------------------------------------*/
SELECT database_id,
       name,
       is_accelerated_database_recovery_on,
       is_read_committed_snapshot_on,
       is_optimized_locking_on
FROM sys.databases
WHERE name = N'OptimizedLockingDemo';
GO

/*--------------------------------------------------------------
  デモ用テーブルの作成
--------------------------------------------------------------*/
USE OptimizedLockingDemo;
GO

-- デモ①用: クラスター化インデックス付き、1,000 行
CREATE TABLE dbo.Demo1
(
    a int NOT NULL PRIMARY KEY,
    b int NULL
);

INSERT INTO dbo.Demo1 (a, b)
SELECT value, value * 10
FROM GENERATE_SERIES(1, 1000);
GO

-- デモ②③用: インデックスなし (ヒープ)
-- → UPDATE はテーブルスキャンになる
CREATE TABLE dbo.Demo2
(
    a int NOT NULL,
    b int NULL
);

INSERT INTO dbo.Demo2 (a, b)
VALUES (1, 10), (2, 20), (3, 30);
GO

/*==============================================================
  10_デモ1_ロック数の比較.sql  (1 セッションで完結)

  同じ「1,000 行 UPDATE」を最適化されたロック OFF / ON で実行し、
  sys.dm_tran_locks で保持ロックを比較する。

  期待結果:
    OFF … PAGE の IX ロック + 行ごとの KEY X ロックが大量 (1,000 個超)
    ON  … XACT リソースへの X ロックが 1 個だけ
==============================================================*/

USE OptimizedLockingDemo;
GO

/*--------------------------------------------------------------
  STEP 1: まず OFF の状態で実行
--------------------------------------------------------------*/
-- 現在の設定を確認 (0 = 無効)
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
       AS is_optimized_locking_enabled;
GO

BEGIN TRANSACTION;

    UPDATE dbo.Demo1
    SET b = b + 1;

    -- このトランザクションが保持しているロックを集計
    SELECT resource_type,
           request_mode,
           COUNT(*) AS lock_count
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@SPID
          AND resource_type IN ('PAGE', 'RID', 'KEY', 'XACT')
    GROUP BY resource_type, request_mode
    ORDER BY lock_count DESC;

    -- 明細も見たい場合はこちら
    -- SELECT resource_type, request_mode, resource_description
    -- FROM sys.dm_tran_locks
    -- WHERE request_session_id = @@SPID;

ROLLBACK TRANSACTION;
GO

/*--------------------------------------------------------------
  STEP 2: 最適化されたロックを ON にする
--------------------------------------------------------------*/
USE master;
GO
ALTER DATABASE OptimizedLockingDemo
    SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE;
GO
USE OptimizedLockingDemo;
GO

-- 1 = 有効 になったことを確認
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
       AS is_optimized_locking_enabled;
GO

/*--------------------------------------------------------------
  STEP 3: 同じ UPDATE をもう一度実行
  → XACT への X ロック 1 個だけになる
--------------------------------------------------------------*/
BEGIN TRANSACTION;

    UPDATE dbo.Demo1
    SET b = b + 1;

    SELECT resource_type,
           request_mode,
           COUNT(*) AS lock_count
    FROM sys.dm_tran_locks
    WHERE request_session_id = @@SPID
          AND resource_type IN ('PAGE', 'RID', 'KEY', 'XACT')
    GROUP BY resource_type, request_mode
    ORDER BY lock_count DESC;

ROLLBACK TRANSACTION;
GO

/*==============================================================
  20_デモ2_セッション1.sql  (SSMS タブ 1)

  デモ②: LAQ (Lock After Qualification) によるブロッキング解消
  「別々の行を更新しているだけなのにブロックされる」が
  ON で解消される。

  dbo.Demo2 はヒープ (インデックスなし) なので
  UPDATE はテーブルスキャン。
  OFF ではスキャン中に全行へ U ロックを取りにいくため、
  セッション 1 が X ロックを持つ行 (a=1) でセッション 2 が詰まる。

  ※ ヒープにしているのは確実に再現するため。a にインデックスがあれば
     この例は回避できるが、インデックスシークでもシーク範囲にロック中の
     行が含まれれば同じブロッキングは起きる (詳細はスライド7のノート)。

  ▼ 実行手順 (タブ 21_デモ2_セッション2.sql と交互に実行)
     [Phase A: OFF]
       1. このタブ:   STEP 1 (OFF に切り替え)
       2. このタブ:   STEP 2 (BEGIN TRAN + UPDATE a=1)
       3. 相手タブ:   STEP 1 (UPDATE a=2)
                      → ★ブロックされて返ってこない
       4. このタブ:   STEP 3 (COMMIT)     → 相手が解放される
     [Phase B: ON]
       5. このタブ:   STEP 4 (ON に切り替え)
       6. このタブ:   STEP 5 (BEGIN TRAN + UPDATE a=1)
       7. 相手タブ:   STEP 2 (UPDATE a=2) → ★即座に完了する!
       8. このタブ:   STEP 6 (COMMIT)
==============================================================*/

/*--------------------------------------------------------------
  STEP 1: 最適化されたロックを OFF にする (Phase A)
--------------------------------------------------------------*/
USE master;
GO
ALTER DATABASE OptimizedLockingDemo
    SET OPTIMIZED_LOCKING = OFF WITH ROLLBACK IMMEDIATE;
GO
USE OptimizedLockingDemo;
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
       AS is_optimized_locking_enabled;  -- 0
GO

/*--------------------------------------------------------------
  STEP 2: 行 a=1 を更新してトランザクションを開いたままにする
--------------------------------------------------------------*/
BEGIN TRANSACTION;

    UPDATE dbo.Demo2
    SET b = b + 10
    WHERE a = 1;

-- ここで COMMIT せずに、セッション 2 の STEP 1 を実行
-- → ブロックされる
GO

/*--------------------------------------------------------------
  STEP 3: コミットしてセッション 2 を解放する
--------------------------------------------------------------*/
COMMIT TRANSACTION;
GO

/*--------------------------------------------------------------
  STEP 4: 最適化されたロックを ON にする (Phase B)
--------------------------------------------------------------*/
USE master;
GO
ALTER DATABASE OptimizedLockingDemo
    SET OPTIMIZED_LOCKING = ON WITH ROLLBACK IMMEDIATE;
GO
USE OptimizedLockingDemo;
GO
SELECT DATABASEPROPERTYEX(DB_NAME(), 'IsOptimizedLockingOn')
       AS is_optimized_locking_enabled;  -- 1
GO

/*--------------------------------------------------------------
  STEP 5: もう一度、行 a=1 を更新してトランザクションを開いたままにする
--------------------------------------------------------------*/
BEGIN TRANSACTION;

    UPDATE dbo.Demo2
    SET b = b + 10
    WHERE a = 1;

-- ここでセッション 2 の STEP 2 を実行 → 今度はブロックされない!
-- (U ロックを取らず、最新コミット済みバージョンで
--  述語 a=2 を評価するため、a=1 の行はセッション 2 にとって
--  「条件を満たさない行」としてスキップされる)
GO

/*--------------------------------------------------------------
  STEP 6: 後片付け
--------------------------------------------------------------*/
COMMIT TRANSACTION;
GO

/*==============================================================
  21_デモ2_セッション2.sql  (SSMS タブ 2)

  デモ②: LAQ によるブロッキング解消 — セッション 2 側
  実行手順は 20_デモ2_セッション1.sql の冒頭コメントを参照。
==============================================================*/

USE OptimizedLockingDemo;
GO

/*--------------------------------------------------------------
  STEP 1: [Phase A: OFF] 行 a=2 を更新する
  → セッション 1 が a=1 に X ロックを持っているためブロックされる。
    (スキャンで a=1 の行に U ロックを取ろうとして待たされる)
  セッション 1 が COMMIT すると完了する。
--------------------------------------------------------------*/
BEGIN TRANSACTION;

    UPDATE dbo.Demo2
    SET b = b + 10
    WHERE a = 2;

COMMIT TRANSACTION;
GO

/*--------------------------------------------------------------
  STEP 2: [Phase B: ON] もう一度、行 a=2 を更新する
  → 今度はブロックされず即座に完了する!
--------------------------------------------------------------*/
BEGIN TRANSACTION;

    UPDATE dbo.Demo2
    SET b = b + 10
    WHERE a = 2;

COMMIT TRANSACTION;
GO

/*==============================================================
  30_デモ3_診断情報.sql  (時間があれば)

  デモ③: 最適化されたロック有効時の新しい診断情報を観察する
  - 待機の種類: LCK_M_S_XACT_MODIFY / LCK_M_S_XACT_READ
  - wait_resource / ロックリソースとしての XACT

  ▼ 実行手順 (3 タブ使用。前提: OPTIMIZED_LOCKING = ON)
     1. タブ 1 (20_デモ2_セッション1.sql): STEP 5 を実行
        → a=1 を更新してトランザクションを開いたままにする
     2. タブ 2: 下の [セッション 2] を実行
        → 今度は「同じ行 a=1」を更新するので、
          ON でも正しくブロックされる
        (LAQ でも、条件を満たす行に別のアクティブトランザクションの
         X TID ロックがあれば完了を待つ = ACID は守られる)
     3. タブ 3 (このタブ): [監視クエリ] を実行
        → wait_type = LCK_M_S_XACT_MODIFY、
          wait_resource に XACT が見える
     4. タブ 1: COMMIT → タブ 2 が解放される
==============================================================*/

/*--------------------------------------------------------------
  [セッション 2] タブ 2 で実行: セッション 1 と同じ行 a=1 を更新
--------------------------------------------------------------*/
USE OptimizedLockingDemo;
GO
BEGIN TRANSACTION;
    UPDATE dbo.Demo2
    SET b = b + 100
    WHERE a = 1;
COMMIT TRANSACTION;
GO

/*--------------------------------------------------------------
  [監視クエリ] タブ 3 (このタブ) で実行
--------------------------------------------------------------*/
USE OptimizedLockingDemo;
GO

-- ブロックされている要求と待機の種類
-- 期待値: wait_type = LCK_M_S_XACT_MODIFY
--         (変更目的で XACT の S ロックを待機)
SELECT r.session_id,
       r.blocking_session_id,
       r.status,
       r.command,
       r.wait_type,
       r.wait_time,
       r.wait_resource,
       t.text AS sql_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
      AND r.blocking_session_id <> 0;
GO

-- XACT ロックリソースの一覧
-- 保持側 (GRANT の X) と待機側 (WAIT の S) が
-- 同じ XACT リソースに見える
SELECT request_session_id,
       resource_type,
       resource_description,
       request_mode,
       request_status
FROM sys.dm_tran_locks
WHERE resource_type = 'XACT';
GO

/*==============================================================
  99_後片付け.sql
  デモ用データベースの削除
==============================================================*/

USE master;
GO

IF DB_ID(N'OptimizedLockingDemo') IS NOT NULL
BEGIN
    ALTER DATABASE OptimizedLockingDemo
        SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE OptimizedLockingDemo;
END
GO

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;

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

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

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

kansaidbstudy.connpass.com

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

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

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 で指定する必要があります。

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

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))
;

メモ：同じ値は同じグループにするけど、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:既にある場合は不要