SQL Serverにおける照合順序の基本と設定単位を理解しよう
照合順序（Collation）とは、文字列の比較・並び順を決定する設定のことです。
SQL Serverでは、大文字小文字の区別（CS/CI）やアクセントの扱い（AS/AI）、ひらがなとカタカナの区別（KS）などを制御できます。
環境ごとに照合順序が異なると、同じSQLでも結果が違ったり、エラーが発生することがあります。この記事では、照合順序の基本と設定単位を体系的に整理します。
目次
照合順序を調べる方法
まずは、現在のサーバーやデータベースがどの照合順序を使用しているかを確認してみましょう。
以下のクエリを実行すると、各レベル（サーバー／DB／列）の設定を確認できます。
-- サーバーレベルの照合順序を確認
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
-- データベースレベルの照合順序を確認
SELECT name, collation_name
FROM sys.databases
WHERE name = DB_NAME();
-- 列レベルの照合順序を確認
SELECT name, collation_name
FROM sys.columns
WHERE object_id = OBJECT_ID('dbo.TableName');
これらを把握しておくと、環境間の照合不一致によるエラーを早期に発見できます。
照合順序とは
照合順序（Collation）は、文字列データの比較や並び替えのルールを決める設定です。
英字では大文字小文字（CS/CI）、日本語では全角／半角やひらがな／カタカナ（KS）の扱いに影響します。
環境が異なると同じ文字列比較でも結果が変わるため、システム全体で統一しておくことが重要です。
代表的な照合順序の種類とフラグの意味
SQL Serverの照合順序は大きく「系統（ベース）」と、動作を細かく指定する「フラグ」の組み合わせで表現されます。
ここでは、実務で遭遇しやすい系統の代表例と、フラグの意味をまとめます。
A. 系統別の代表例と特徴
| 系統（ベース） | 代表例 | 主な特徴／注意点 | 主な用途 |
|---|---|---|---|
| Windows照合順序 |
Japanese_CI_ASLatin1_General_100_CI_AS
|
Windowsのロケール規則に準拠する標準系。 バージョン番号（例： _100）付きは並び替えの精度が向上。 |
新規システムの既定。 多言語・最新環境での推奨。 |
| SQL照合順序 | SQL_Latin1_General_CP1_CI_AS |
旧来の互換系。Windows系と比較して並び順の規則が異なる。 新規採用は非推奨（互換性理由で残存）。 |
レガシー資産の継承時のみ。 |
| UTF-8 対応 | Japanese_CI_AS_UTF8 |
SQL Server 2019以降の VARCHAR 系でUTF-8エンコードを使用可能。文字集合の拡張とストレージ効率の両立に有効。 |
大規模テキストや多言語混在の最適化。 |
| サロゲートペア対応 | …_SC（例：Japanese_CI_AS_SC） |
サロゲートペア（拡張絵文字など）の補助文字を正しく扱う。 文字長計算・並び順が実データ意図に近づく。 |
絵文字・特殊文字を扱うUI/UXやSNS連携。 |
| DB 既定を参照 | DATABASE_DEFAULT |
クエリ内で COLLATE DATABASE_DEFAULT を指定すると、実行時のデータベース既定照合に合わせて比較できる。 |
クロスDBや一時テーブル混在時の衝突回避。 |
B. フラグ（感度オプション）早見表
| フラグ | 意味 | 例 | 備考 |
|---|---|---|---|
_CI / _CS |
Case Insensitive / Case Sensitive（大文字小文字の区別） | Japanese_CI_AS / Japanese_CS_AS |
英字の大小を同一視するかを制御。 |
_AI / _AS |
Accent Insensitive / Accent Sensitive（アクセントの区別） | Latin1_General_100_CI_AI / …_AS |
ダイアクリティカルマークの有無を区別するか。 |
_KS |
Kana Sensitive（かな種の区別：ひらがな/カタカナ） | Japanese_CS_AS_KS |
日本語特有の区別。必要時のみ有効化。 |
_WS |
Width Sensitive（全角/半角の区別） | …_CI_AS_WS |
同じ文字でも幅違いを別扱いにする。 |
_SC |
Supplementary Characters（補助文字対応） | …_CI_AS_SC |
絵文字などのサロゲートペアを正しく扱う。 |
_UTF8 |
UTF-8 エンコード（主に VARCHAR 系） |
…_CI_AS_UTF8 |
SQL Server 2019+。多言語＆サイズ効率に有利。 |
DATABASE_DEFAULT |
DB既定照合へ合わせる特殊指定 | COLLATE DATABASE_DEFAULT |
クエリ内での一時回避・統一に便利。 |
命名は一般に「ベース_大小感度_アクセント感度_（任意フラグ）」の順で構成されます。
例：Japanese_CI_AS（日本語/大小無視/アクセント区別）、Japanese_CI_AS_SC_UTF8（日本語/大小無視/アクセント区別/補助文字/UTF-8）。
-- 代表的な使い分け例
-- 大小無視・アクセント区別で比較したい
SELECT *
FROM dbo.Customers
WHERE FullName COLLATE Latin1_General_100_CI_AS = 'Jose';
-- DB既定に合わせてJOINを通す（衝突回避）
SELECT *
FROM DB1.dbo.Users AS u
JOIN DB2.dbo.Members AS m
ON u.Name COLLATE DATABASE_DEFAULT = m.Name COLLATE DATABASE_DEFAULT;
照合順序を設定できる単位
SQL Serverでは、照合順序を設定できる単位が4つあります。
それぞれの設定レベルごとに、メリットと注意点を理解しておきましょう。
① サーバー（インスタンス）単位
- 概要：インストール時に指定。master・model・tempdb などシステムDBの既定値として機能します。
- メリット：一時DB（tempdb）との照合不一致が起きにくく、新規DBの初期値も統一できます。
- デメリット：後から変更するのはほぼ不可能（インスタンス再構築レベル）。
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
② データベース単位
- 概要：CREATE/ALTER DATABASE で指定。新規作成される列やメタデータの既定値になります。
- メリット：アプリケーション単位で要件に合わせた設定が可能。
- デメリット：既存の列の照合順序は自動では変わりません。
また、サーバー（tempdb）と異なる照合を使うと一時テーブルやクロスDB比較でエラーが発生します。
-- DBの照合順序を確認
SELECT name, collation_name
FROM sys.databases
WHERE name = DB_NAME();
-- DBの既定照合を変更（既存列は自動で変わらない）
ALTER DATABASE SampleDB COLLATE Japanese_CI_AS;
③ 列（カラム）単位
- 概要：文字列型列（char / varchar / nchar / nvarchar）ごとに個別設定が可能です。
- メリット：列単位で細かい区別（CS/CIなど）を制御でき、多言語対応にも向いています。
- デメリット：JOINや比較時に照合不一致が起きやすく、都度 COLLATE 指定が必要になる場合があります。
-- 列単位で照合順序を指定して作成
CREATE TABLE dbo.Customers (
CustomerId int PRIMARY KEY,
FullName nvarchar(100) COLLATE Japanese_CI_AS,
Kana nvarchar(100) COLLATE Japanese_CS_AS_KS
);
-- 既存列の照合を変更（依存関係に注意）
ALTER TABLE dbo.Customers
ALTER COLUMN FullName nvarchar(100) COLLATE Japanese_CI_AS NOT NULL;
④ 式・クエリ単位（COLLATE句）
- 概要：クエリ内で一時的に照合を指定して比較やJOINを成立させる方法です。
- メリット：クロスDBやtempdbとの不一致を局所的に解決でき、既存スキーマを変更せずに対応可能。
- デメリット：クエリごとのパッチ対応になり、可読性や保守性が低下します。
また、場合によってはインデックスの利用に影響します。
-- 片側（あるいは両側）にCOLLATEを指定してJOIN
SELECT *
FROM dbo.TableA AS a
JOIN dbo.TableB AS b
ON a.Name COLLATE DATABASE_DEFAULT
= b.Name COLLATE DATABASE_DEFAULT;
-- クロスDBで異なる照合を統一する例
SELECT *
FROM DB1.dbo.Users AS u
JOIN DB2.dbo.Customers AS c
ON u.Code COLLATE Japanese_CI_AS
= c.Code COLLATE Japanese_CI_AS;
よくある不一致のケース
異なる照合順序が混在すると、次のようなエラーや不具合が発生します。
- クロスDB JOIN：DBごとに照合順序が異なると比較でエラー（Msg 468）が発生。
- 一時テーブルの利用：tempdbとユーザーDBで照合が異なると比較不可。
- 列ごとの不一致：同一DB内でも列単位で照合が異なるとJOIN／WHEREで衝突。
-- エラー例：異なる照合順序を持つ列を比較
SELECT *
FROM DB1.dbo.Users AS u
JOIN DB2.dbo.Customers AS c
ON u.Name = c.Name;
-- → Msg 468: "Cannot resolve collation conflict..."
解決策は以下の2つです。
1️⃣ クエリ内で COLLATE を明示する。
2️⃣ 設計段階でサーバー／DB照合を統一しておく。
まとめ
照合順序は、文字列処理の挙動を左右する重要な設定です。
- 設定単位は「サーバー／DB／列／クエリ」の4レベル
- 原則は上位（サーバー・DB）で統一し、列単位の例外は最小限に
- 不一致エラーは COLLATE DATABASE_DEFAULT で一時対応可能
- 既存DBの照合変更は影響範囲が大きいため、事前テストが必須
参考リンク
補足：照合順序の不一致はアプリケーションの動作や検索結果に直接影響します。
設計段階で統一方針を決めておくことが、長期的な安定運用につながります。
0 件のコメント:
コメントを投稿