SE の雑記

SQL Server / SQL Database を使用しており設定を初期状態で使用していると、インスタンス / データベースの照合順序は、

• Japanese_CI_AS : 日本語環境のデフォルト
• SQL_Latin1_General_CP1_CI_AS : 英語環境のデフォルト

のいずれかを使用しているケースが多いのではないでしょうか？

これらの照合順序は列レベルの照合順序の初期設定として、引き継がれますので列の比較をする際の挙動にも影響をしてきます。

照合順序については、次のドキュメントの内容を確認することになります。

• 照合順序と Unicode のサポート
• 照合順序 – 文字の比較と並び順 (その 1)
• 照合順序 – 文字の比較と並び順 (その 2)
• 照合順序 – 文字の比較と並び順 (その 2)
• 改行されていないスクリプトを確認したい場合はこちら
• Collation and Unicode Support

SQL Server では Unicode の文字の比較を考慮する際には、照合順序の次のような内容を意識する必要があるのではないでしょうか。

• サポートされる Unicode のバージョン
• Unicode コードポイント
• 比較時の重み

SQL Server では、nchar / nvarchar / char (UTF-8 サポートあり) / varchar (UTF-8 サポート) を使用することで、Unicode の文字列を格納することができ、どの照合順序を使用していてもこれらのデータ型を使用している場合は、次のようなエンコードで文字を格納することができます。

• nchar / nvarchar : UCS-2 / UTF-16
• char (UTF-8 サポートあり) / varchar (UTF-8 サポートあり) : UTF-8

ただし、格納と文字の比較は別であり、照合順序の設定によって「格納はできても想定した比較結果にならない」ということがあります。

日本語圏で考えた場合、

• 新しい Unicode のバージョンで追加された文字
• サロゲートペア
• 異体字

というような設定が関係し、「新しい Unicode のバージョンで追加された文字」については、「基本多言語面 (BMP)」である「000000 – 00FFFF」に追加されることもありますので、BMP の範囲の文字についても、照合順序の設定によっては正しく比較されないことがあります。

Unicode の情報については、https://home.unicode.org/ から確認することができ、Unicode のバージョンや、文字のコードポイントを確認する場合には、次の情報を使用するとよいのではないでしょうか。

• Unicode® Code Charts Help and Links
• Unicode Character Code Charts
• https://www.fileformat.info/info/unicode/char/search.htm

SQL Server は文字の格納については、Unicode をサポートしたデータ型を使用していれば、柔軟にデータを登録することができますが、データの比較 については、照合順序の設定が大きく影響してきます。

本投稿では、以前のバージョンの照合順序となる BIN / Unicode は使用しない前提で記載していますので、バイナリ照合順序といえば BIN2 となり、Unicode サポートの照合順序といえば、照合順序名に Unicode がついていないものを前提としています。

文字のコードポイントの確認方法

照合順序と文字コードの関係を調査する際には「文字のコードポイント」の確認が必要になるケースがあります。

「照」という文字であれば、https://www.fileformat.info/info/unicode/char/7167/index.htm からも確認することができますが次のようになるかと思います。

• Unicode コードポイント : U+7167
• UTF-16 BE : 0x7167
• UTF-16 LE : 0x6771
• UTF-8 : 0xE785A7

特定の文字のバイナリを知りたい場合には、SQL Server 上で次のようなクエリを実行することで取得ができます。(nchar / nvarchar は UTF-16 LE が使われています)

SELECT CAST(N'照' AS varbinary)
SELECT CAST('照' COLLATE Japanese_XJIS_140_CI_AS_UTF8 AS varbinary)

各 Unicode のエンコード方式でエンコードした場合のバイナリのコードポイントについては、上記の方法で知ることができるのですが、Unicode のコードポイントを SQL Server から確認することができません。

特定の文字のコードポイントを知りたい場合は、ワードパット (wordpad.exe) を起動して、文字を入力、選択した状態で「Alt+X」で Unicode のコードポイントに変換することができます。

