varchar(4)からnvarchar(4)に変更した場合、適切に照合順序を変えるには

from 検証: varcharからnvarcharへ型変更する手順

途中まで

varchar(4)のテーブルを作成

code:sql

CREATE TABLE #T (vc varchar(4));

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

table:result

column_name type character_set collation max_len octet_len

vc varchar cp932 Japanese_CI_AS 4 4

INSERT

code:sql

INSERT INTO #T(vc) VALUES('𩸽');

INSERT INTO #T(vc) VALUES('愛');

INSERT INTO #T(vc) VALUES('a愛');

INSERT INTO #T(vc) VALUES('aa愛');

-- INSERT INTO #T(vc) VALUES('a愛愛'); -- これはダメ

SELECT vc

, LEN(vc) AS length, DATALENGTH(vc) AS data_length

, CAST(vc AS varbinary(max)) AS bin

FROM #T;

table:result

vc length data_length bin

?? 2 2 0x3F3F

愛 1 2 0x88A4

a愛 2 3 0x6188A4

aa愛 3 4 0x616188A4

nvarchar(4)に変更

code:sql

ALTER TABLE #T ALTER COLUMN vc nvarchar(4);

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

table:result

column_name type character_set collation max_len octet_len

vc nvarchar UNICODE Japanese_CI_AS 4 8

INSERT

code:sql

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

INSERT INTO #T(vc) VALUES(N'a𩸽');

INSERT INTO #T(vc) VALUES(N'aa𩸽');

INSERT INTO #T(vc) VALUES(N'a愛𩸽'); -- これ入るのは違和感がある

SELECT vc

, LEN(vc) AS length, DATALENGTH(vc) AS data_length

, CAST(vc AS varbinary(max)) AS bin

FROM #T;

table:result

vc length data_length bin

?? 2 4 0x3F003F00

愛 1 2 0x1B61

a愛 2 4 0x61001B61

aa愛 3 6 0x610061001B61

𩸽 2 4 0x67D83DDE

a𩸽 3 6 0x610067D83DDE

aa𩸽 4 8 0x6100610067D83DDE

a愛𩸽 4 8 0x61001B6167D83DDE

照合順序変更

code:sql

ALTER TABLE #T ALTER COLUMN vc nvarchar(4) COLLATE Japanese_Bushu_Kakusu_100_CI_AS_SC;

後片付け

code:sql

DROP TABLE #T;