メモ:not in と not exists は等価じゃない

どっかで書いた気もするけど、null が混じったときに結果が違うよと。

select * from (values (1), (2), (3)) as a(id)
where a.id not in (select * from (values (null), (2)) as b(id))

結果無し

select * from (values (1), (2), (3)) as a(id)
where not exists (select * from (values (null), (2)) as b(id) where b.id = a.id)

1、3 がヒット。

f:id:odashinsuke:20220107085503p:plain

メモ:互換性レベルは下位バージョンの動作検証には良くない

実働環境が古い SQL Server で手元には新しいのしかない時に、互換性レベル下げたらエラーになってくれるかなー?と思ってやってみたけどダメだった。
ALTER DATABASE 互換性レベル (Transact-SQL) - SQL Server | Microsoft Docs

ちゃんと古い環境用意しないとうっかり新しい機能、関数使っちゃうね…。

メモ:SELECT - INTO の指定先のテーブルの存在チェックはコンパイル時

1個のクエリ内で drop create で一時テーブル使い回ししたいなーと書いてたらエラーになったのでメモ。

こういうのがエラーになってクエリ自体実行されない。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable
drop table if exists #temptable

/* ↑で drop table してるのでクエリ実行時にエラー */
select * from #temptable
/*
error Msg 208, Level 16, State 0 
Invalid object name '#temptable'.
*/

/* ↑で drop table してても、こいつがあるとクエリの実行がされない!*/
select [Name] into #temptable from sys.tables
/*
error Msg 2714 Msg 2714, Level 16, State 1 
There is already an object named '#temptable' in the database.
*/

正解は2回目以降は truncate + insert select。

drop table if exists #temptable
select [Name] into #temptable from sys.tables
select * from #temptable

truncate table #temptable

select * from #temptable

insert into #temptable select [Name] into #temptable from sys.tables

メモ:SqlDataReader.GetColumnSchema は データ読まないので明らかに暗黙の型変換でエラーが出る場合でもセーフ

タイトル長い…
まあ当然といえば当然なんですが、明らかにダメなやつでも通るとちょっとびっくりする。。

例:文字型の列に 日付型のパラメータ渡す。

using Microsoft.Data.SqlClient;
using System;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection(@"接続文字列~"))
            {
                conn.Open();
                using (var cmd = conn.CreateCommand())
                {
                    cmd.CommandText = @"select * from sys.columns 
where name = @from";
                    cmd.Parameters.Add(new SqlParameter(
                        "@from", System.Data.SqlDbType.DateTime) { Value = DateTime.Now });
                    using (var reader = cmd.ExecuteReader())
                    {
                        // データ読むまではエラーが出ない。
                        foreach (var cs in reader.GetColumnSchema())
                        {
                            Console.WriteLine(cs.ColumnName);
                            Console.WriteLine(cs.ColumnOrdinal);
                            Console.WriteLine(cs.DataTypeName);
                        }
                        // この↓実行するとエラーがでる。
                        while (reader.Read())
                        {
                            Console.WriteLine(reader.GetValue(0));
                        }
                    }
                }
            }
            Console.ReadKey();
        }
    }
}

データ読むとこんなエラー。

メッセージ 241、レベル 16、状態 1、行 1
文字列から日付と時刻、またはそのいずれかへの変換中に、変換が失敗しました。

まあよく考えると、このエラーってこういう時に出るやつで。

select * from [商品] where [文字型の商品コード] = 100

これ商品コードが全部数値に変換出来るだったら、たまたまエラーにならない。
ってのと同じ。
データ型の変換 (データベース エンジン) - SQL Server | Microsoft Docs
暗黙の型変換で上手いこと言ったらセーフってやつ。
※convert_implicit はパフォーマンス上よろしくないので、上手くいっても実はセーフじゃない。。

多分他の DataReader も同じだろうけど、試したのが SQL Server だったので、タイトルは SqlDataReader にしとく。

メモ:T-SQL ある範囲内での 小さい順に空き番を見つける

決められた範囲があって、その中で空いている最小の番号を取得するってクエリ。
例:40000~4000000 までの間で未使用の番号を少ない順に10個とる。

範囲用の表を作って存在しないデータ取得

with [範囲] as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from [範囲] where [番号] < 4000000
), [使用済] as (
  select [番号] from 
    (values (40001), (40003), (40004), 
      (40005), (40008), (40009), (40010)) as [t]([番号])
)
select top(10) [範囲].[番号]
from [範囲]
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
)
option (maxrecursion 0)

