【SQL Server】照合順序に気をつけろ！　デフォルトだと大文字、小文字を区別しないぞ！！

結論から先に書きます！

デフォルト設定だとアルファベットの大文字、小文字を区別しません。

(ﾉД`)ｼｸｼｸ

照合順序

長いこと、ITの業界にいるんだけど、照合順序ってまったく知りませんでした。

先日、テスト担当者から小文字で検索してもデータが表示できると報告を受けたんです。

テーブルには大文字で”ABC”と登録してあったんですが、小文字の”abc”でも検索できたとのこと。

そんなはずはない！！

正直、「ちゃんとテストやってんのか？」と疑いながら確認したら本当に小文字で検索できた？

ビックリしてネットで調べると、照合順序という聞いたことのない言葉が。

詳細はMSDNのページを参照して下さい。

照合順序 - 文字の比較と並び順 (その 1)

ザックリ説明すると、文字の順序を示すSQL照合順序と文字を区別するWindows照合順序の２つがあり、Windows照合順序の設定で大文字と小文字が区別されずデータが表示されました。

SQL照合順序

例えば、”あ”、“い”、“う”、“え”、“お”であればこの順序で並ぶのは誰でもわかりますがMSDNのページにも例である通り、”朝”と”海”はどちらが大きいのか？

これを決めているのが照合順序です。

もし、“朝”と”海”の順序がなければ、Selectの度に順番が変わってしまいます。

Windows照合順序

今回のメインはこちらの照合順序。

設定は下記ページに詳しく紹介されてます。

SQL Server の照合順序 – マイクロソフト系技術情報 Wiki

・CIだと”a”と”A”は同じです。

・AIだと”は”、”ぱ”、”ば”は同じです。

・KIだと”は”と”ハ”は同じです。

・WIだと”A”(半角)と”Ａ”(全角)は同じです。

SQL Serverのデフォルト設定

デフォルト設定は、”Japanese_CI_AS“です。

つまり、デフォルト設定だとアルファベットの大文字と小文字は区別しない。

濁点、半濁点は区別するです。

※”a”と”A”は同じと判断され、”は”、”ぱ”、”ば”は別だと判断されます。

“ABC”とDBには登録されてるのに、”abc”, “Abc”とかで検索してもヒットします。

どうすればいい？

データベースの設定をSqlserver ManagementStudio変更することができます。

照合順序の変更時の内部動作について at SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

ただ、ここで注意があります。

データベースの設定を変更しても、それまでに作成しているテーブルは元の設定のままです。

※設定変更後に作成されたテーブルは新しい設定でテーブルが作成されます。

すでに作成したテーブルの設定を変更するにはALTER TABLE文で全カラムを変更すればOKです。

変更のポイントとして

①文字列(VARCHAR、CHAR)のみの変更でOK（数値、日付項目は変更の必要なし）

②その項目がプライマリキーの場合は、最初にプライマリキーを削除してから設定変更し、変更後にプライマリキーを再設定。

とはいえ、全テーブルの文字列項目を調べて、プライマリキーを削除して、設定変更して、プライマリキーを再設定するのって大変ですよね。

サンプルとして私が作成したSQLを以下に記します。

この３つのSQLをそれぞれ流すと、「プライマリキー削除」、「設定変更」、「プライマリキー再設定」のSQL文が表示されます。

表示されたSQLを順に実行すれば設定変更できます。

※’TEST_%’は適宜修正して下さい。

この例は、テーブル名が”TEST_”で始まるテーブルが対象となっています。

gist1b7f1de46df8376bd51a96c9171d696e

gist076bd694d60a93df76e6de7f1ceffed2

gist87bd938f3d771714ce2f42134e1de520

最後に

いかがですか？

もし、デフォルト設定でSQL Serverをすでに作成してしまい、困っている人はぜひこのSQLで再設定して下さい。

ちなみに、変更する時は、事前にローカル環境などでテストしてから検証環境や、本番環境に適用して下さい。

ただ、最初に照合順序を設定すれば、こんな面倒なことをしなくてもいいんですけどね。

【関連記事：他にも気をつけたい事】

【解決方法】SQL Serverデフォルト設定(READ COMMITTED SNAPSHOT OFF)だと同時接続でロック待ちが発生します – フリーランスチャレンジ！！

【DB】Group ByしてCOUNTすると０件が取得できない – フリーランスチャレンジ！！

【広告】

SQLアンチパターン

posted with ヨメレバ

ビル・カーウィン/和田卓人オライリー・ジャパン 2013年01月

楽天ブックス

Amazon

Kindle

SQL Server　Transact-SQLプログラミング　実践開発ガイド

posted with ヨメレバ

五十嵐貴之技術評論社 2019年10月09日頃