最近、DBの照合順序を変更する必要があった。SQLServer2012。一部のテーブルのみとか、部分的に照合順序を変える事は正直あまり考えられないので、インスタンス丸ごと変える。
で、色々見てるとだいたい↓の感じで書いてある。
ユーザー データベースに作成する新しいオブジェクトの照合順序は、 ALTER DATABASE ステートメントの COLLATE 句を使用して変更できます。 このステートメントを実行しても、既存のユーザー定義テーブルの列の照合順序は変わりません。 ALTER TABLEの COLLATE 句で変更することができます。
master、model、msdb、および tempdb の各システム データベースを再構築する場合は、元の場所からデータベースを削除して再作成する必要があります。 再構築ステートメントに新しい照合順序を指定する場合は、その照合順序の設定でシステム データベースが作成されます。 これらのデータベースに対するユーザーの変更はすべて失われます。 たとえば、master データベースのユーザー定義オブジェクト、msdb にスケジュールされたジョブ、または model データベースの既定のデータベース設定に対する変更が対象になります。
とかとか。⇒TechNet
ユーザーDBは変えれなくはないけど、DBの照合順序変えても既存は変更されない。あと、システムデータベース（tempとか）はユーザDBとか存在してると変えられない。作り直しの必要あり。。。
それ何て再インストール？
正直、ユーザDBのインスタンス変えても、一時テーブルとか使うとtempテーブルにデータが出来るから、そっちと照合順序違くなって、Joinとか落ちるので、全DB変更する以外の事はあまし考えられない。。。
ググると色々出てくるんだけど、ぶっちゃけインスタンス再インストール（or別で作る）以外に無難な方法がありませんね。
あと、見てるとDBバックアップして変更後に復元するとか書いてあるサイトもあるけど、それやると、照合順序復元されてまるで意味なし。
ストアドとかの戻りとかはDBの照合順序変えれば適用されるらしいけど、テーブルの定義は変わらないようす。ついでにDBの照合順序は新しく作成されるテーブルに適用されるものみたいなので、既存は何も変わらない。
DBの照合順序変えてもsp_helpでテーブル見れば定義変わってないのが確認できる。
とゆーことで、以下の感じで全部作り直した。
- 既存をバックアップするか、リンクサーバ出来るようにしておく
- インスタンスを新しく作る（照合順序はインストール時に指定する）
- リンクサーバかバックアップを別名で復元
- 元のDBと同じものをCreateする
- 元のDBの全オブジェクトをスクリプト化しておいて、新しい方に全部作成
- 元DB復元したものかリンクサーバで元データを新しい方に入れ込む
ただ、↓のコマンドは通ったから、全列に対し実行するスクリプト作ればいけるかな？
alter table [テーブル名] alter column [列名] nvarchar(200) collate Japanese_XJIS_100_CI_AS_SC
見かけ上は上でも上手くいってるんだけど、本当にうまくいってるのか自信がありません。どうやって確認すればいいんだろうか。。。
ちなみに変更するのはサロゲートペア文字への対応用です。プログラム的な話はあれですが、SQLでいくと普通の照合順序使ってると、len()とかおかしくなる。サロゲの文字の場合はlen()が2でかえってくるので。なのでrightとかsubstringとか全部おかしくなる。ゆえに照合順序を変えて、サロゲでもlen()で1って帰ってきてくれるようにしないと色々あれなのです。
照合順序的には「japanese*****SC」みたいに「SC」つく照合順序が補助文字があーだこーだでサロゲ文字入ってきても上手くいく照合順序の様子。
あー、いちを個別のクエリ上でcollate句で照合順序指定すればテーブルとかDBの照合順序が違くても上手くいく。でも、んな事をやり続ける自信ありませんヨネ。。。
