型を合わせとくとデータが多くなっても安心

第13回　関西DB勉強会
https://kansaidbstudy.connpass.com/event/294889/

Transcript

1. 型を合わせとくとデータが多くなっ ても安心 第13回 関西DB勉強会 2023/10/21 @shinsukeoda
2. 今日のお話し SQL Server の話しです 2012 以前 (～ 2012) 2014 以降 (2014 ～) 今日詳しく話すのはこっち！ 型の不一致で起きる問題の1パターンを紹 介します 今日取り上げるのは文字列型について
3. 2種類の文字列型 char / varchar 日本語環境で特に意識してなかったら、 MS932 で文字データが格納される char および varchar (Transact-SQL) - SQL Server | Microsoft Learn nchar / nvarchar Unicode で文字データが格納される nchar および nvarchar (Transact-SQL) - SQL Server | Microsoft Learn
4. 今日のお題 create table [受注テーブル] ( [Id] bigint not null primary key , [区分] char(1) not null , [伝票番号] varchar(10) not null , constraint [UX_受注テーブル_区分_伝票番号] unique ([区分], [伝票番号]) )
5. 区分、伝票番号でデータを取得 ① where [区分] = 1 and [伝票番号] = 'JU00000001’ ② where [区分] = '1' and [伝票番号] = 'JU00000001’ ③ where [区分] = N'1' and [伝票番 号] = N'JU00000001' テーブル定義から抜粋 [区分] char(1) not null [伝票番号] varchar(10) not null
6. ① 区分に数値 where [区分] = 1 and [伝票番号] = 'JU00000001’ テーブル定義から抜粋 [区分] char(1) not null [伝票番号] varchar(10) not null 3つの中では、1番ダメなやつ 区分列が暗黙の型変換で数値に変換されて実行される。 区分列の 1文字が全て数値に変換出来るならエラーにならない。
7. 型変換の優先順位 データ型の優先順位 (Transact-SQL) - SQL Server | Microsoft Learn より
8. ② 全部型が一致 where [区分] = '1' and [伝票番号] = 'JU00000001’ テーブル定義から抜粋 [区分] char(1) not null [伝票番号] varchar(10) not null 正解！ 全部型が一致してるので問題なし。unique 制約のインデックス も使われる。
9. ③ 区分に nchar / nvarchar where [区分] = N'1' and [伝票番号] = N'JU00000001' テーブル定義から抜粋 [区分] char(1) not null [伝票番号] varchar(10) not null おしい！ 暗黙の型変換で、区分列 が nvarchar に変換されて実行さ れる。 文字列型同士の変換なのでエラーにはならない。
10. 暗黙の型変換って… -- 数値に変換 CONVERT_IMPLICIT(int, [dbkan].[dbo].[受注テーブ ル].[区分],0) = CONVERT_IMPLICIT(int, @1, 0) -- nchar に変換 CONVERT_IMPLICIT(nchar(1), [dbkan].[dbo].[受注 テーブル].[区分],0) = @1
11. 数値型への変換
12. nchar 型への変換
13. 暗黙の型変換でもインデックス有効？ SQL Server 2014 以降だと、char / varchar / nchar / nvarchar の convert_implicit ではインデックスが効 くようになっています！ 公式ドキュメントは未確認！ 2012 までは 数値と同じようにテーブルス キャン
14. 型変換無しの実行プラン
15. 型変換無しのリード/実行時間 (1 row affected) Table '受注テーブル_10000000'. Scan count 0, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 0 ms, elapsed time = 0 ms.
16. 型変換有りの実行プラン
18. 型変換有りのリード/実行時間 (1 row affected) Table '受注テーブル_10000000'. Scan count 1, logical reads 18057, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0. SQL Server Execution Times: CPU time = 1234 ms, elapsed time = 1243 ms.
19. 手抜き実装 + ライブラリの問題？ プログラムから パラメータ 渡す時に型指 定してる？プログラムの型からライブラ リが内部で推察して型指定されてない？ .NET の場合 String => nvarchar になる パラメーターおよびパラメーター データ型の構成 - ADO.NET | Microsoft Learn
20. まとめ 型揃えとかないとデータ増えた時やらか すかも join の条件の列は、双方同じ型になってい る？ プログラムから SQL 呼び出す時にパラメー タの型を手抜きせずにちゃんと渡してる？ SQL のリテラル書くときに、’aaa’, N’aaa’ の違い意識してる？
21. サンプルクエリとか 型を合わせとくとデータが多くなっても 安心 - お だ のスペース (hatenablog.com)