nvarcharの列にUnicode文字をINSERTしてみる
環境
code:select.sql
select @@VERSION;
table:result
(列名なし)
確認用テーブルの準備
code:create.sql
vc varchar (max)
, nvc nvarchar(max)
-- varcharからnvarcharに変えてみる
, altvc varchar (max)
-- 照合順序をJapanese_Bushu_Kakusu_100_CI_AS_SCに設定してみる
, kakusu_nvc nvarchar(max)
, text nvarchar(max)
);
nvarcharの確認
カラムの情報
code:select_INFORMATION_SCHEMA.sql
SELECT
COLUMN_NAME AS column_name,
DATA_TYPE AS type,
CHARACTER_SET_NAME AS character_set,
COLLATION_NAME AS collation,
CHARACTER_MAXIMUM_LENGTH AS max_len,
CHARACTER_OCTET_LENGTH AS octet_len
FROM
tempdb.INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME = 'nvc';
table:result
column_name type character_set collation max_len octet_len
nvc nvarchar UNICODE Japanese_CI_AS 3 6 文字をINSERTしてみる
code:truncate_and_select.sql
SELECT nvc, LEN(nvc) AS length, DATALENGTH(nvc) AS data_length,
CAST(nvc AS varbinary(max)) AS bin FROM #T; table:result
nvc length data_length bin
a 1 2 0x6100
愛 1 2 0x1B61
?? 2 4 0x3F003F00
code:truncate_and_select.sql
SELECT nvc, LEN(nvc) AS length, DATALENGTH(nvc) AS data_length,
CAST(nvc AS varbinary(max)) AS bin FROM #T; table:result
nvc length data_length bin
a 1 2 0x6100
4バイトだが2文字扱いになっている
未使用
code:drop.sql
-- 一時テーブルなのでログアウトしたら消えるが明示的に消したいとき用途
DROP TABLE tempdb.#T;