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

概要:ローカル環境テスト環境では想定通りのデータが抽出できるのに、本番環境では抽出できない
目次:
■照合順序とは
■照合順序の確認方法
■照合順序の指定方法
■照合順序の設定値
■最後に
■執筆者プロフィール

こんにちは。エンジニアの河原です。

皆さん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」(半角)と「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も全く同じなのに、環境によって結果が異なる」という
現象が発生した際には照合順序のことを思い出し、設定値を確認して見てください。

■執筆者プロフィール

プロフィール:2022年中途入社/男性エンジニア
趣味:料理、読書、脱出ゲーム、ディズニー
経歴:
4年制大学の情報システム工学科を卒業後、
スマートシティ事業や金融ソリューション事業を提供する企業に新卒入社。
システムエンジニアとして市町村向け税金管理用WEBサイトの開発や
プロジェクトマネジメント、後輩育成などを中心に12年程従事。
2022年にWin-Winへのこだわりと自ら挑戦できる環境に魅力を感じw2に入社。
現在はEPPLを目指しながらEP案件の開発を行っている。

関連記事

プロジェクトストーリー

技術

コメント

この記事へのコメントはありません。

カテゴリー

TOP
TOP