今回のケースであれば「U+7167」ということがわかりましたので、IME パッドを起動して、

確認したコードポイントの情報を確認することで、UTF-8 / UTF-16 / Shift JIS のコードポイントを確認することができます。

冒頭で紹介した URL の情報の他に、このような方法を活用する事でもコードを確認することができます。

SQL Server がサポートする Unicode のバージョン

照合順序 – 文字の比較と並び順 (その 1) の情報で少し古いものとなるのですが、SQL Server がサポートする Unicode のバージョンは、照合順序のバージョンによって、次のように記載されています。

• バージョン記載なし (例 : Japanese_CI_AS) : SQL Server 2000 (8.0) : Unicode 2.0
• 90 (例 : Japanese_90_CI_AS) : SQL Server 2005 (9.0) : Unicode 3.2
• 100 (例 : Japanese_XJIS_100_CI_AS) : SQL Server 2008 (10.0) : Unicode 5.0

SQL Server の各照合順序でもプロパティとして、照合順序のバージョンを持っており、次のようなクエリで確認することができます。

SELECT 
	name, 
	description,
	COLLATIONPROPERTY( name, 'codepage') AS codepage,
	COLLATIONPROPERTY( name, 'version') AS version
FROM 
	sys.fn_helpcollations() 
WHERE 
	(name LIKE N'Japanese%CI[_]AS' OR name LIKE N'Japanese%CI[_]AS[_]UTF8' )
	AND name not LIKE '%Unicode%'
ORDER BY
	version

SQL Server の照合順序のプロパティのバージョンについては、Unicode のバージョンと一致する値ではありませんが、

• バージョン記載なし : 0
• 90 : 1
• 100 : 2
• 140 : 3

となっており、140 (例 : Japanese_XJIS_140_CI_AS) から、新しい照合順序のバージョンとなっていることが確認できます。

2022/3/17 時点では、140 がどの Unicode のバージョンとなるかの情報は公開されていないようではあるのですが、140 については Unicode 6.0 以降の対応を進められているものとなっているのではないでしょうか。

追加多言語面の比較

「追加多言語面」(SMP) については、Unicode 3.2 で追加が行われたものとなります。

Unicode 2.0 では、「基本多言語面」(BMP) の範囲となる「0 – 0xFFFF 」までのサポートとなるため、バージョン無しの照合順序 (例 : Japanese_CI_AS) では、「重みによる文字の比較」を「追加多言語面」以降に含まれる文字で実施することはできません。

追加多言語面の文字で「重みによる文字の比較」を実施するためには、追加多言語面がサポートされる Unicode 3.2 以降が使用される、90 以降の照合順序を使用する必要があります。

Unicode の進化によって、「基本多言語面」にも新しい文字が追加されることがありますので、照合順序の設定によっては、基本多言語面の文字の比較についても、使用しているバージョンによっては動作が異なるものが出てきます。(90 では重みの比較ができないが、100 ではできる文字というものがあります)

重みによる比較を実施する場合は、「新しいバージョンの Unicode で各面に追加された文字が重みによる比較を実施できるか」が重要なポイントとなるのではないでしょうか。

初期設定の照合順序を使用した場合の問題

それでは、初期設定の照合順序を使用した場合の問題について見ていきます。(絵文字等が入ってくるので、以降のクエリは画像キャプチャとしています)

上記のクエリは、次の 2 つの文字を Japanese CI_AS / SQL_Latin1_General_CP1_CI_AS の照合順序の列に投入しています。

どちらの文字も「追加多言語面」の文字でありサロゲートペアが使用されているものとなります。

上記のようなデータが格納されている場合に、次のクエリを実行するとどうなるでしょうか？

初期設定の照合順序では、実行結果は次のようになります。

「各絵文字を設定」したものと「です」という文字を一致で検索しているのですが、想定通りの検索結果となっていませんね。

C1 (Japanese_CI_AS) / C2 (SQL_Latin1_General_CP1_CI_AS) のどちらの照合順序でも同様の結果となっています。

