SE の雑記

日本語版の SQL Server をインストールした環境の文字コード / 文字コードに関連するドキュメントについてまとめておきたいと思います。
今回はインストールタイプ (Box) の SQL Server を日本語版でインストールした環境をベースに考えていますが、これは、SQL Server のデータベースエンジンをベースとしている環境で共通の考え方になります。

過去のバージョンの情報にはなりますが、次の情報も参考になります。

• SQL Server 2005 のインターナショナル機能 2007 年 6 月改訂版
• Microsoft SQL Server 2005 のインターナショナル機能

文字コード

日本語版の SQL Server で使用される文字コードですが、基本的には次の 4 種類になるかと思います。

• CP932 (Microsoft コードページ 932 : Shift_JIS 拡張)
• Unicode
  • UCS-2
  • UTF-16LE (CP1200)
  • UTF-8 (65001)

どのような文字コード / 文字エンコードでデータを格納するかは「データ型」と「照合順序」によって変わります。

char/varchar

• char および varchar (Transact-SQL)

char/varchar 型の文字コードについては、照合順序の設定によって文字コードが変わります。

UTF8 の照合順序がついていない Japanese_xxx の照合順序を使用している場合、char/varchar は CP932 の文字コードで格納されるため、Shift_JIS 相当の文字コードが使用されます。

SELECT CAST('あ' AS varbinary(max))

このようなクエリを実行した場合、CP932 の文字コードで表現されますので、「82A0」となります。

UTF8 照合順序でない場合、照合順序に設定されている文字コードでデータが格納されるため、文字コードの範囲外の文字 (サポートされていない) については、使用している文字コードでは文字の表現ができないため「?」として登録が行われます。

Shift-JIS でサポートしていない文字 、サロゲートペアの文字を該当するケースが多いかと。

これらの文字については Shift-JIS (厳密には CP932) で登録ができないため、これらの文字の箇所については、「?」に変換されてデータが登録されます。

SQL Server 2019 で導入された UTF8 付きの照合順序を使用した場合、char/varchar は UTF-8 の文字エンコードで格納が行われます。

DROP TABLE IF EXISTS #T
CREATE TABLE #T(C1 varchar(3) COLLATE Japanese_XJIS_140_CI_AS_UTF8 )
INSERT INTO #T VALUES(N'あ')
SELECT CAST(C1 AS varbinary(max)) FROM #T

このクエリの場合、UTF8 の照合順序で varchar に文字列を格納していますので、格納された文字は UTF-8 のエンコードとなります。

そのため「E38182」となります。

nchar / nvarchar

• nchar および nvarchar (Transact-SQL)

これらのデータ型については、Unicode でデータの格納が行われますが、UCS-2 / UTF-16 の文字エンコードが使用されます。

_SC が設定されていない場合は、UCS-2、_SC が設定されている場合は UTF-16 とのことなのですが、_SC を使用していない照合順序でも、UCS-2 が対応する 0x0～0xFFFF までの基本多言語面以外の文字も格納できているので、UTF-16 が基本なのかなと思います。

SELECT CAST(N'あ' AS varbinary(max))

このクエリを実行した場合、実行結果としては「0x4230」となります。

SQL Server の UTF-16 の文字エンコードについては、UTF-16LE が使用されており、データページ内のデータについても LE のエンコードでデータが格納されていますので、下位バイトが先頭になってデータが構成されています。

そのため、上記の結果を UTF-16 で表現しようとした場合は「0x3042」となります。

照合順序

照合順序については次のような情報を確認することになります。

• 照合順序と Unicode のサポート
  • 包含データベースの照合順序
• COLLATE (Transact-SQL)
• sys.fn_helpcollations (Transact-SQL)
• 照合順序関数 – COLLATIONPROPERTY (Transact-SQL)

覚えておくと便利なのが、sys.fn_helpcollations と COLLATIONPROPERTY を組み合わせた情報取得です。

char/varchar 型はデータ型によって格納される文字コードが変わります。

どの照合順序であれば、どの文字コードが使用されるかについては次のようなクエリで確認ができます。

SELECT 
    *, 
    COLLATIONPROPERTY(name, 'CodePage') AS CodePage,
    COLLATIONPROPERTY(name, 'LCID') AS LCID,
    COLLATIONPROPERTY(name, 'ComparisonStyle') AS ComparisonStyle,
    COLLATIONPROPERTY(name, 'Version') AS Version
FROM 
    sys.fn_helpcollations() 
WHERE 
    name like 'Japanese%'
ORDER BY
    Version
GO

このクエリを実行すると次のような結果が取得できます。

「CodePage」が char/varchar で使用されるコードページになります。

