ストアドでIDの空き番を探す

クエリの動作環境

• OS：20.04.4 LTS (Focal Fossa)
• SQLServer：Microsoft SQL Server 2022 (RC0) - 16.0.900.3 (X64) Aug 10 2022 03:18:41 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.4 LTS)
  ※Dockerで動かしています

ストアド作成用のクエリ

CREATE PROCEDURE GetIDGaps
AS
BEGIN
    -- Declare table variables to hold gaps and max value
    DECLARE @Gaps TABLE (StartID INT, EndID INT);

    -- Find gaps in the ID sequence
    ;WITH CTE AS (
        SELECT
            BusinessEntityID,
            ROW_NUMBER() 
            OVER (ORDER BY BusinessEntityID) 
            AS RowNum
        FROM Person.Person
    )
    INSERT INTO @Gaps (StartID, EndID)
    SELECT
        t1.BusinessEntityID + 1 AS StartID,
        t2.BusinessEntityID - 1 AS EndID
    FROM
        CTE t1
        LEFT JOIN CTE t2 ON t1.RowNum = t2.RowNum - 1
    WHERE
        t2.BusinessEntityID - t1.BusinessEntityID > 1;

    -- Select the gaps
    SELECT StartID, EndID FROM @Gaps;

END;

使用しているデータ

AdventureworksというMSのサンプルデータを利用しています。
正確には、Adventureworksのデータを利用しているDockerイメージを使わせてもらっています。
こちらのブログで紹介されているDockerイメージをビルドしました。

www.nuits.jp

ストアドを作成する前に、ストアドが実行されたときの結果を先に確認

CREATE PROCEDUREしないように、実際に動作する部分のクエリを選択してSSMSで実行します。
左は実際の空き番の開始IDと終了ID、右は特に条件を指定しないテーブルの取得結果です。

BusinessEntityIDが歯抜けになっている部分の値と、ストアドの結果が一致しています。
あとはCREATE PROCEDUREから実行すればストアドが作成できますね。

感想？的な

ストアドを書くことが最近はめっぽう減っていて、ちょっと書き方というか雰囲気を思い出したいなと思い、書いてみました。
ストアドどうこうよりも、Adventureworksが入ったSQLServerをDockerでインストールしてすぐに使えるのが感動してしまった...ｗ