SQL Server には「照合順序」という設定があります。
照合順序のドキュメントでは、次のように説明が行われています。
照合順序では、データセット内の各文字を表すビット パターンが指定されます。 また、照合順序はデータの並べ替えおよび比較を行うための規則を決定します。 SQL Server では、単一のデータベース内で異なる照合順序を持つオブジェクトを格納できます。 非 Unicode 列の場合は、照合順序の設定によってデータのコード ページと表示可能な文字が指定されます。 非 Unicode 列の間でデータを移動する場合は、移動元のコード ページから移動先のコード ページに変換する必要があります。
文字列の比較 / ソートを行うための規則のほかに、「非 Unicode 文字列型のコードページ」も照合順序の設定に依存するようになっており、char / varchar 型については、設定されている照合順序に応じたコードページが使用されて、文字列の格納が行われます。
この、文字列を格納する際に使用されるコードページについてまとめておきたいと思います
Contents
照合順序によって使用されるコードページの確認方法
SQL Server では、インスタンスレベル / データベースレベル / 列レベルで照合順序を設定することができます。
包含データベース という設定を使用することで、どの内容をどの設定の照合順序に合わせるかを調整することができ足りもしますが、今回は列に設定されている照合順序を例として考えていきたいと思います。
通常、列レベルの照合順序は、データベースレベルの照合順序を引き継いでおり、データベースレベルの照合順序のデフォルトは、インスタンスレベルの照合順序となっています。
そのため、列レベルの照合順序は、インスタンスレベルと同じ内容になっているのが通常の設定です。
列レベルで照合順序を変更したり、クエリ内で照合順序を変更することもできますが、そこまで踏み込んで整理するとややこしくなりますので、今回は各レベルの照合順序は同一の設定当という前提で。
インスタンスレベルで使用されている照合順序で使用されているコードページについては、次のようなクエリで確認をすることができます。
SELECT
name,
COLLATIONPROPERTY(name, 'CodePage') AS code_page
FROM
sys.fn_helpcollations()
WHERE
name = SERVERPROPERTY('COLLATION')
COLLATIONPROPERTY という関数があり、照合順序で使用されるコードページを確認することができるようになっています。
日本語環境に SQL Server をインストールした際のデフォルトの設定値である「Japanese_CI_AS」であれば、コードページ 932 (CP932 : Shift-JIS) が使用されます。
英語環境のデフォルトでは「SQL_Latin1_General_CP1_CI_AS」が使用されており、この照合順序の場合は 1252 が使用されることになります。
このような方法で確認をすることができるコードページが、char / varchar 型のような非 Unicode コードページで使用される文字コードとなります。
SQL Server では、Unicode 型の文字列型としては、nchar / nvharchar 型がありますが、これらのデータ型では「UTF-16」が固定で使用されるようになっており、nchar / nvarchar 型の文字コードについては照合順序に依存せず、固定のコードページが使用されます。
SQL Server 2019 で追加された UTF-8 対応の照合順序 を使用した場合は、照合順序のコードページを取得した際には、UTF-8 (65001) が設定されていることが確認できます。
「_UTF8」の照合順序を使用した場合は、char / varchar 型で使用されるコードページは UTF-8 となります。
(この照合順序を使用しても nchar / nvarchar 型のデータ型で使用されるコードページは UTF-16 であることは変わりません)
「非 Unicode 型で使用される文字コード (コードページ) は照合順序の設定に依存し、照合順序で使用されるコードページは COLLATIONPROPERTY 関数で確認できる」というのが、照合順序とコードページの関係を理解する際の最初のポイントとなるのではないでしょうか。
実際にページレベルの内容で確認をしてみる
「ドキュメントにこう書いてあった」で終わるのは好きではないので、実際に SQL Server のデータ格納領域であるページレベルで、コードページについても確認してみたいと思います。
まずは、次のようなスクリプトで、CP932 のバイナリ値を確認しておきます。
[System.Text.Encoding]::GetEncoding(932).GetBytes("8GB=1かずあき") | Format-Hex
トラディショナルなメモリサイズの指標である「8GB=1かずあき」は CP932 では「38 47 42 3D 31 82 A9 82 B8 82 A0 82 AB」というバイナリ値であることが確認できました。
それでは、SQL Server で実際にデータを投入するために、次のようなクエリでテーブルを作成し、ページ番号の取得を行います。
C1 varchar(100) という、非 Unicode の文字列型については、Japanese_CI_AS の照合順序を指定していますので、CP932 でデータが格納されます。
DROP TABLE IF EXISTS Kazuaki
GO
CREATE TABLE Kazuaki(
C1 varchar(100) COLLATE Japanese_CI_AS
)
INSERT INTO Kazuaki VALUES('8GB=1かずあき')
SELECT * FROM Kazuaki
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
今回のテーブルは TESTDB という DB に作成しており、INSERT したレコードは、ページ番号 432 に登録されていることが確認できていますので、次のようなクエリでページの情報を取得します。
DBCC TRACEON(3604)
DBCC PAGE('TESTDB', 1, 432,3)
これで、TESTDB の file_id = 1 / page_id = 432 のページ情報を取得することができます。
実際に取得したデータがこちらです。
先ほどスクリプトで確認した「38 47 42 3D 31 82 A9 82 B8 82 A0 82 AB」というバイトの内容が、SQL Server のページ情報内にも格納されていることが確認できますね。
このことから、「CP932 が使用される照合順序の varchar 型のデータには、CP932 のコードページが使用されたバイナリデータが格納されている」ことが確認できます。
SQL Server 2019 の UTF-8 対応の照合順序を使用した場合
SQL Server 2019 の UTF-8 対応の照合順序で格納した場合も見てみましょう。
[System.Text.Encoding]::GetEncoding(65001).GetBytes("8GB=1かずあき") | Format-Hex
「38 47 42 3D 31 E3 81 8B E3 81 9A E3 81 82 E3 81 8D」というバイトで構成されていることが確認できますね。
UTF-8 の場合、「かずあき」の各文字が 3 バイトで構成されますので、CP932 と比較すると使用されるバイト数が増加します。
それでは、SQL Server 側の情報も確認してみます。
DROP TABLE IF EXISTS Kazuaki
GO
CREATE TABLE Kazuaki(
C1 varchar(100) COLLATE Japanese_XJIS_140_CI_AS_VSS_UTF8
)
INSERT INTO Kazuaki VALUES('8GB=1かずあき')
SELECT * FROM Kazuaki
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
UTF-8 のバイトデータで格納されていることが確認できますね。
UTF-8 の照合順序を使用した場合、char / varchar 型には、UTF-8 のコードページでデータが格納されていることが実際の情報でも確認できました。
nchar / nvarchar 型でも確認してみる
nchar / nvarchar でも同様の確認をしてみます。
[System.Text.Encoding]::GetEncoding(1200).GetBytes("8GB=1かずあき") | Format-Hex
DROP TABLE IF EXISTS Kazuaki
GO
CREATE TABLE Kazuaki(
C1 nvarchar(100) COLLATE Japanese_XJIS_140_CI_AS_VSS_UTF8
)
INSERT INTO Kazuaki VALUES('8GB=1かずあき')
SELECT * FROM Kazuaki
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
nchar / nvarchar 型は照合順序のコードページの影響は受けず、UTF-16 固定でコードページが使用されます。
そのため、「Japanese_XJIS_140_CI_AS_VSS_UTF8」というような、char / varchar 型で UTF-8 の Unicode 文字列が使用される照合順序を使用していても nchar / nvarchar では UTF-16 (CP1200) が使用されてデータの格納が行われます。
列ごとに照合順序を変更した場合
最後に、列ごとに異なる照合順序を設定してみたいと思います。
DROP TABLE IF EXISTS Kazuaki
GO
CREATE TABLE Kazuaki(
C1 varchar(100) COLLATE Japanese_CI_AS,
C2 varchar(100) COLLATE Japanese_XJIS_140_CI_AS_VSS_UTF8
)
INSERT INTO Kazuaki VALUES('8GB=1かずあき', '8GB=1かずあき')
SELECT * FROM Kazuaki
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)
C1 については、CP932 で 13 バイトで格納され、C2 については CP65001 で 17 バイトで格納されていることが確認できますね。
このように列レベルで照合順序を設定することで、各列で異なるコードページでデータを格納することも設定としては可能です。
照合順序と文字列型のコードページの関係については、本投稿のような情報を元にしてみると、理解が進む点もあるのではないでしょうか。