SE の雑記

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

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

leave a comment

SQL Server の照合順序はサーバーレベル (インストール時に指定するシステムデータベースの照合順序) 以外のレベルであれば、インストール後に変更することが可能です。
データベースの照合順序の設定または変更

サーバーレベルの照合順序に関してはシステムデータベースの再構築が必要となります。
サーバーの照合順序の設定または変更

照合順序を変えた場合、どのような動作が行われているのか見てみたいと思います。

■照合順序の変更時の動作


ユーザーデータベースを作成した場合、デフォルトではサーバーの照合順序がユーザーデータベースでも使用されます。
ユーザーデータベースの照合順序は作成後も変更することができます。
image

BOL に以下のように書かれているようにデータベースの照合順序を変更しても既存のテーブルの照合順序は変更されません。

ユーザー データベースに作成する新しいオブジェクトの照合順序は、ALTER DATABASE ステートメントの COLLATE 句を使用して変更できます。 このステートメントを実行しても、既存のユーザー定義テーブルの列の照合順序は変わりません。 ALTER TABLE の COLLATE 句で変更することができます。

既存のテーブルで照合順序を変更するためには ALTER TABLE で各列に対して照合順序を変更する必要があります。

照合順序ですが、

  • 文字列やソート時の比較基準
  • 非 Unicode 文字列の文字コード
    # Japanese の場合は CP932 が使用される

として使用されています。

SQL Server 2008 以降は Japanese_XJIS_CI_AS を使うことが多いでしょうか。

今回使用しているデータベースは、[Japanese_XJIS_CI_AS_SC] が使用されています。

  • 大文字小文字を区別しない、
  • アクセントを区別する
  • 補助文字を使用する

の設定が使用されています。

そのため、最初の列にプライマリキーを設定した状態で、以下のようなクエリを実行した場合はすべての文字 (a A a A) が同じ文字として認識されるため、最初の INSERT 分以外はエラーとなります。
image

データベースの照合順序を変更しても、既存のテーブルの照合順序は変更されないため、上記のデータを格納するためにはテーブルの列の照合順序から CI を使用しない設定にする必要があります。
ALTER TABLE (Transact-SQL)

具体的には以下のようなクエリを実行して、テーブルの照合順序を変更します。
# 列レベルの照合順序を変更する場合、データ型を変更しない場合もデータ型を指定する必要があります。

ALTER TABLE Table_1
ALTER COLUMN Col1 nvarchar(10)
COLLATE Japanese_XJIS_100_CS_AS_WS_SC

なお、プライマリーキーやインデックスのような列に依存するオブジェクトが設定されている場合、設定を解除しないと照合順序の変更はできません。
image

 

列に依存するオブジェクトが設定されていない状態で、照合順序を変更した際のログレコードの内容を確認してみます。
image

認識できるページ ID としては 96 / 67 / 68 が変更されていることが確認できます。
変更されたオブジェクトとしては、

  • sys.sysrscols
  • sys.sysschobjs
  • sys.syscolpars

が変更されていることが [AllocUnitName] から確認できます。

実際にどのような変更が行われているのか確認してみます。
上記のテーブルは専用管理者接続 (DAC) を使用すると確認することができます。
image

照合順序の変更はデータページではなくテーブルや列の属性情報が変更されているようですね。
結合列として使用している列の照合順序を変更した場合、照合順序の競合が発生する可能性があります。
image

その場合は、COLLATE 句を使用して照合順序を合わせる必要が出てきます。
image

照合順序を変更した場合、どのような動作が行われるのかが気になったので少し調べてみました。

Written by masayuki.ozawa

10月 21st, 2012 at 8:50 pm

Posted in SQL Server

Tagged with

Leave a Reply

*