照合順序 - 文字の比較と並び順 (その 1)
神谷 雅紀
Escalation Engineer
照合順序が分かりにくいという意見がありましたので、今回は照合順序を取り上げます。
照合順序とは何か
SQL Server では、文字の大小関係を比較する場合の基準を照合順序 (collation) と呼んでいます。例えば、「朝」と「海」ではどちらが大きいのか、「あ」「ア」「ｱ」を大きい順に並べた場合どのように並ぶのかといった、文字の大小関係を決めているのが照合順序です。
言語としての日本語の観点では、「朝」と「海」のどちらが大きくても、さほど問題にはならないように思えるかもしれません。しかし、もしこれらの文字に大小関係がなかったら、データを大きい順に並べても毎回違った並び順になる可能性があります。さらに、もしこれらの文字に大小関係がなかったら、大きくも小さくもないということになります。大きくも小さくもないということは、
if (a < b) …
else if (a > b) …
else …
の式で最後の else に入るということであり、そこに入るのは a = b の場合だけです。つまり、等しいということです。「朝」と「海」が等しいとなると、それは言語としての日本語でも問題となってきます。
このように、照合順序 (文字の大小関係) は、データを扱う処理にとっては、重要な要素です。
照合順序はどのような場面で使われるのか
文字の比較を行うすべての場面で使われます。
例えば、インデックスを作成する際には、キー列の値順にインデックス行を並び替えるために使われます。select … from … order by Col1 のようなクエリでデータを並び替える際にも使われます。select … from … group by Col1 のようなクエリでグルーピングを行う際にも使われます。また、if (@a = @b) のような if ステートメントによる比較でも使われます。select … from … where Col1 > N’X’ のようなクエリの検索条件内での比較でも使われます。
つまり、使用する照合順序が違うと、文字の大小関係が異なるため、同じクエリでもその結果は違ったものになってきます。
SQL 照合順序
SQL Server の照合順序には 2 種類あり、ひとつが SQL 照合順序、もうひとつが Windows 照合順序です。照合順序の名前が “SQL_” から始まるものが SQL 照合順序、そうでないものが Windows 照合順序です。
SQL 照合順序では、非 Unicode データについては、Windows とは互換性のない SQL Server 独自の方法による大小比較が行われます。この方法は、Unicode データ型をサポートしていなかった SQL Server 6.5 以前のバージョンで行われていた方法です。また、非 Unicode データと Unicode データとで比較規則が異なるため、同じ文字を比較した場合であっても、非 Unicode データと Unicode データでは結果が異なる場合があります。
SQL 照合順序は、SQL Server 6.5 以前のバージョンとの互換性のみを目的としている照合順序であるため、SQL Server 6.5 以前のバージョンの動作に依存した、前世紀から存在するような古いアプリケーションを実行している場合を除けば、あえて SQL 照合順序を選択する必要性はありません。
そのため、以降は、特に記載していない限り、すべて Windows 照合順序についての内容です。
照合順序名の構成
SQL Server 2012 SP1 には、今確認してみたところ、サポートしている全言語で 3885 種類、日本語に限っても 138 種類の照合順序が用意されています。これら照合順序の比較結果がどうなるのかは、その名前から判断することができます。照合順序の名前には、次の規則があります。
日本語照合順序のひとつである Japanese_90_CS_AS_KS_WS_SC を例に見てみましょう。Japanese_90_CS_AS_KS_WS_SC は、 “_” で区切られる部分ごとに以下の意味があります。
値 |
意味 |
Japanese |
辞書順に並び変えた場合の並び順が、日本語辞書順であることを表しています。また、非 Unicode データ型の場合は、そのデータの言語 (コードページ, code page) が何であるのかを表します (後述します)。非 Unicode データ型では、言語によって同じ文字コード (文字を識別するための数値) に異なる文字が割り当てられているため、この部分が異なると同じ文字コードでも異なる文字になります。 |
90 |
照合順序のバージョンを表しています。 90 はバージョン 9.0 である SQL Server 2005、100 はバージョン 10.0 である SQL Server 2008 を表しています。 |
CS |
2 文字目の S または I は以下の意味です。S (Sensitive) は区別する。I (Insensitive) は区別しない。 |
AS |
辞書順に並べる場合に濁点や半濁点などの有無を区別するかどうかを AS または AI のいずれかの形で表します。 1 文字目の A は Accent (アクセント) の意味です。2 文字目の意味は C (大文字小文字) と同じです。 |
KS |
辞書順に並べる場合に平仮名カタカナを区別するかどうかを KS または KI のいずれかの形で表します。 1 文字目の K は Kana (カナ) の意味です。2 文字目の意味は C (大文字小文字) と同じです。 |
WS |
辞書順に並べる場合に全角半角を区別するかどうかを WS または WI のいずれかの形で表します。 1 文字目の W は Width (幅) を表しています。2 文字目の意味は C (大文字小文字) と同じです。 |
SC |
SQL Server 2012 以降で、辞書順に並べる場合に補助文字を認識するかどうかを区別します。 SC は Supplementary Character (補助文字) の意味です。SC が付いている照合順序は補助文字を認識し、文字列の文字数を返す LEN 関数などの動作に影響します。 |
並び順は、辞書順ではなくバイナリ順 (ビット配列順、文字コード順、コードポイント順) もあります。バイナリ順の照合順序の名前は、Japanese_90_BIN や Japanese_90_BIN2 のように CS_AS_KS_WS の部分が BIN または BIN2 となります。
値 |
意味 |
BIN |
文字列中の最初の一文字のみコードポイント (各文字に割り当てられたビット配列) による比較を行い、以降はバイトごとの比較を行います。 SQL Server 2000 以前の古いバージョンとの互換目的での使用を除いて、使用する必要性はありません。 |
BIN2 |
すべての文字をコードポイントによる比較を行います。 |
日本語照合順序の違い
これを読んでいる人のほとんどは、日本語を扱っていると思いますので、ここでは、複数ある日本語の照合順序の違いについて説明します。
SQL Server 2016 製品出荷 (RTM) 版では、「照合順序名の構成」で説明した “Japanese” にあたる部分には、Japanese の他に、Japanese_XJIS, Japanese_Bushu_Kakusu, Japanese_Unicode の合計 4 種類があります。
日本語の照合順序は以下のステートメントで一覧できます。
select collationproperty(name,'LCID') as LCID,
cast(collationproperty(name,'ComparisonStyle') as varbinary(4)) as ComparisonStyle,
name
from sys.fn_helpcollations()
where cast(collationproperty(name,'LCID') as int) & 0xFFFF = 0x0411
order by name
余談ですが、Windows の LCID は言語 ID とソート ID から構成されていて、上位 12 ビットは現状使用されておらず、次の 4 ビット (16 ～ 19 ビット) がソート ID、下位16 ビット (0 ～ 15 ビット) が言語 ID です。言語 ID 0x0411 は日本語です。SQL Server での LCID も Windows のものと全く同じです。ComparisonStyle についても、プログラム (今となってはアンマネージドもしくはネイティブというべきでしょうか) を書く人は、上のステートメントの実行結果を見て何となく気づいたのではないかと思いますが、CompareString API の dwCmpFlags パラメータに指定する値と同じです。例えば、日本語環境でよく使われている Japanese_CI_AS の ComparisonStyle は 196609 (0x00030001) ですので、これは、NORM_IGNORECASE | NORM_IGNOREKANATYPE | NORM_IGNOREWIDTH ということになります。
話を元に戻して、各照合順序の LCID を見てみます。 ソート ID 欄の ( ) 内の “SORT_” は、winnt.h での定義です。
照合順序 |
LCIDソート ID (16 ～ 19 ビット目) |
Japanese |
0x00000411 (1041)0x0 (SORT_JAPANESE_XJIS) |
Japanese_XJIS |
0x00000411 (1041)0x0 (SORT_JAPANESE_XJIS) |
Japanese_Bushu_Kakusu |
0x00040411 (263185)0x4 (SORT_JAPANESE_RADICALSTROKE) |
Japanese_Unicode |
0x00010411 (66577)0x1 (SORT_JAPANESE_UNICODE) |
これを見て分かるように、照合順序の名前は、LCID、言い換えれば、言語とソート方法に対応しています。つまり、Japanese_XJIS と Japanese_Bushu_Kakusu の何が違うかと言えば、言語はどちらも日本語ですが、ソート順、つまり、文字の大小関係の定義が違うのです。
では、Japanese と Japanese_XJIS は同じソート ID を持っていますが、これは何が違うのでしょうか？
ここで、上で説明したバージョンが関連してきます。Japanese_XJIS にはバージョン 100 しかありません。一方、Japanese には、バージョンなし (バージョン 80 の意味)、バージョン 90 があります。つまり、これらは、バージョンが違うのです。
では、バージョンが違うと何が違うのでしょうか？
答えは、カバーしている文字です。
SQL Server は、その時々の Windows が対応する Unicode バージョンに対応してきました。Unicode バージョンは、バージョンが上がるほど含まれる文字数が多くなってきています。つまり、これらの照合順序は、同じソート ID なのですが、より上位の照合順序バージョンは、より上位の Unicode バージョンに対応しており、より上位の Unicode バージョンはより多くの文字を含んでいるのです。
以下は、照合順序バージョンと Unicode バージョンの対応です。
※ 各バージョンの Unicode で定義されている文字の一覧に興味がある場合は、Unicode Consortium のサイトで探してみて下さい。
照合順序バージョン |
SQL Server バージョン |
Unicode バージョン |
記載なし |
SQL Server 2000 (8.0) |
Unicode 2.0 |
90 |
SQL Server 2005 (9.0) |
Unicode 3.2 |
100 |
SQL Server 2008 (10.0) |
Unicode 5.0 |
ちなみに、日本語照合順序 Japanese と Japanese_XJIS_100、Japanese_Bushu_Kakusu_100 の比較で解説されているように、Japanese_XJIS と Japanese_Bushu_Kakusu は、Windows Vista/2008 で追加された並び順です。
このように SQL Server の辞書順は、Windows に準じています。Windows 照合順序と呼ばれるのも、これが理由です。
Japanese_Unicode に関する注意
Japanese_Unicode は、SQL Server 7.0 との互換性のためだけに残されています。SQL Server 7.0 との互換が必要なアプリケーションが存在している場合を除いて、この照合順序を使用する必要性はありません。
なぜバージョン？
なぜ SQL Server はひとつの照合順序、例えば、Japanese を新しい Unicode バージョンに対応させるのではなく (既存の照合順序に対応文字を加える) のではなく、別の新しい照合順序、Japanese_90 や Japanese_XJIS_100 を追加しているのでしょうか？
その答えを考える前提として、Unicode バージョンが対応していない文字の扱いを見てみましょう。
対応していない文字の扱い
Unicode バージョンによって対応する文字数が増えているのは、上で説明したとおりです。では、対応していない文字は全く扱えないのでしょうか？例えば、Unicode 3.2 に含まれない文字は、Japanese_90 照合順序の列に格納できないのでしょうか？
そんなことはありません。対応していない文字であっても、データベースに格納することはでき、また、取り出すこともできます。
では、何ができないのでしょう？
それは、比較です。
対応していない文字であっても、データベースに格納したり取り出したりすることはできますが、比較はできません。
なぜ比較ができないでしょうか?
それは、文字に重み (weight) がないからです。
Books Online にも「これまでは重み付けがなく、等価として比較されていた文字に、重み付けが追加されました。(Weighting has been added to previously non-weighted characters that would have compared equally.) 」といった記載があります。
文字の重み (weight)
辞書順の照合順序の場合、文字は、文字もしくはその文字のコードポイント (文字に割り当てられた特定のビット配列、文字コード) を比較するのではなく、その文字に割り当てられた「重み」(weight) を比較することで、大小関係を判断します。
「重み」とは、その文字の属性です。例えば、”A”, “a”, “Ａ” はどれもアルファベットの最初の文字という属性を持っています。また、”A” と “Ａ” は大文字、”a” は小文字という属性を持っています。さらに ”A” と “a” は半角、”Ａ” は全角という属性も持っています。
ここまで説明すると、多くの人は気づいたのではないかと思います。これら属性は、辞書順照合順序の Case, Accent, Kana, Width と対応しています。文字の比較は、このような、各文字に割り当てられたこれらの属性を比較します。その比較時に、指定されている照合順序によって、どの属性を比較し、どの属性を比較しないかが決まります。
この「重み」がない文字は、実質的に比較対象となる値がないため、比較を行うことができなくなります。
これまでのところ、「重み」のない文字は便宜的に重みが 0 と見なされるため、異なる文字であっても、同じ文字と見なされます。これが、上で抜粋した Books Online の記載「これまでは重み付けがなく、等価として比較されていた文字」です。
簡単なサンプルです。
コードポイント 18240 (0x4740) と 18241 (0x4741) の文字を以下のように比較すると、バージョン 80 では同じ、90 では異なる文字と判断されます。
if N'䝀' = N'䝁' collate Japanese_ci_as
print '80 - true'
else
print '80 - false'
go
if N'䝀' = N'䝁' collate Japanese_90_ci_as
print '90 - true'
else
print '90 - false'
go
再び余談ですが、Windows には LCMapString という API があります。この API に LCMAP_SORTKEY を指定すると、指定した文字のソートキーが返されます。このソートキーが指定した文字の「重み」です。もし興味があれば、実際の値を見ることができます。
なぜバージョン？
改めて、なぜ SQL Server はひとつの照合順序、例えば、Japanese を新しい Unicode バージョンに対応させるのではなく (既存の照合順序に対応文字を加える) のではなく、別の新しい照合順序、Japanese_90 や Japanese_XJIS_100 を追加しているのかを考えてみます。
もし、そうしなかったらどうなるか考えてみましょう。もし、新しい照合順序ではなく、既存の照合順序を拡張していったらどうなるか。
新しい文字が追加されている (「重み」を持った文字が増えている) ということは、そうでなかった時とは文字の大小関係が変わっているということです。文字を並べ替えた時の順番も変わっている可能性があるということです。
これは、データベースにとっては大きな問題です。既存のインデックスは、そのインデックスが作成された時の文字の大小関係に従って並べられています。その後は、データが追加変更された時の文字の大小関係に従って決定された場所に格納されます。もし、インデックス作成後に文字の大小関係が変わってしまったら、インデックス内のデータは一定の並び順ではなくなってしまいます。インデックスは一定の並び順が保たれているから、それを使ったデータの絞り込みが可能なのであって、一定の並び順になっていないのであれば、意味はありません。もし、インデックス作成後に文字の大小関係が変わったら、既存のインデックスはすべて再構築しなければなりません。
Windows では、照合順序は SQL Server のようにバージョン管理されていないため、文字が追加されて大小関係が変わっているかどうかを確認することのできる API GetNLSVersion が用意されています。文字の大小比較を Windows に完全に依存して行っている場合は、この API を用いて、インデックス作成時と現在の文字の大小関係が変わっていないかどうか (NLS バージョンが変わっていないかどうか) を確認し、変わっている場合には、インデックスの再構築を行う必要があります。
SQL Server の場合は、上のとおり、照合順序をバージョン管理しており、Windows に完全には依存していないため、異なる Windows バージョン上でデータベースを移行したとしても、インデックスを再構築する必要はありません。
※ SQL Server でも、SQL Server for Windows CE は、文字の大小比較を Windows の機能を用いて行っています。そのため、NLS バージョン (Windows の照合順序バージョン) が変わると、データベースオープン時にインデックスはすべて再構築されます。その結果、データベースのオープンに時間を要する場合があります。どのバージョンの Windows 間で NLS バージョンが変わるのかは、実際に各 Windows 上で GetNLSVersion を呼び出してみると分かります。
照合順序の適用対象
照合順序は、インスタンス、データベース、列、式に指定できます。明示的に照合順序を指定しない場合、データベースはインスタンスの、列はデータベースの、式はデータベースの照合順序を継承します。
例えば、インスタンスの照合順序が Japanese_CI_AS であれば、他の照合順序を明示的に指定して作成しない限り、そのデータベースに作成されるテーブル内の列は、Japanese_CI_AS になります。明示的に指定すれば、列ごとに照合順序を変えることもできます。
式への指定は、式に対して直接 collate 句を指定することで行えます。例えば、select … where c1 = @a collate Japanese_BIN2 と指定すれば、c1 列の照合順序が Japanese_CI_AS と定義されていたとしても、この select に関しては、Japanese_BIN2 を用いて比較が行われます。collate 句は、if (@a = @b) collate Japanse_BIN2 といったように、クエリではないステートメントでも使用可能です。
一時的に照合順序を変更したい場合や、ストアドプロシージャや関数などでどのデータベースで実行された場合も同じ照合順序を使いたい場合などに便利です。
長くなってきましたので
長くなってきましたので、このあたりで一旦区切りたいと思いますが、ここに書いた内容が理解できていれば、ほとんどの場合、どの照合順序をどのように使えばいいのかを判断できるのではないかと思います。
もし、「いや、まだこれが漏れている」「照合順序を選ぶためには、これも知っている必要がある」といった内容があれば、この Blog にメールもしくはコメントして下さい。
次は、照合順序に関わる考慮点などについて書こうと思っています。
日付 |
変更内容 |
|
| 2013/10/16 | 公開 | 神谷 |
| 2016/07/26 | ブログシステム変更に伴うフォーマット修正SQL Server 2016 に関する情報追記 | 神谷 |
Comments
- Anonymous
June 17, 2014
照合順序 - 文字の比較と並び順 (その 2)<a rel="nofollow" target="_new" href="https://blogs.msdn.microsoft.com/jpsql/2016/07/26/2-3/