t-sqlunicodechardatalength

T-SQL surprising DATALENGTH values of char and nchar

SELECT DATALENGTH('źźźź')                       -- 4
SELECT DATALENGTH(CONVERT(char, 'źźźź'))        -- 30
SELECT DATALENGTH(CONVERT(nchar, 'źźźź'))       -- 60
SELECT DATALENGTH(CONVERT(varchar, 'źźźź'))     -- 4
SELECT DATALENGTH(CONVERT(nvarchar, 'źźźź'))    -- 8

I know that char is non-Unicode type, but nchar actually IS a unicode type

Solution

  • Yes - and what's your question?

    If you don't define a length in a CAST or CONVERT, then 30 characters is the system-default.

    So this

    SELECT DATALENGTH(CONVERT(char, 'źźźź'))

    is equivalent to

    SELECT DATALENGTH(CONVERT(char(30), 'źźźź'))

    and since the CHAR (and NCHAR) datatypes are always padded to their defined length, you get 30 characters and thus 30 (char) and 60 (nchar) bytes length.

    All perfectly clear and well documented - see MSDN documentation on CAST and CONVERT

    length

    Is an optional integer that specifies the length of the target data type. The default value is 30.

    When you're using the variable length strings instead varchar or nvarchar, you only get as many characters stored as there are really in the string - therefore you get 4 characters and thus 4 and 8 bytes of length.