LoginSignup
6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@NT_ATin株式会社NEXT TECHNOLOGY

【備忘録】照合順序とNプレフィックス【SQLServer】

Last updated at Posted at 2025-03-19

はじめに

業務でSQLServerを使う機会があり、そこで全角文字が「?」になるという問題にものすっっっごく悩まされまして…
忘れないようにちょっとした実験結果を添えて備忘録として残したいと思います。

（ざっくりと）照合順序とは？

照合順序では、データセット内の各文字を表すビット パターンが指定されます。 また、照合順序はデータの並べ替えおよび比較を行うための規則を決定します。 SQL Server では、単一のデータベース内で異なる照合順序を持つオブジェクトを格納できます。 非 Unicode 列の場合は、照合順序の設定によってデータのコード ページと表示可能な文字が指定されます。 非 Unicode 列の間でデータを移動する場合は、移動元のコード ページから移動先のコード ページに変換する必要があります。
引用：照合順序と Unicode のサポート
https://learn.microsoft.com/ja-jp/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#collation

『コードページ（文字コード）は何を使うか』や『「あ」と「ア」の並び替えをしたときどちらが大きいか』、『比較の際にアルファベットの大文字小文字を区別するか』などの規則のことを照合順序といいます。
SQLServerではこの照合順序をインスタンスの生成時に必ず設定し、任意でテーブルのカラムに設定することができます。（カラムで照合順序の指定を行わない場合はインスタンスの生成時に指定した照合順序が設定されます。）

設定した照合順序でどのコードページが使われているかは以下のSQLで確認できます。

照合順序の文字コード確認.sql
SELECT
    name, 
    COLLATIONPROPERTY(name, 'codepage') AS codepage
FROM
    sys.fn_helpcollations() 
WHERE
    name IN ('Japanese_CI_AS','SQL_Latin1_General_CP1_CI_AS')

image.png

コードページがどの文字コードに対応しているかは以下リンクで確認できます。
https://learn.microsoft.com/en-us/windows/win32/intl/code-page-identifiers

（ざっくりと）Nプレフィックスとは？

Unicode 文字の文字列定数には、プレフィックスとして文字 N を付けて UCS-2 または UTF-16 の入力を通知します。これは SC 照合順序が使用されているか使用されていないかによって異なります。 N プレフィックスがない場合、文字列は、特定の文字を認識できない可能性があるデータベースの既定のコード ページに変換されます。 SQL Server 2019 (15.x) 以降のバージョンでは、UTF-8 対応照合順序が使用されている場合、既定のコード ページには Unicode UTF-8 文字セットを格納できます。
引用：nchar および nvarchar (Transact-SQL)
https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?redirectedfrom=MSDN&view=sql-server-ver16#remarks

INSERTやUPDATEの際、「N'あいうえお'」のような感じに値の頭に「N」を付けることがあります。
この「N」のことをNプレフィックスといい、「これはUnicodeの文字列ですよ」をSQLServerに伝えるために必要なものとなります。
（Nは「National Language」のNだそうです。）
https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/constants-transact-sql?view=sql-server-ver16#unicode-strings

実験

どのような時に登録した文字が「?」になってしまうのかを確認するために以下の実験を行ってみました。

  • 日本語環境のデフォルト照合順序「Japanese_CI_AS」と英語環境のデフォルト照合順序「SQL_Latin1_General_CP1_CI_AS」のサーバーを用意する。

  • 用意したサーバーにそれぞれ

    1. 照合順序の指定を行わない「CHAR」「VARCHAR」「TEXT」「NCHAR」「NVARCHAR」「NTEXT」を持つテーブル
    2. 照合順序で「Japanese_CI_AS」を指定した「CHAR」「VARCHAR」「TEXT」「NCHAR」「NVARCHAR」「NTEXT」を持つテーブル
    3. 照合順序で「SQL_Latin1_General_CP1_CI_AS」を指定した「CHAR」「VARCHAR」「TEXT」「NCHAR」「NVARCHAR」「NTEXT」を持つテーブル

の３テーブルを作成する。

  • 以下の方法でNプレフィックスを付けたデータとつけていないデータの登録を行う。（環境依存文字はなし）
    1. エディタから登録
    2. エディタからUTF8で保存したファイルを読み込んで登録
    3. エディタからSJISで保存したファイルを読み込んで登録
    4. sqlcmdから登録（UTF8指定）
    5. sqlcmdから登録
    6. sqlcmdでUTF8保存したファイルを読み込んで登録
    7. sqlcmdでSJIS保存したファイルを読み込んで登録

実験１：サーバーの照合順序が「Japanese_CI_AS」の場合

image.png

0. 準備