照合順序にはバージョンがあり、SQL Server 2019 では 0～3 のバージョンが存在しています。

文字コードの規格は年月が進むにつれアップデートされますので、アップデートされた内容に対応がされるようにするには、新しいバージョンの照合順序を適切に設定する必要が出てくるかと思います。

(古いバージョンを使用していても UTF-16 で表現できる文字であれば、格納はできるかなと)

新しいバージョンの照合順序ではなく、追加された照合順序のオプションを使用することでも対応できる範囲がありますが。

照合順序のオプションとして、最新の文字コードに対応するための設定としては次のようなものがあります。

• _SC : 補助文字 : supplementary characters
  • 140 の照合順序では _SC が自動的にサポートされる
• _VSS : 異体字セレクターセンシティブ:Variation-selector-sensitive

_SC の照合順序を使用していない場合、サロゲートペアの文字列の比較や、文字列長の取得などを行った場合に、サロゲートペア文字列を考慮した文字の比較が行われません。

UTF-16LE の「0x42D89FDF」は、サロゲートペアの次の文字となります。

SELECT 
    LEN(CAST(0x42D89FDF AS nvarchar(2)) COLLATE Japanese_XJIS_140_CI_AS),
    LEN(CAST(0x42D89FDF AS nvarchar(2)) COLLATE Japanese_XJIS_100_CI_AS)

SC 対応の照合順序でないと、文字列長を取得しようとした場合に、1 文字ではなく 2 文字として認識が行われることになります。

_VSS の照合順序を使用した場合は、異体字セレクターの比較が厳密に行われるようになります。

異体字セレクターを厳密に確認しない、照合順序では次の比較を行った場合、両方の文字が取得されます。

SELECT
	*
FROM
	(VALUES(CAST(0x5B84 AS nvarchar(1))),(CAST(0x5B8440DB00DD AS nvarchar(3)))) AS t(C1)
WHERE
	C1 = CAST(0x5B84 AS nvarchar(2)) COLLATE Japanese_XJIS_140_CI_AS

_VSS を使用した場合は厳密に比較を行うことができます。

SELECT
	*
FROM
	(VALUES(CAST(0x5B84 AS nvarchar(1))),(CAST(0x5B8440DB00DD AS nvarchar(3)))) AS t(C1)
WHERE
	C1 = CAST(0x5B84 AS nvarchar(2)) COLLATE Japanese_XJIS_140_CI_AS_VSS

異体字セレクターについても、文字列長の取得の結果が照合順序によって異なってきます。

SELECT
	LEN(C1 COLLATE Japanese_XJIS_140_CI_AS_VSS),
   LEN(C1 COLLATE Japanese_XJIS_140_CI_AS),
   LEN(C1 COLLATE Japanese_XJIS_100_CI_AS),
   LEN(C1 COLLATE Japanese_XJIS_100_CI_AS_SC)
FROM
	(VALUES(CAST(0x5B84 AS nvarchar(1))),(CAST(0x5B8440DB00DD AS nvarchar(3)))) AS t(C1)

基本的な考えとしては、照合順序のオプションが増えた場合、その照合順序オプションを使用しないと、複雑な文字列面を使用した表現についての比較/取得の誤差が発生することになるかと。

関数

文字コードが関係する文字列関数としては次のようなものがあります。

• CHAR (Transact-SQL)
• ASCII (Transact-SQL)
• UNICODE (Transact-SQL)
• NCHAR (Transact-SQL)

CHAR / ASCII については ASCII コードベースの対応ですので本投稿では割愛し、UNICODE / NCHAR について触れておきたいと思います。

UNICODE / NCHAR は Unicode ベースの文字列関数となりますが、一般的に指定するのが Unicode コードポイントになるかと思います。(UCS-2 の範囲であれば、コードポイントとエンコードは同一な気がしますが)

ちょっと変わった Unicode の文字としては、追加多言語面の麻雀牌があります。

この面では、0x1F000 から麻雀牌の文字を表すことになるのですが、0x1F000 の文字を UTF-16LE のエンコードで表すと、「0x3CD800DC」となります。

この文字を NCHAR で出力しようとするとどうなるかというと、次のクエリとなります。

SELECT NCHAR(126976)
SELECT NCHAR(0x1F000)

UTF-16LE でエンコードした際のバイナリ値を指定するのではなく、Unicode コードポイントを指定することで想定している文字を出力することができます。

追加多言語面を NCHAR で出力するには、データベースの照合順序として _SC が使用されている必要があり、それ以外の照合順序では、追加多言語面を出力することができないかと思います。

SQL Server のバージョンアップ時には照合順序が追加されることが多く、どのようなことを実現することができるようになったのかの把握がなかなか大変ですね…。