桁数拡張作業で照合順序が合わないDB間でSQLエラー発生(SQL Server)

Database
照合順序

SQLserverについて、昨日つまづいたこと備忘録です。

SQLServerで桁数拡張するもSQLエラーが発生

SQLServer2012で構築しているA,B2つのデータベースがあり、特定のカラムの桁数を拡張する対応を行っていました。

拡張の際に外部キー制約が邪魔になるので、一旦、インデックスとプライマリキー制約を削除してから作業します。

<手順>
① インデックスとプライマリキーを削除
② alter tableコマンドでカラムの桁数を拡張
③ プライマリキーとインデックスを再作成

これらの拡張作業を終えて、システムにアクセスすると・・・

SQLエラー発生!!

なんでだ!?
どうも日付などの一部のデータの取得に失敗している模様。

データベース間の照合順序が異なっていたことが原因

調べていくと、どうやらA,B2つのデータベース間の照合順序の違いが影響しているということが分かってきました。

照合順序とは何か?
MSさんによると照合順序とは以下の説明がありました。

SQL Server では、文字の大小関係を比較する場合の基準を照合順序 (collation) と呼んでいます。
例えば、「朝」と「海」ではどちらが大きいのか、「あ」「ア」「ア」を大きい順に並べた場合どのように並ぶのかといった、文字の大小関係を決めているのが照合順序です。
https://blogs.msdn.microsoft.com/jpsql/2016/07/26/1-3/

2つのデータベースの照合順序は以下でした。
・データベースAの照合順序は、Japanese_CI_AS
・データベースBの照合順序は、Japanese_BIN

この業務システムでは、データベースA,B間のテーブルを結合している処理があるのですが、
異なる照合順序のテーブルを結合すると、SQLエラーが発生するようです。

この照合順序ですが、基本的にはデータベース単位で設定されるものなのですが、
実は、テーブル単位、または、セル単位でも設定できるようです。

今回ハマったのはまさにこれでした。
セル単位で照合順序を指定してました。

今回のカラムの桁数拡張作業において、桁数拡張したタイミングで、このセル単位の照合順序がリセットされてしまっていたようです。

よって②の alter tableコマンドの中で、カラムにCOLLATE句をつけて実行することにしました。

<手順>
① インデックスとプライマリキーを削除
② alter tableコマンドでカラムの桁数を拡張して、COLLATE句で照合順序を変更
alter table [TABLE] alter column [COLUMN(n)] COLLATE Japanese_CI_AS;
③ プライマリキーとインデックスを再作成

照合順序について注意すべき点

今回のようなカラムの桁数拡張などの対応を行う場合、まずはじめにデータベースの定義情報を確認すると思います。
その時、SQL Server Management StudioなどでCREATE TABLE文を発行してみて、どのような構文で作成されたのかをチェックする方が多いのではないでしょうか。

この時に発行されたCREATE文には、照合順序の指定が含まれません。

もしかしたら照合順序を含めて出力する方法もあるのかもしれませんが、普通に操作しているとこうなります。

列単位に明示的に指定されている照合順序を一括で抜き出すクエリがあれば、誰か教えてください。

というわけで、桁数拡張などテーブルの定義変更を行う場合には、データベース単位で照合順序が一致しているかチェックしましょう!

Appendix

ウチのデータベースの照合順序は、Japanese_CI_AS、Japanese_BINの2種類でしたが
照合順序ってどのくらい種類があるんだろう?と思い調べてみました。

なんとSQLServer2012の場合、
日本語だけで138種類、全言語だと3885種類もの照合順序が用意されているようです。

へぇー!

 

コメント