【SQLServer】同じSQLでも環境で結果が異なる場合の対処法

2022/12/06

皆さんSQLServerでこんな経験ありませんか？

・ローカル環境テスト環境では想定通りのデータが抽出できるのに、本番環境では抽出できない

色々と原因が考えられますが、何を調べてもわからない場合は「照合順序」を疑ってみてください。

SQLServerにおける照合順序とは以下のようなものです。
・文字の大小比較の際の基準
・並べ替えや検索条件の照合に利用される設定

私はエンジニア歴13年ほどなのですが、過去1度だけこのような経験をしました。
その際は、データの差分を調査し、実際に流れているSQLを調査し、とかなり時間を要したのですがデータもSQLも全く同じで原因が全くわからず途方に暮れました。
最終的に先輩に相談したところ、照合順序という存在を教えてもらい解決することができました。

■照合順序とは

インデックスの作成時やORDER BYでのソート、GROUP BYでのグループ可、WHEREでの条件検索など様々な場面で使用されます。
例えば、アルファベットの「a」「A」、かなの「あ」「ア」「ｱ」を小さいほうから順に並べたらどう並ぶか、漢字の「河」「川」ではどちらが大きいのかなどの、文字の大小関係を決めているものになります。

そのため照合順序の設定が、本番環境とローカル環境で異なっていると冒頭のような事象が発生し、データもSQLも全く同じなのに、抽出結果が異なる。ということが起きてしまいます。
おそらくほとんどの人が意識したことは無いと思うのですが、照合順序が異なれば、クエリの結果が違ってきてしまうため実は結構重要な要素だったりします。

SQL Serverの照合順序には、SQL照合順序とWindows照合順序の2種類があるとのことですが、SQL照合順序はUnicode データ型をサポートしていなかった SQLServer 6.5 以前のバージョンとの互換性のみを目的としている照合順序であるため、基本的に照合順序と言えば、Windows照合順序のことと考えればよいようです。

■照合順序の確認方法

実際に照合順序ですが、以下の二通りの方法で確認できます。
※照合順序はインスタンス毎に設定されているので、インスタンス毎に確認してください。

　●以下のSQLを実行する
　SELECT SERVERPROPERTY(‘COLLATION’) AS サーバー照合順序

　●SQL Server Management Studioで確認する
　インスタンスを右クリック→プロパティでサーバーの照合順序を確認

■照合順序の指定方法

この照合順序の設定、変更もできるのですが本番環境の設定を変更してしまうと
他のSQLにも影響を与えてしまうので、かなりの注意と確認が必要です。

ですが、実は照合順序は以下のようにSQL実行時に指定することもできます。

WHERE [列名] COLLATE Japanese_CI_AS_KS_WS = ‘検索条件’

※「Japanese」の後ろの「_CI_AS_KS_WS」部分が実際の照合順序の設定になります。

■照合順序の設定値

「CI」だと「a」と「A」は同じになります。「CS」だと別になります。
「AI」だと「は”」、「ば」、「ぱ」は同じになります。「AS」だと別になります。
「KI」だと「か」と「カ」は同じになります。「KS」だと別になります。
「WI」だと「A」(半角)と「Ａ」(全角)は同じになります。「WS」だと別になります。

CI … Case Insensitive（大文字と小文字を区別しません。）
AI … Accent Insensitive（アクセントを区別しません。）
KI … Kana Insensitive（ひらがなとカタカナを区別しません。）
WI … Width Insensitive（半角と全角を区別しません。）

CS … Case Sensitive（大文字と小文字を区別します。）
AS … Accent Sensitive（アクセントを区別します。）
KS … Kana Sensitive（ひらがなとカタカナを区別します。）
WS … Width Sensitive（半角と全角を区別します。）

■最後に

私の約13年のエンジニア歴の中でいまだに1度しか発生したことはありませんが、
「データもSQLも全く同じなのに、環境によって結果が異なる」という現象が発生した際には照合順序のことを思い出し、設定値を確認して見てください。