結果はこんなん
f:id:odashinsuke:20210819191504p:plain
実行プランを見ると、Nested Loops の 後に Top なので、サクッと終わる。
f:id:odashinsuke:20210819191528p:plain
これ option (maxrecursion 0) してるけど、実際には、10件取れるまでしかループしてないから、無くてもエラーにならない。
再帰クエリで循環参照した時にどうなるのか?(SQL Server, PostgreSQL) - お だ のスペース
で少し書いたけど、再帰クエリと Top 良い感じ。

データ増えるとどうなるかというと…

drop table if exists [使用済]
create table [使用済] (
  [番号] bigint
);
with cte as (
  -- わざと 40000, 40001 は空けてる
  select cast(40002 as bigint) as [番号] 
  union all
  select [番号] + 1 from [cte] where [番号] < 3999900
)
insert into [使用済] select [番号] from cte
option (maxrecursion 0)
;
insert into [使用済] values (3999905), (3999906), (3999910)
;
with [範囲] as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from [範囲] where [番号] < 4000000
)
select top(10) [範囲].[番号]
from [範囲]
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
)
option (maxrecursion 0)

f:id:odashinsuke:20210819191949p:plain
めっちゃ遅いし、40000, 40001 が入ってない。。

実行プランを見ると、Nested Loop じゃなくなったので、当然結果も違う。
ってことは order by を付けないとダメなんで件数少なくても全件ループしてしまうのでこのやり方じゃダメ。*1
※order by 付けなくてもこのデータ量だと、再帰のループが多すぎて遅すぎる!
f:id:odashinsuke:20210819192011p:plain
ってことで、別のやり方を。

2021/08/20 追記 範囲用のテーブルが事前にあったら?

Twitter でレスあったので試しましたが、
事前に範囲用のテーブルがあった場合は、挙げたケースの中では一番早いかな。
↑のが遅いのは再帰の回数多くてデータ作るところが問題な訳で、元からデータがあったら問題無し。

drop table if exists [範囲]
;
create table [範囲] (
  [番号] bigint not null primary key
)
;
with cte as (
  select cast(40000 as bigint) as [番号]
  union all
  select [番号] + 1 from cte where [番号] < 4000000
)
insert into [範囲] select [番号] from cte
option (maxrecursion 0)
select top(10) [範囲].[番号]
from [範囲] 
where not exists (
  select * from [使用済]
  where [使用済].[番号] = [範囲].[番号]
) 
option (maxrecursion 0)

範囲が可変の場合に、どこまで事前に持たせるかってのは考え所ではあるかな~。

window 関数使って前後のデータと比較する

正直、クエリだけでやるのは大変なので、
クエリで空き番を取得するための材料を取る方向に逃げます。

LEAD (Transact-SQL) - SQL Server | Microsoft DocsLAG (Transact-SQL) - SQL Server | Microsoft Docs を使って

-- 取り合えず11件取っとけば、空き番10個あるなら事足りる。  
-- 11件なのは、先頭のデータが範囲の最小の時は、10件だと空き番10個取れない。。
select top(11) * from (
  select [番号]
    , lag([番号]) over (order by [番号]) as [前の番号]
    , lead([番号]) over (order by [番号]) as [次の番号]
  from [使用済]
) data_
where [番号] + 1 <> [次の番号] 
  or [次の番号] is null 
  or [前の番号] is null
order by [番号]

f:id:odashinsuke:20210819192530p:plain
さっきよりは大分マシだけど、試した環境で 6秒位掛かるのでやっぱ待てないかな?

空き番取得 + 先頭 + 最後

1クエリで取るの諦めて、空き番取るだけ + 先頭 + 最後だと

-- 空き番取る
select [空き開始番号], [空き終了番号] 
from (
  select [使用済].[番号] + 1 as [空き開始番号]
    , (
        select min([番号]) - 1 
        from [使用済] as [nest_] 
        where [nest_].[番号] > [使用済].[番号]
    ) as [空き終了番号]
  from [使用済] left join [使用済] as [cond_] 
    on [使用済].[番号] = [cond_].[番号] - 1
  where [cond_].[番号] is null
) as [data_]
where [空き終了番号] is not null
order by 1;
;
-- 先頭
select top(1) [番号] as [先頭] 
from [使用済] order by [番号]
;
-- 最後
select top(1) [番号] as [最後] 
from [使用済] order by [番号] desc
;

f:id:odashinsuke:20210819194105p:plain
クエリ3つ発行するけどこれが一番良さげ。

データ件数とパフォーマンスと実装のしやすさで好きなの選んだらいーんじゃないかな?
ちなみに再帰で範囲データを作ってないやつは、範囲外のデータがある可能性があるので、範囲の条件を付ける必要あるよ!

*1:最初のクエリはたまたま意図した結果が取れただけ!