LEN and DATALENGTH of VARCHAR and NVARCHAR

Question

After reading "What is the difference between char, nchar, varchar, and nvarchar in SQL Server?" I have a question.

I'm using MS SQL Server 2008 R2

DECLARE @T TABLE
(
C1 VARCHAR(20)  COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS,
C2 NVARCHAR(20) COLLATE Chinese_Traditional_Stroke_Order_100_CS_AS_KS_WS
)

INSERT INTO @T VALUES (N'中华人民共和国',N'中华人民共和国')

SELECT LEN(C1)        AS [LEN(C1)],
       DATALENGTH(C1) AS [DATALENGTH(C1)],
       LEN(C2)        AS [LEN(C2)],
       DATALENGTH(C2) AS [DATALENGTH(C2)]
FROM   @T

Returns

LEN(C1)     DATALENGTH(C1) LEN(C2)     DATALENGTH(C2)
----------- -------------- ----------- --------------
7           12             7           14

Why the second DATALENGTH(C1) is 12?

Is this something to do with the Chinese characters? try doing it on normal characters, I guess it works well. — Nadeem_MK, Jun 05 '15 at 10:02

score 2 · Answer 1 · edited May 23 '17 at 11:58

In your INSERT you are converting text from unicode to chinese codepage for C1. Most likely this process alters the text and something may be lost.

Here is SQL Fiddle.

You can see that the second character 华 is stored as 3F in varchar. You can also see that the last character 国 is also stored as 3F in varchar. 3F is a code for ?. When Windows tries to convert text from unicode to the codepage and certain character can't be represented in the given codepage, the conversion function (most likely WideCharToMultiByte) puts ? for such characters .

One more example. The last, but one character 和 is encoded as A94D in varchar and 8C54 in nvarchar. If you look it up in Character Map it will show these codes (unicode and codepage):

character map

LEN and DATALENGTH of VARCHAR and NVARCHAR

1 Answers1