SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

SQL Server の UTF-8 サポートのデータストアへの格納と取得について

leave a comment

SQL Server のクライアント/サーバー間のデータ通信に使用される文字コードについて に書いた内容と近いものとなりますが、自分の中で情報を整理しておきたいと思ったので投稿を遺しておきたいと思います。

SQL Server 2019 で UTF-8 のサポート が追加されており、SQL Server が UTF-8 をどのように取り扱うかについての確認となります。

SQL Server の文字の取り扱いについては、Windows / SQL Server の両観点から情報を確認する必要があるかと思いますので、次の情報も参照するとよいかと思います。

Unocode 文字データ型の N” の修飾についての情報

SQL Server で Unicode 文字データを取り扱うとき、該当の文字を「N”」で修飾することがあります。日本語圏でよく見るサンプルの文字としては、「N’森鷗外る’」ですね。

この N” の修飾については次のようなドキュメントで解説が行われています。

注意

Unicode ではない文字列定数と変数は、データベースの既定の照合順序に対応するコード ページを使用します。 コード ページの変換は、Unicode 以外の文字データのみで作業し、Unicode ではない文字データ型 charvarchar、および text を参照する場合に発生する可能性があります。 SQL Server は、コード ページがデータベースの既定照合順序に対応するコード ページと異なる場合に、Unicode 以外の文字列定数および変数を、参照される列または COLLATE を使用して指定された列の照合順序に対応するコード ページに変換します。 最適なマッピングが見つかった場合、新しいコード ページで見つからない文字はすべて類似した文字に変換されます。それ以外の場合は、既定の置換文字 "?" に変換されます。

複数のコード ページを処理する場合、コード ページの変換を避けるために、文字定数の先頭に大文字の ‘N’ を付け、Unicode 変数を使用することができます。

Unicode 文字データ型 ncharnvarcharntext を参照している場合は、’expression‘ の前に大文字の ‘N’ を付ける必要があります。 ‘N’ が指定されていない場合、SQL Server では、文字列はデータベースまたは列の既定の照合順序に対応するコード ページに変換されます。 文字列がこのコード ページにない場合は、失われます。

データベースの照合順序 / 列レベルの照合順序で「_UTF8」を使用している場合とそうでない場合で挙動が変わってきます。

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


SELECT COLLATIONPROPERTY(name, 'CodePage') AS code_page, * 
FROM sys.fn_helpcollations()
WHERE name LIKE 'Japanese%'

 

日本語の照合順序の場合、「_UTF8 の照合順序」であれば「65001」(UTF-8) 、「_UTF8 がつかない照合順序」では「932」(Shift-JIS) がコードページとして利用されます。

そのため、UTF-8 サポートの照合順序を使用している場合、N” を使用しなくても Unicode 固有で Shift-JIS に含まれていない文字についても取り扱いができるようになります。

SQL Server に対してのクエリについては、UTF-16 で SQL Server に対して送信が行われていたはずですので、クエリ内の文字については基本的については基本的に Unicode ベースで考えられているかと。

 

リッチテキストエディタと IME パッドでコードページを確認する

文字コードを確認する場合、ワードパッドや Word のようなリッチテキストエディターと IME パッドを組み合わせて使用する方法があります。

今回は Word を使用しているのですが、文字を選択して、

image

「Alt+x」を押すと、選択した文字が Unicode のコードに変換されます。

image

今回の結果であれば「あ」は「3042」ということが確認できました。このコードが確認出来たら「IME パッド」で UTF-8 / UTF-16 / Shift-JIS のコードを確認することができます。

IME パッドは IME のプロパティから確認できます。

image

3042 を IME パッドで確認すると次のような情報が確認できます。

image

  • UTF-16 (BE) : 0x3042
  • UTF-8 : 0xE38182
  • Shift-JIS : 0x82A0

であるということが確認できますね。

 

本投稿で使用するサンプルデータ

