More than 3 years have passed since last update.

[SQLServer] SQLServerに大量のダミーデータを作成する

SQLServer

Posted at 2021-04-07

前置き

Qiitaに長らく投稿していませんでしたが、SQLServerが必要になったので久しぶりにメモ書き。
今までDBは何にも触ってなかったので1からのお勉強中です。。。

本命としては、「大量のデータから目当てのデータを取得する際のパフォーマンス計測」がやりたいことですが、その為にも「大量のデータ」が必要。
そんな訳で大量のダミーデータの作り方をググって出てきたSQL文をペタッと貼ったら出来ました、、、が、何が書かれているのか理解できなかったので調べて、私なりの解釈を書いていきます。

参考にしたページ（全部同じこと書いてあります）

その他、文法について調べたもの

概要

さて、件のSQL文はこちら。（参考元から文字の大小や変数名だけ変えてますが、同じものです）
'TestTable'というテーブルに対して1000万件のダミーデータを作ります。
私の環境（SQLServer2019 EXPRESS）では大体30秒弱でした。

CreateDummy.sql


DECLARE @p_NumOfRows BIGINT
SELECT @p_NumOfRows=10000000; 

WITH Base AS ( 
  SELECT 1 AS n	
  UNION ALL	
  SELECT n+1 FROM Base WHERE n < CEILING(SQRT(@p_NumOfRows)) 
), 
Expand AS ( 
  SELECT 1 AS c FROM Base AS b1, Base AS b2 
), 
Nums AS ( 
  SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM Expand 
)

INSERT INTO TestTable 
SELECT 'DATA' + RIGHT('00000000' + CONVERT(NVARCHAR, n), 8) 
FROM Nums WHERE n <= @p_NumOfRows

OPTION (MaxRecursion 0);

作られたデータを見てみると素晴らしいことに確かに1000万件のデータがあるんですが、
SQL文はINSERT, UPDATE, DELETEくらいしか使ったことのない私にはこのSQL文の意味が分からない。
ということで、分解して細かくしてみました。

分解して解読

まず、最初の2行ですね。ここはプログラミングをやっていればお馴染みの変数宣言のようです。

DECLARE @p_NumOfRows BIGINT
SELECT @p_NumOfRows=10000000;

DECLAREが宣言、@p_NumOfRowsが変数名、BIGINTが型名ですね。
変数名の先頭に記号を持たせるのはperlにどことなく似ている（perlだと@は配列ですけどね）
ちなみにBIGINTはおおよそ-922京～922京だそうです。（SQLServer 数値型のデータ範囲（最小値～最大値））
次のSELECT文で10,000,000（ダミーデータの個数）を代入していて、これにより@p_NumOfRowsは10,000,000という数値として使用できます。

次に問題のWITH句。どこまで切り取ればいいか迷いましたが、、、

WITH Base AS ( 
  SELECT 1 AS n	
  UNION ALL	
  SELECT n+1 FROM Base WHERE n < CEILING(SQRT(@p_NumOfRows)) 
),

まず、大まかな流れとしてはWITH Base AS (〇〇)となっていますね。
丸括弧の中がサブクエリと呼ばれる「クエリの中で呼ばれるクエリ」のようですが、このサブクエリによって取得できたデータをBaseという名前で今後使いますよ。というのが、WITH句の効果らしい。

では、その丸括弧には何が書いてあるかというと、日本語で書き下すと多分こんな感じ

「1（nとして再利用）」という単一のデータを用意し、
「CEILING(SQRT(@p_NumOfRows))」を超えないnに対してn+1というデータを追加する。

1の次に2をBaseに入れたら、そこからまた2に+1した3をBaseに入れて...と、繰り返して数値データを加えていきます。

CEILINGは指定された数（float）以上の最小の整数を返す関数で、SQRTは指定された数の平方根を出す関数ですね。
つまり今回の場合、1000万の平方根以上の最小の整数は3163となり、
Baseには1~3163のデータがここで格納されるわけですね。

さて、WITH句の続き。