このような結果となっている理由

今回、検索に使用した文字は「追加多言語面」に含まれている文字となります。

しかし、Japanese_CI_AS / SQL_Latin1_General_CP1_CI_AS というような照合順序では、バージョン 0 / Unicode 2.0 の文字について考慮されたものとなります。

バージョン 0 の照合順序については、コードポイントは「基本多言語面」(0 – 0xFFFF) の範囲となり、「追加多言語面」に含まれている文字の「重みづけ」には対応していないようです。

「重みづけ」に対応していない文字については、「重みづけにより比較を行う照合順序で比較時の対象から除外」されているようで、「絵文字+です」となっている検索については、絵文字の部分の比較が除外され「です」と同じ重みになるかの比較が行われるようです。

「重みづけのある照合順序」での対応方法案

「追加多言語面」に含まれている文字の「重みづけ」に対応している照合順序は、バージョン 1 以降の照合順序を使用する必要があり、今回のクエリであれば、テーブルの定義を次のようにして、バージョン 1 以降の照合順序を使用してすることで、

想定した検索が行えるようになります。

比較する文字の内容のよっては、バージョン 1 ではなく、2 , 3 を使用する必要があります。(バージョン 1 ではサポートされておらず、それ以降でサポートされるようになった文字の対応)

「BIN2 以外の照合順序」を使用する場合は、「照合順序が比較対象の文字の重みづけをサポートしているか」が重要なポイントとなります。

コードポイントによる比較と重みによる比較

SQL Server の文字列の比較については、

• コードポイントによる比較 (BIN2 照合順序の使用 : 例 : Japanese_XJIS_140_BIN2)
• 重みによる比較 (BIN2 以外の照合順序の使用 : 例 : Japanese_XJIS_140_CI_AS)

の 2 種類があります。

コードポイントによる比較

コードポイントによる比較はシンプルで、「格納されているデータと比較対象のデータのバイナリコードポイントが同一かどうか」で判断が行われます。

「BIN2」の照合順序を使用した場合は、この比較方法となり、

• 「あ」 と 「ア」を同一とする : かなとカナを区別しない
• 「ア」と「ｱ」を同一とする : 全角と半角を区別しない

というような文字の比較は行うことができず、「完全一致」による比較のみとなり、検索における柔軟性に制限が発生します。

しかし、「SQL Server の照合順序で対応していない文字の重みづけがあった場合」でも、バイナリコードポイントによる比較であれば、比較を行うことができますので、未知の文字に対しても対応を行うことができます。

現時点の最新のバージョンである SQL Server で最新の照合順序を使用する場合「Japanese_XJIS_140_CI_AS_UTF8」(UTF_8 はなくてもよいですが) となるのではないでしょうか。

この照合順序を使用したテーブルを作成して、次のようなデータを投入してみます。

今回使用している絵文字は「基本多言語面」に存在している「装飾文字」の絵文字となります。

このようなデータを投入している状態で、次のクエリを実行するとどのようなデータが取得されるでしょうか？

取得されたデータが次の内容となります。

「です」で比較をした場合に、「U+2700」の文字が含まれるデータの検索が実施できてしまっていますね。

「Japanese_XJIS_140_CI_AS_UTF8」を使用しても、重みづけを設定できない文字が存在していることが確認できました。

このような文字が登録される場合に、想定した検索を行う場合は、「BIN2 照合順序」を使用します。

BIN2 照合順序であれば、検索の柔軟性は低下しますが、未知のコードポイントのデータについても比較を行うことができ、想定した検索結果となる可能性が高くなります。

BIN2 照合順序を使用した場合、照合順序と Unicode のサポート に記載されている補助文字 (SC) のサポートを有効化することができないため、「SCA 照合順序なし」の動作となります。

文字列の比較については、精度が上がりますが、文字列関数や特定のワイルドカードの使用については、動作に制限が出ますので、BIN2 照合順序を使用する場合は、この制限を受け入れる必要があることは注意点となります。

重みによる比較

