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

2016-07-26

神谷雅紀
Escalation Engineer

照合順序 - 文字の比較と並び順 (その 1) では照合順序とは何かを書きました。今回は、照合順序に関わるいくつかの注意点について書きます。

照合順序の衝突

異なる照合順序が指定されている列同士は、比較することができません。

以下は、その簡単なサンプルです。

use mastergodrop database ja_90_bin2go-- 照合順序 japanese_90_bin2 のデータベースを作成create database ja_90_bin2 collate japanese_90_bin2gouse ja_90_bin2go-- 照合順序 japanese_90_bin2 のデータベースに japanese_90_ci_as と japanese_90_cs_as の列を持つテーブルを作成create table dbo.ja_90_cias (c1 int, c2 nvarchar(10) collate japanese_90_ci_as)create table dbo.ja_90_csas (c1 int, c2 nvarchar(10) collate japanese_90_cs_as)go-- japanese_90_ci_as と japanese_90_cs_as の列でテーブルを結合select * from dbo.ja_90_cias i inner join dbo.ja_90_csas s on i.c2=s.c2go-- 実行結果メッセージ 468、レベル 16、状態 9、行 1equal to 操作の "Japanese_90_CS_AS" と "Japanese_90_CI_AS" 間での照合順序の競合を解決できません。

これが問題になる最も一般的な例が、tempdb 上に作成される一時テーブルです。

use ja_90_bin2go-- ユーザーデータベースにテーブルを作成create table dbo.parmanent_tab (c1 int, c2 nvarchar(10))-- 一時テーブルを作成create table #temporary_tab (c1 int, c2 nvarchar(10))go-- これらのテーブルを結合select * from dbo.parmanent_tab p inner join #temporary_tab t on p.c2=t.c2go-- 実行結果メッセージ 468、レベル 16、状態 9、行 1equal to 操作の "Japanese_CI_AS" と "Japanese_90_BIN2" 間での照合順序の競合を解決できません。

一時テーブルは tempdb に作成されますので、一時テーブルの列は明示的に指定しない限り、tempdb の照合順序を継承します。tempdb は、SQL Server インストール後に明示的に変更していない限り、SQL Server インストール時に指定したインスタンスの照合順序に設定されています。そのため、ユーザーデータベースやテーブルの作成時に tempdb とは異なる照合順序を指定した場合には、注意が必要です。

照合順序が同一ではない列の比較を行う場合は、以下のように、比較に使用する照合順序を明示的に指定する必要があります。

select * from dbo.parmanent_tab p inner join #temporary_tab t on p.c2=t.c2 collate japanese_90_cs_as

データベース照合順序とメタデータ参照

データベースの照合順序は、メタデータにも適用されます。

例えば、BIN2 照合順序のデータベースにあるテーブルを参照する場合は、テーブル名や列名の大文字小文字が区別されます。

以下は、その例です。テーブル名の最初の文字 j は小文字ですが、クエリでの参照では J と大文字になっているため、エラーとなります。

select * from dbo.Ja_90_cias i inner join dbo.ja_90_csas s on i.c2=s.c2go-- 実行結果メッセージ 208、レベル 16、状態 1、行 1オブジェクト名 'dbo.Ja_90_cias' が無効です。

日本語におけるアクセント

「ぁ」などは、Japanese および Japanese_90 照合順序では、大文字小文字 (Case) ではなくアクセント (Accent) として扱われます。

「々」「ー」(長音) などは、直前の文字の繰り返しであり、それ自体は意味を持たないものとして解釈されます。

if N'あ' = N'ぁ' collate japanese_90_ci_aiprint N'equal'elseprint N'not equal'goif N'あ' = N'ぁ' collate japanese_90_ci_asprint N'equal'elseprint N'not equal'-- 実行結果equalnot equal

「重み (weight)」がない文字を用いた比較

BIN もしくは BIN2 照合順序として比較することで、重みが定義されていない文字の比較が可能になります。

付録 : 文字一覧の作成スクリプトサンプル (SC 照合順序サポートバージョン (SQL Server 2012 以降) 用)

マイクロソフトでは、個々の照合順序ごとの文字の並び順の一覧や並び順の規則などは公開していません。並び順を確認する必要がある場合は、確認の必要な照合順序ごとに、以下のようなスクリプトにより確認して下さい。

-- 準備if db_id(N'collation') is not nulldrop database collationgocreate database collation collate Japanese_XJIS_100_CI_AS_SCgouse collationgodrop table dbo.t1gocreate table dbo.t1(k int,c nvarchar(2) collate Japanese_XJIS_100_CI_AS_SC)godeclare @c intset @c=0while (@c <= 0x10FFFF)begininsert into dbo.t1 values (@c, NCHAR(@c))set @c+=1endgo-- Japanese_XJIS_100_CI_AS_SC 順select * from dbo.t1 order by c collate Japanese_XJIS_100_CI_AS_SC,kgo-- Japanese_90_CI_AS 順select * from dbo.t1 order by c collate Japanese_90_CI_AS,kgo

-- Japanese_CI_AS 順select * from dbo.t1 order by c collate Japanese_CI_AS,kgo

-- Japanese_CI_AS 順select k as 'code', c 'char',casewhen NextChar = c collate Japanese_CI_AS then '='else '!=' end as 'EQ/NEQ',NextChar as 'next char',unicode(NextChar) as 'next char code'from(select k, c, lead(c,1,0) over (order by c collate Japanese_CI_AS, k) as 'NextChar' from dbo.t1) Xorder by c collate Japanese_CI_AS, kgo

日付	更新内容
2014/01/15	公開	神谷
2016/07/26	リンク等修正	神谷

Share via