CREATE_TABLE.sql
CREATE TABLE NSTR_CHK( 
    NO INT
    , CHAR_COLUMN CHAR (100)
    , VARCHAR_COLUMN VARCHAR (100)
    , TEXT_COLUMN TEXT
    , NCHAR_COLUMN NCHAR (100)
    , NVARCHAR_COLUMN NVARCHAR(100)
    , NTEXT_COLUMN NTEXT
);
CREATE TABLE NSTR_CHK_Japanese_CI_AS( 
    NO INT
    , CHAR_COLUMN CHAR (100) COLLATE Japanese_CI_AS
    , VARCHAR_COLUMN VARCHAR (100) COLLATE Japanese_CI_AS
    , TEXT_COLUMN TEXT COLLATE Japanese_CI_AS
    , NCHAR_COLUMN NCHAR (100) COLLATE Japanese_CI_AS
    , NVARCHAR_COLUMN NVARCHAR(100) COLLATE Japanese_CI_AS
    , NTEXT_COLUMN NTEXT COLLATE Japanese_CI_AS
);
CREATE TABLE NSTR_CHK_SQL_Latin1_General_CP1_CI_AS( 
    NO INT
    , CHAR_COLUMN CHAR (100) COLLATE SQL_Latin1_General_CP1_CI_AS
    , VARCHAR_COLUMN VARCHAR (100) COLLATE SQL_Latin1_General_CP1_CI_AS
    , TEXT_COLUMN TEXT COLLATE SQL_Latin1_General_CP1_CI_AS
    , NCHAR_COLUMN NCHAR (100) COLLATE SQL_Latin1_General_CP1_CI_AS
    , NVARCHAR_COLUMN NVARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS
    , NTEXT_COLUMN NTEXT COLLATE SQL_Latin1_General_CP1_CI_AS
);

1. エディタから登録

INS_DATA1.sql
INSERT INTO NSTR_CHK VALUES (1,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK VALUES (2,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (1,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (2,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (1,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (2,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');

image.png
image.png

2. エディタからUTF8で保存したファイルを読み込んで登録

image.png
image.png
image.png

3. エディタからSJISで保存したファイルを読み込んで登録

image.png
image.png
image.png

4. sqlcmdから登録（UTF8指定）

INS_DATA4.sql
INSERT INTO NSTR_CHK VALUES (7,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK VALUES (8,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (7,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (8,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (7,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (8,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');

image.png
image.png

5. sqlcmdから登録

INS_DATA5.sql
INSERT INTO NSTR_CHK VALUES (9,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK VALUES (10,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (9,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (10,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (9,'備忘録','備忘録','備忘録','備忘録','備忘録','備忘録');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (10,N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録',N'備忘録');

image.png
image.png

6. sqlcmdでUTF8保存したファイルを読み込んで登録

image.png
image.png
image.png

7. sqlcmdでSJIS保存したファイルを読み込んで登録

image.png
image.png
image.png

実験２：サーバーの照合順序が「SQL_Latin1_General_CP1_CI_AS」の場合

image.png

0. 準備

SQLは実験１で使ったものを使用

1. エディタから登録

SQLは実験１で使ったものを使用
image.png
image.png

2. エディタからUTF8で保存したファイルを読み込んで登録

SQLは実験１で使ったものを使用
image.png
image.png

3. エディタからSJISで保存したファイルを読み込んで登録

SQLは実験１で使ったものを使用
image.png
image.png

4. sqlcmdから登録（UTF8指定）

SQLは実験１で使ったものを使用
image.png
image.png

5. sqlcmdから登録

SQLは実験１で使ったものを使用
image.png
image.png

6. sqlcmdでUTF8保存したファイルを読み込んで登録

SQLは実験１で使ったものを使用
image.png
image.png

7. sqlcmdでSJIS保存したファイルを読み込んで登録

SQLは実験１で使ったものを使用
image.png
image.png

結果

  • どこでどのようにSQLを実行するかによって結果が左右されることはない
  • サーバーに設定されている照合順序で暗黙変換が行われた場合、サポートされている文字であれば「?」にならない
    →逆にサポートされていない場合は「?」になってしまうため、Nプレフィックスを付ける必要がある
  • カラムに設定されている照合順序が全角文字をサポートしていなければNプレフィックスを付けて送っても「?」になる

補足

サポートされていない場合は「?」になるため、実験では「?」になっていなかった「Japanese_CI_AS」でも、環境依存文字などが来た場合は以下キャプチャように「?」になってしまいます。
「NCHAR」「NVARCHAR」「NTEXT」のカラムに登録した文字が「?」になっていないのは、照合順序に何が設定されていてもUnicodeで保存されるためです。
https://learn.microsoft.com/ja-jp/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?view=sql-server-ver16
https://learn.microsoft.com/ja-jp/sql/relational-databases/collations/collation-and-unicode-support?view=sql-server-ver16#Supplementary_Characters

INS_DATA8.sql
INSERT INTO NSTR_CHK VALUES (15,'拉麵','拉麵','拉麵','拉麵','拉麵','拉麵');
INSERT INTO NSTR_CHK VALUES (16,N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (15,'拉麵','拉麵','拉麵','拉麵','拉麵','拉麵');
INSERT INTO NSTR_CHK_Japanese_CI_AS VALUES (16,N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (15,'拉麵','拉麵','拉麵','拉麵','拉麵','拉麵');
INSERT INTO NSTR_CHK_SQL_Latin1_General_CP1_CI_AS VALUES (16,N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵',N'拉麵');

image.png
image.png

おわりに

カラムに設定している照合順序だけでなく、サーバーに設定している照合順序にも影響受けるのですね…
インスタンス作成時の照合順序をしっかりと合わせておかないと、ローカル環境ではうまくいっているのに検証環境では「?」になっている…なんてことが起きそうで怖いですね（）

SQLServerで漢字やら特殊な文字やら取り扱うときには注意しましょう。

それでは、ここまでご覧いただきありがとうございました！

6
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
6
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?