本投稿では、「Japanese_CI_AS」がインスタンスレベルの照合順序として設定されている SQL Server 2019 に対して、次のクエリで作成したテーブルをサンプルとして使用しています。

CREATE DATABASE [UnicodeTest] COLLATE Japanese_CI_AS
GO

USE [UnicodeTest]
GO

DROP TABLE IF EXISTS T1
GO

CREATE TABLE T1 (
    C1 varchar(100) COLLATE Japanese_CI_AS,
    C2 varchar(100) COLLATE Japanese_XJIS_140_CI_AS_UTF8,
    C3 nvarchar(100)
)
GO

 

SQL Server に送信されるクエリの文字コード

この辺の内容は、以前投稿した内容と同じになります。

上記のサンプルデータのテーブルでは、文字列データ型に対して、次のように複数の照合順序 / コードページが使用されるようになっています。

  • C1 : Japanese_CI_AS : Shift-JIS
  • C2 : Japanese_XJIS_140_CI_AS_UTF8 : UTF-8
  • C3 :  Japanese_CI_AS : UTF-16LE

このようなテーブルに対してデータを投入するクエリを実行すると、SQL Server では、各文字がどのように解釈がされているでしょうか。

INSERT INTO T1 VALUES('あいうえお',N'あいうえお',N'あいうえお')
GO

 

SQL Server との通信を Wireshark でキャプチャしてみるtお、SQL Server に対しては次のようなデータとしてクエリが送信されていることが確認できます。

image

INSERT の VALUES 句に設定されている日本語の文字列がどのように SQL Server に送信されているかというと、次のようになります。

image

あいうえをという文字列を N” で修飾あり、なしで送信しているのですが、すべての文字が

  • 0x4230 : あ
  • 0x4430 : い
  • 0x4630 : う
  • 0x4830 : え
  • 0x4a30 : お

として送信されていることが確認できます。

SQL Server に対して実行するクエリテキストは UNICODESTREAM として送信することになっていますので、クエリテキストはどのような文字 / 照合順序を使用していたとしても、すべてが UTF-16LE でエンコードされた文字列として送信が行われています。

それでは、次のようにデータを投入するとどうなるしょうか?

INSERT INTO T1 VALUES('',N'',N'')
GO

 

「」は、Shift-JIS には存在していない文字であり、サロゲートペアの文字となります。

IME パッドで次のようなコード (UTF-16LE だと、0x42d8 9fdf) が使用されていることが確認できます。IME パッドからも Shift-JIS には存在していない文字であることが確認できますね。

image

N で修飾していても、していなくても、クエリテキストは、テキスト全体を UTF-16LE でエンコードした UNICODESTREAM として送信されますので、VALUES の各文字は、すべて 0x42d8 9fdf として送信が行われています。

image

 

C1 に対応した文字コードは Shift-JIS ではあるのですが、クエリを送信したタイミングでは照合順序の文字コードには影響なく、該当のテキストを UTF-16LE で表したバイトデータとして送信が行われていることになります。

SQL Server で文字列データ型のコードページがサポートしていない文字を挿入しようとすると「?」(既定の置換文字) でデータが格納されますが、? に置換されるのは、SQL Server に対してクエリを送信したタイミングではありません。

クエリを受信した後に SQL Server がデータを格納するタイミングで、各列の照合順序のコードページを基にして、受信したクエリテキストのデータを変換しようとした際に、該当のコードページで対応した文字がない場合は ? に置換され格納されることになります。

 

SQL Server に格納されたデータ

それでは、先ほどのデータがどのように SQL Server に格納されたのかを確認してみます。

SELECT * FROM T1
OUTER APPLY sys.fn_PhysLocCracker(%%physloc%%)

DBCC TRACEON(3604)
DBCC PAGE ('UnicodeTest', 1, 232,3)

image

今回投入したデータは file_id : 1 / page_id : 232 に格納されていますので、それに合わせて、DBCC PAGE のパラメーターを指定しています。

実際に格納されたページのデータが次の画像になります。

image

