SE の雑記

MySQL の場合は、10.1.4 接続文字セットおよび照合順序 のような設定で、クライアント / サーバー間のデータ通信 (クエリ / 結果) で使用される文字コードを設定できるかと思います。

SQL Server の場合は、サーバーに文字データ格納時の文字コード (コードページ) / 文字列を比較する場合のルールを「照合順序」で設定することができますが、クライアント / サーバー間のデータ通信で使用される文字コードについては、明示的な指定を行うことはできません。

SQL Server ではクライアント / サーバー間の通信で使用されるデータではどのような文字コードが使用されているのかについて、まとめてみたいと思います。

本投稿のような内容を調べる場合には、クエリ実行時のパケットをキャプチャして、SQL Server のプロトコルのドキュメント に記載した TDS の使用を確認することになるかと思います。

Windows の OS としての文字コードの扱いについては、Unicode と文字セットについて を参照してください。

クライアントから送信されるデータ (SQL) で使用される文字コード

クライアントからサーバーに送信されるデータは SQL となります。
最初に、TDS の仕様としてどのようになっているのかをドキュメントから確認してみます。

クライアントからサーバーに送信される SQL については、バッチが基本単位となりますので、2.2.6.7 SQLBatch を確認てみましょう。

SQL のテキストについては「UNICODESTREAM」としてサーバーに送信される仕様となっています。クライアントから送信する SQL のテキストについては、Unicode を使用するということでプロトコルで規定されていますね。

実際にどのようなメッセージが送信されているのかについては、プロトコルのサンプルに記載があり、4.6 SQL Batch Client Request から確認できます。

それでは、このメッセージをデコードしてみたいと思います。

デコードには、次のようなスクリプトを使用します。

 [byte[]]$b = @(0x0d,0x0) Write-Host ("ASCII : {0}" -f [System.Text.Encoding]::ASCII.GetString($b)) Write-Host ("UTF-16LE : {0}" -f [System.Text.Encoding]::Unicode.GetString($b)) Write-Host ("UTF-16BE : {0}" -f [System.Text.Encoding]::BigEndianUnicode.GetString($b)) Write-Host ("UTF-8 : {0}" -f [System.Text.Encoding]::UTF8.GetString($b)) Write-Host ("CP932 : {0}" -f [System.Text.Encoding]::GetEncoding(932).GetString($b)) 

$b に、デコードするバイト配列を指定しますので、上記の場合は、次のようになります。

実行結果がこちらになります。UTF-16BE 以外は文字化けしていますね。

ドキュメントのサンプルについては「select ‘foo’ as ‘bar」という、2 バイト文字が含まれていないクエリになっています。

Unicode どのような文字コードになっているかがこれでは確認ができませんので、次のクエリを実行してみます。

この時のネットワークのパケットが次のようになりますので、ここから SQLText の UNICODESTREAM をスクリプトに設定して実行してみます。

スクリプトで出力した結果が以下になります。

UTF-16LE の場合のみ、正常にデコードできていますね。

クライアントから送信される SQL については、UTF-16LE でエンコードした文字列を送信するのがプロトコルレベルで規定されていることが確認できました。

サーバーから送信されるデータ (結果セット) で使用される文字コード

クライアントからサーバーに送信されるデータ (SQL) については、UTF-16LE でエンコードされていることが確認できましたので、クエリの実行結果で使用される文字コードについても確認してみます。

SQL Server のデータ通信には、TDS が使用されており、TDS は Tabluar Data Stream の略となり、表形式のデータストリームとして、データが受信されます。

この表形式データストリームには、「サーバーから送信された表形式データの列情報」についても含まれています。

列情報については、2.2.7.4 COLMETADATA に記載されています。

列情報の中には、列名のほかに「TYPE_INFO」として、データ型の情報が含まれています。

TYPE_INFO については、2.2.5.6 Type Info Rule Definition に記載されていますので、こちらの情報も確認をします。

文字列データ型の場合、COLLATION が含まれていることが確認できますね。

Collation については、2.2.5.1.2 Collation Rule Definition に記載されています。

サーバーからクライアントに返される結果セットには、データ型の情報が含まれており、データ型の情報については Collation (照合順序) の情報も含まれています。

左が varchar / 右が、nvarchar のデータを取得した場合の COLMETADATA の情報となります。

データ型のほかに、照合順序のコードページも含まれていますので、列をどのようなコードページでデコードすればよいかが、サーバーからの結果 (レスポンス) に含まれていることが確認できますね。

ちなみに UTF-8 の照合順序を使用した varchar については、NVarChar として返されているようでした。

SQL Server は文字列を使用している列単位で照合順序を設定できるため、列によって異なるコードページを指定することができますが、サーバーからのレスポンス内にデータ型にコードページの情報も含まれているため、取得したデータをどのようにデコードすればよいかは、レスポンスから判断することができます。

まとめ

SQL Server はクライアントとサーバー間で通信されるデータの文字コードは、

• クライアントからの送信 : UTF-16LE
• サーバーからの送信 : レスポンス内のデータ型が含まれる照合順序の情報により、どのように文字をデコードすればよいかを判断

というようプロトコルで規定されており、設定によって制御を行うものではないということになるのかと。