Expand AS ( 
  SELECT 1 AS c FROM Base AS b1, Base AS b2 
),

形としては先ほどのBaseと同じく、丸括弧内で取得したデータをExpandとして使うという意味。
丸括弧の中身はだいぶ簡素ですが、日本語で書き下すとこんな感じ

「1（cとして再利用）」というデータを、Baseのデータ数の二乗分複製する。

FROM句の後ろに、データソースを２つ、カンマ区切りで他に何の条件も付けずに書くと
「クロス結合」「単純結合」「直接結合」などと呼ばれる、結合されたデータを返すようです。
b1とかb2は、Baseという名前を2回使うことが出来ないから名前を分けているだけですね。
かつ、SELECT ~ FROMの中身はBaseのデータに依存せず、固定で「1」なので、作られるデータは全て「1」です。

で、クロス結合というのは各々の「全ての行」の「全ての組み合わせ」が結果になるので、
この場合、3163×3163で10,004,569個の「1」というデータが出来上がる。というわけです。

そしてWITH句の最後。

Nums AS ( 
  SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM Expand 
)

大まかな流れはBase, Expandと同様に、サブクエリで取得した内容をNumsとして使いますよということ。
サブクエリの中では、SELECT-OVER句というのが使われているようですが、詳細は長くなってしまうので先達者様にお任せします。（分析関数（ウインドウ関数）をわかりやすく説明してみた）
端的にこの文の意味だけを書き下すと、

Expand内データの「行番号」をデータとする。

ということだけですね。ROW_NUMBERは読んで字のごとくですが、行番号です。
その後のORDER BY cは、先ほどのExpand内で定義したc（但し、全て1）を並べるという意味ですが、全て1なので、特に意味はなさそうです。
公式ドキュメントによると、ROW_NUMBERには'ORDER BY'句が必須と書いてあるので、致し方なくでしょう。

ROW_NUMBER (Transact-SQL)

ORDER BY 句は、指定したパーティション内の行に一意の ROW_NUMBER を割り当てる順序を決定します。この引数は必須です。

なお、蛇足ですが、ORDER BYは昇順（ASC）か降順（DESC）か選べますが、省略すると昇順になります。

そしてようやく、メインディッシュというか、INSERT文ですね。

INSERT INTO TestTable 
SELECT 'DATA' + RIGHT('00000000' + CONVERT(NVARCHAR, n), 8) 
FROM Nums WHERE n <= @p_NumOfRows

INSERT INTO TestTableは恐らく説明は不要でしょうが、'TestTable'というテーブルにデータを挿入するという意味ですね。

次の'SELECT'文では、DATAという文字列の後ろに数字をくっつけています。
00000000（'0'が8文字）に先ほどのNums内で定義していた行番号nを文字列に変換し、合体。そして、右から8文字分トリミングしています。
例えば、62859という数値に対しては左から'0'を8文字足して'0000000062859'となり、その右から8文字分を取って'00062859'となって、
左に'DATA'をくっつけて'DATA00062859'が完成。

Numsのデータに対してこの操作を施しますが、このままだと10,004,569（10,000,000より多い）まで作ってしまうので、
WHERE n <= @p_NumOfRowsで最初に指定した@p_NumOfRowsを上限として作って出来上がりです。

さて、ここまででもう1000万のダミーデータが出来るわけですが、、、忘れてはいけないのが、最後に一文ありましたね。

OPTION (MaxRecursion 0);

この一文を書かずに実行するとすぐにエラーになります。
何故なら、SQLServerでは（多分どのDBでも）、クエリ内の永久ループ防止の為、「最大再帰回数」というのが指定されているからです。
SQLServerではデフォルトで100回に設定されているため、クエリ内で101回以上の再帰処理が入るとエラーで止まってしまうようです。

それを回避する為、オプションとして、MaxRecursion（最大再帰回数）を指定します。
MaxRecursionは0~32,767まで指定可能で、0は上限なしだそうです。
よって、'DATA***'を作る際に1000万回のループがありますが、上限を無視して繰り返しデータ作成ができるという訳でした。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up