テーブルの定義としては次のようになっています。

  • C1 : Japanese_CI_AS : Shift-JIS
  • C2 : Japanese_XJIS_140_CI_AS_UTF8 : UTF-8
  • C3 :  Japanese_CI_AS : UTF-16LE

「あいうえお」を各文字コードで表すと次のようになります。

  • Shift-JIS : 0x82a0 82a2 82a4 82a6 82a8
  • UTF-8 : 0xe38182 0xe38184 0xe38186 0xe38188 0xe3818a
  • UTF-16LE : 0x4230 0x4430 0x4630 0x4830 0x4a30

各文字コードで格納されているデータを囲ったものが次の画像となるのですが、それぞれの列に対して、適切なコードページでエンコードされたバイナリデータとして格納されていることが確認できます。

image

「」という文字を格納した場合は、次のようになります。

image

Shift-JIS の varchar については、 に対応した文字が用意されていません。そのため、UTF-16LE で SQL Server が受け取った、の文字を Shift-JIS に変換することができないため置換文字である ? として登録が行われます。今回はサロゲート文字のため、各文字のバイトに対応した変換が行われるため、?? として登録が行われています。

UTF-8 の varchar / UTF-16 の nvarchar については、対応する文字がサポートされていますので、各文字コードの情報で正常に登録が行われています。

 

この事から、

  • SQL Server は、UTF-16LE で受信したクエリテキストの内容を、データ格納時に適切なコードページに変換して格納している
  • 列に指定されている照合順序のコードページが、対応の文字のコードをサポートしていない場合は置換文字である ? で格納

ことが確認できました。

SQL Server の文字化けとして ? として登録がされるというように言われることがありますが、これは「該当の列の照合順序のコードページで対応していない文字については、登録時に置換文字である ? で登録される」という動作となり、SQL Server が ? でデータを受信しているわけではありません。

 

UTF-8 のデータを返す場合 (UTF-8 のサポートが行われていないクライアント)

SQL Server に登録されている UTF-8 の照合順序の文字をクライアントに返す場合の動作を見てみましょう。

最初は、UTF-8 の照合順序が追加される前の古いクライアントライブラリを想定したものとなります。(2018/10/4 にリリースされた Microsoft.Data.SqlClient 4.6.1 を使用)

SELECT TOP 1 * FROM T1 WHERE C2 = N''

実行結果は以下のようになります。

image

C1 については、置換文字で登録されていますので ?? となっており、C2/C3 については登録時に指定したデータが返っていています。

C3 は nvarchar ですので UTF-16LE で帰ってくるはずですが、UTF-8 の varchar についてはどのようなデータが返ってくるでしょうか?

結果としては次のようになります。

image

C2 / C3 のという文字については、nvarchar / varchar (UTF-8) ともに、0x42d8 9fdf というデータとして受け取っていることが確認できます。

これは、UTF-16LE でを表したものとなります。

TDS の可変長データのデータタイプの定義について 2.2.5.4.3 Variable-Length Data Types で確認すると、次のようになっています。

  • VARCHARTYPE : 0x27
  • NVARCHARTYPE : 0xE7

image

四角で囲った部分が C2 / C3 のデータ型を表しているものとなるはずですが、どちらも 0xE7 が設定されていますね。

SQL Server 2019 で UTF-8 の varchar を使用した場合、SQL Server のデータストアとしては、varchar データ型に、UTF-8 の文字コードを使用してデータが格納されていますが、SQL Server からクライアントにデータを返す時には、nvarchar 相当のデータとして返しています。

UTF-8 の varchar のデータを、nvarchar のデータに変換してクライアントに返すことで、nvarchar のデータを解釈できるクライアントであれば、SQL Server 2019 リリース以前のクライアントからも、UTF-8 の varchar で格納されているデータを取り扱うことができ、古いクライアントでも正常にデータを取得することが可能となります。

変換のオーバーヘッドはありそうですが、広い範囲のクライアントに対応するためには、このような方法をとらざるを得ないのでしょうね。

 

