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


## 前置き

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

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

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

* [SQL Serverへ大量のデータを高速で追加する](https://www.excellence-blog.com/2017/06/01/sql-server%E3%81%B8%E5%A4%A7%E9%87%8F%E3%81%AE%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E9%AB%98%E9%80%9F%E3%81%A7%E8%BF%BD%E5%8A%A0%E3%81%99%E3%82%8B/)
* [SQL Serverに1億件のテストデータを高速に作成する](https://dotnetmemo.hatenadiary.org/entry/20111015/1318663995)
* [SQLServerで大量データの作成方法について](https://lawless098.com/sqlserver_datacreate/)

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

* [SQLServer 数値型のデータ範囲（最小値～最大値）](https://johobase.com/sqlserver-numeric-data-range/)
* [WITH句を利用したサブクエリの使い回し](http://techblog-softem.com/developers/blog/2017/10/04/with%E5%8F%A5%E3%82%92%E5%88%A9%E7%94%A8%E3%81%97%E3%81%9F%E3%82%B5%E3%83%96%E3%82%AF%E3%82%A8%E3%83%AA%E3%81%AE%E4%BD%BF%E3%81%84%E5%9B%9E%E3%81%97/)
* [UNION と UNION ALL で結合](https://sql55.com/t-sql/t-sql-union.php)
* [CEILING (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/ceiling-transact-sql?view=sql-server-ver15)
* [sqlでfrom句に複数テーブルを指定し、結合条件を記載しない場合の取得結果について](https://teratail.com/questions/29727)
* [【SQL】結合入門（クロス結合、内部結合、外部結合）](https://qiita.com/aki3061/items/e9e7c37d59991c7aa3c2)
* [ROW_NUMBER (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15)
* [分析関数（ウインドウ関数）をわかりやすく説明してみた](https://qiita.com/tlokweng/items/fc13dc30cc1aa28231c5)
* [SQLServerの再帰SQLで最大再帰数の指定](https://lightgauge.net/database/sqlserver/1597/)

## 概要

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

```SQL: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行ですね。ここはプログラミングをやっていればお馴染みの`変数宣言`のようです。

```SQL
DECLARE @p_NumOfRows BIGINT
SELECT @p_NumOfRows=10000000; 
```

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

---

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

```SQL
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句の続き。

```SQL
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句の最後。

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

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

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

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

[ROW_NUMBER (Transact-SQL)](https://docs.microsoft.com/ja-jp/sql/t-sql/functions/row-number-transact-sql?view=sql-server-ver15)
> ORDER BY 句は、指定したパーティション内の行に一意の ROW_NUMBER を割り当てる順序を決定します。 この引数は必須です。 

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

---

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

```SQL
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万のダミーデータが出来るわけですが、、、忘れてはいけないのが、最後に一文ありましたね。

```SQL
OPTION (MaxRecursion 0);
```

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

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