SQL Server の一時テーブルをNode.jsから使う時の注意点

SQL Server にある一時テーブルは、CSVファイルなど外部にある情報を一時的に取り込んでSQLとしてDB内にあるデータと合わせて処理したい時に便利です。
Azure SQL Databaseでも利用可能であり、Blobストレージにあるファイルの取り込み先としても利用できます。

Node.jsでAzure Functionsの関数を実装している時に、SQL Serverのクライアント`mssql`でハマったことについて書きます。

# TLTR :clock10: 

一時テーブルを作る時はCREATE文を`query`じゃなくて、`batch`を使う。
以上。

# サンプルコード

## 素直に書くと動かない

SQL文の実行は`query`メソッドを使う。
だから、一時テーブルの作成も`query`メソッドで実行する。

```javascript:sample.js
const sql = require('mssql');

(async () => {
    try {
        var connection = await sql.connect({
            user: '**',
            password: '**',
            server: '**',
            database: '**'
        })
        var request = connection.request();
        // Create a temporary table #tmp_users
        await request.query('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

        const names = ['Su', 'Yui', 'Moa']
        names.forEach(async (name) => {
            await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
        })

        const result = await request.query('SELECT * FROM #tmp_users')
        console.log(result)
    } catch(err) {
        console.error(err)
    }
})();
```

しかし、素直に[`query`](https://www.npmjs.com/package/mssql#query-command-callback)でSQL文を送ってみるも、期待どおりに動かない。どわーっとエラーが出て来るのだけど、肝心なところだけ見ると、どうやら作ったはずの一時テーブル`#tmp_users`が無いようだ。

```shell 
$ node sample.js
...
RequestError: Invalid object name '#tmp_users'.
...
```

## 動く

```javascript:sample.js
const sql = require('mssql');

(async () => {
    try {
        var connection = await sql.connect({
            user: '**',
            password: '**',
            server: '**',
            database: '**'
        })
        var request = connection.request();
        // Create a temporary table #tmp_users
        await request.batch('CREATE TABLE #tmp_users (name NVARCHAR(100) NOT NULL)')

        const names = ['Su', 'Yui', 'Moa']
        names.forEach(async (name) => {
            await request.query(`INSERT INTO #tmp_users VALUES ('${name}')`)
        })

        const result = await request.query('SELECT * FROM #tmp_users')
        console.log(result)
    } catch(err) {
        console.error(err)
    }
})();
```

`query`ではなく、`batch`という別のメソッドを使うと期待した通りに動く。

# 何が起きているのか

色々調べてみたので、まとめてみる。

## そもそも一時テーブルとは :clock1: 

* [Temporary Table - CREATE TABLE (Transact-SQL) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables)
 
一時的な用途で使うためのテーブルのことで、作成時のセッションの中でだけ存在できる。
セッションを切った時に、SQL Serverによって自動的にDROP TABLEが実行される。
ローカル一時テーブルは、接続しているセッションの中でだけ参照できる。一方で、グローバルは他のセッションからでも参照ができるが、ローカルと同様に、その一時テーブルを作成したセッションを切った時に削除される。

## で、Node.jsのクライアント`mssql`の`query`と`batch`って何だ :question: 

`mssql`の[READMEに`batch`の記述](https://www.npmjs.com/package/mssql#batch-batch-callback)があるので、そこを見ると。

> Execute the SQL command. Unlike query, it doesn't use sp_executesql, so is not likely that SQL Server will reuse the execution plan it generates for the SQL. 

とある。`query`は内部で`sp_executesql`という動的にSQLを実行するための、システムが予め持っているストアドプロシージャを使っているようだ。

## `sp_executesql` に渡されるSQLは別セッションで実行される。

* [A bit about sql server's local temp tables](http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx)

`sp_executesql`と一時テーブルに関するこの記事によると、

> Any dynamic SQL execution with either exec() or sp_executeSQL is ran in 
a child session/scope of the current one.

ということで、異なるセッションで実行されるそうだ。

## まとめ

* ローカル一時テーブルはセッション内でしか存在できず、かつそのセッション内でしか参照できない
* `query`で実行されるSQLはそこまでのセッションとは異なるセッションで実行される。
* すなわち、`query`でローカル一時テーブルを作成しても、即座に消えてしまう。

ということがわかりました。つまり、ローカル一時テーブルを作成する時は`batch`で実行しましょうということです :smile: 


# 参考

* [テーブル | Microsoft Docs | Microsoft Docs](https://docs.microsoft.com/ja-jp/sql/relational-databases/tables/tables)
* [mssql - npm](https://www.npmjs.com/package/mssql)
* [A bit about sql server's local temp tables](http://weblogs.sqlteam.com/mladenp/archive/2006/11/03/17197.aspx)
* [Scope of temporary tables in SQL Server - Stack Overflow](https://stackoverflow.com/questions/18614344/scope-of-temporary-tables-in-sql-server/18614467#18614467)
* [Temporary Table - CREATE TABLE (Transact-SQL) | Microsoft Docs](https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-transact-sql#temporary-tables)