データの投入方法

古いクライアントライブラリで UTF-8 の文字列の挿入等を行う場合も同様で、varchar ではなく、nvarchar として処理を行う必要があるようです。(UTF-8 の varcahr に SqlDbType.VarChar でデータを投入しようとすると「Invalid data for UTF8-encoded characters」になっていました)

 

UTF-8 のデータを返す場合 (UTF-8 のサポートが行われているクライアント)

UT-8 のサポート状況ついては次のドキュメントから確認できそうです。

.NET Framework 4.7.2 / .NET Core 2.2 の System.Data.SqlClient / Microsoft.Data.SqlClient 1.0 以降については、UTF-8 のサポートが含まれているようです。

UTF-8 のサポートで、アプリケーション コードを変更する必要はありません。 これらの SqlClient の変更によって、サーバーで UTF-8 がサポートされ、基になる列の照合順序が UTF-8 である場合に、クライアントとサーバーの間の通信が最適化されます。 SQL Server 2019 の新機能の UTF-8 のセクションを参照してください。

クライアントとサーバーの通信が最適化されているという対応がポイントになりそうな気がしています。(どのような変更が行われたのかの厳密な確認はしていませんが…)

今回はテスト用の文字としては「ABあCD」という文字を使用しており、「あ」の文字コードは次のようになっています。

image

 

SQL Server 2019 の UTF-8 に対応していないライブラリとなる System.Data.SqlClient の 4.6.1 で UTF-8 の文字列を取得した場合、パケットとしては次のようになっています。

image

C1 は varchar / Japanese_XJIS_140_CI_AS_UTF8 となっているのですが、自動的に nvarchar に変換された結果が取得されており、「あ」という文字については UTF-16 の「0x3042」として取得されています。

 

それでは、UTF-8 に対応している System.Data.SqlClient を 4.7.0 に変更して実行してみます。

今回は nvarchar の変換は発生せず、varchar として処理が行われており、「あ」については「0xE38182」の UTF-8 として取得されています。

image

SQL Server 上も文字列として 「0xE38182」として格納がされているため、UTF-8 の変換が発生しないのであれば、そちらのほうがパフォーマンスは良いかと思います。

クライアントライブラリもできるだけ最新のものを使用し、UTF-8 のデータはそのまま受け取れるようにしておいたほうが良いのかと。

データの投入方法

UTF-8 対応ライブラリの場合、UTF8 対応の varchar に対してデータを投入する場合は、SqlDbType.VarChar でデータを投入することができるため、通常の方法と同等で操作を行えば問題はなさそうです。

 

まとめ

SQL Server の基本的なデータ操作としては、次のようになっているようです。

  • SQL Server へのクエリテキストの送信は UTF-16LE でエンコードされたバイナリデータで送信される
  • SQL Server へのデータ格納ついては、受信したクエリテキストを列の照合順序に応じた文字コードに変換して格納を行う
    • 格納時に変換ができない文字については ? という置換文字で登録
    • N” で修飾していない場合、該当の文字を照合順序の文字コードのデータとして認識
    • N” で修飾している場合、該当の文字を Unicode のデータとして認識
  • 古いクライアントライブラリ向けの挙動としては、varchar (UTF-8) のデータ型を操作する場合は、nvarchar に変換してデータを取り扱う
    • これにより SQL Server 2019 リリースより前のクライアントでも varchar (UTF-8) で格納されているデータを正しく取り扱うことができる
  • UTF-8 をサポートしたクライアントライブラリ向けの挙動としては、UTF-8 で変換せずに文字データが返される。登録する際には varchar で操作を行うことで UTF-8 として取り扱われる。

ネットワークキャプチャと DBCC PAGE を組み合わせて使用すると、SQL Server がどのようにクエリ / データを解釈しているのかの理解を深めることができるのではないでしょうか。

Share

Written by Masayuki.Ozawa

12月 31st, 2021 at 9:01 pm

Posted in SQL Server

Tagged with

Leave a Reply