「重みによる比較」は、「各文字をどのように比較するかは指定された比較方法に応じた重みづけ」により、同じ文字かどうかの判断を行うという方法となり、「BIN2 以外の照合順序を使用している場合に採用される方法」となります。

ポイントとなるのが「重みが設定できない文字については除外されて比較が行われるような動作となっている」ということではないでしょうか。

先ほどの例でいえば、次の文字については、最新の照合順序でも想定した比較ができない = 重みが判断できなかった文字となります。

このような文字が含まれている場合、想定した比較とならない可能性がありますが、

• 「あ」 と 「ア」を同一とする : かなとカナを区別しない
• 「ア」と「ｱ」を同一とする : 全角と半角を区別しない

重みが判断できる文字であれば、というような比較を行うことができ、検索の柔軟性は向上します。

「重みが判断できない文字」の対応については、等値検索だけでなくいくつかのケースで弊害が発生することがあります。

キー重複の可能性

重みが設定できない場合、該当の文字が無視されます。そのため次のレコードを投入しようとした場合はキー重複が発生します。

Japanese_CI_AS では判断できない重みの文字でユニークになるデータを登録しようとしていますが、重みを判断できないため、該当の文字が無視され「です」という文字列のレコードが重複するものとみなして、キー重複によるエラーが発生します。

インデックスによる範囲検索が正しくできない

次のようなクエリを実行したとします。

この時の C1 による検索は次のようになります。

Index Scan が実行されていますね。

クエリを「SELECT C1 FROM T1 WHERE C1 LIKE N’A%’」のように変更した場合は Seek が実行されます。

前方一致でインデックスに対しての検索を実行した場合、内部では、

• 指定した文字以上
• 次の重みの文字未満

というような範囲検索が行われています。

しかし「重みが判断できない文字」で検索を行った場合、どのような範囲で重みを制御すればよいかのが Algebrizer が判断ができず、論理ツリーを正しく生成することができません。

‘A%’ で検索を行った場合、論理ツリーの設定としては次のような内容で生成が行われます。

65 = A / 66 = B となるのですが、これらの値を使用して LIKE の検索を行うような設定となっています。それでは使用している照合順序では重みづけができない文字を使用した場合はどうなるでしょうか？

先ほどは値が設定されていた部分が「NULL」となっていることが確認できます。

重みづけが判断できない文字が使用された場合、範囲検索を行うための文字を指定することができないため、このような論理ツリーとなり、結果として、シーク述語として指定した文字が使用できないというような動作になっているのかと思います。

「キー重複の可能性」「インデックスによる範囲検索が正しくできない」については、最新のバージョンの照合順序を使用することで、サポートされる Unicode バージョンが強化され文字の重みづけが行われることで解消されるケースもありますが、解消されない場合は BIN2 の照合順序を使用する必要が出てくるのではないでしょうか。

最新のバージョンの照合順序を使用しても、BIN2 以外では、U+2700 の Unicode 7.0 で追加されたハサミの文字の比較がうまくできなかったので、重みを利用する最新の照合順序でも、比較できない文字というものは存在しています。

まとめ

文字コードについては、

• 寿司/ ビール問題 (SMP : サロゲートペア)
• かまどねずこ問題 (異体字)

のようにいろいろなパターンがあり、毎度頭を悩ませる問題ではあります…。

SQL Server であれば、UTF-8 / UTF-16 で表せる文字であれば、照合順序のバージョンに依存せず、格納することはできても、比較の段階になると照合順序のバージョンが大きく影響し来ます。

• 検索の柔軟性を犠牲にして比較できる文字の範囲を広げるため、最新の照合順序の BIN2 を使用する
• 検索の柔軟性のメリットを活かすため、比較できない文字 (重みを設定できない文字) は許容するための最新の BIN2 以外の照合順序を使用する

のどちらかを採用することになると思うのですが、文字コード (Unicode  バージョン) の進化に合わせて、現在の環境であれば、どのような検索が可能なのかを意識するのは大事ですね。