nvarcharの列の照合順序を変えて文字数をカウントしてみる

from SQL Serverの文字列型varchar nvarchar

環境

code:select.sql

select @@VERSION;

table:result

(列名なし)

Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) (略)

確認用テーブルの準備

code:create.sql

CREATE TABLE #T (

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)

);

kakusu_nvcの照合順序を変える

code:alter.sql

ALTER TABLE #T ALTER COLUMN altvc nvarchar(max);

ALTER TABLE #T ALTER COLUMN kakusu_nvc nvarchar(max) COLLATE Japanese_Bushu_Kakusu_100_CI_AS_SC;

nvarcharで列の照合順序に_SC(補助文字)を追加

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 = 'kakusu_nvc';

table:result

column_name type character_set collation max_len octet_len

kakusu_nvc nvarchar UNICODE Japanese_Bushu_Kakusu_100_CI_AS_SC 3 6

code:truncate_and_select.sql

TRUNCATE TABLE #T;

INSERT INTO #T(kakusu_nvc) VALUES (N'a');

INSERT INTO #T(kakusu_nvc) VALUES (N'愛');

INSERT INTO #T(kakusu_nvc) VALUES (N'𩸽');

SELECT kakusu_nvc, LEN(kakusu_nvc) AS length, DATALENGTH(kakusu_nvc) AS data_length,

CAST(kakusu_nvc AS varbinary(max)) AS bin FROM #T;

table:result

kakusu_nvc length data_length bin

a 1 2 0x6100

愛 1 2 0x1B61

𩸽 1 4 0x67D83DDE

𩸽が1文字の4バイトになった

未使用

code:drop.sql

-- 一時テーブルなのでログアウトしたら消えるが明示的に消したいとき用途

DROP TABLE tempdb.#T;