Why is the length of a value sometimes the width of the column instead of the length of the string?

Question

Consider the following SQL

DECLARE @JSON VARCHAR(max);
DECLARE @t AS TABLE(
    field  char(32),
    len1   int,
    nfield nchar(32),
    nlen1  int,
    vfield varchar(32),
    vlen1  int
);

SET @JSON = '[
    { "Field" : "abcd" }
]'
INSERT INTO @t
SELECT
    Field      as field,
    Len(Field) as len1,
    Field      as nfield,
    Len(Field) as nlen1,
    Field      as vfield,
    Len(Field) as vlen1
FROM OPENJSON (@JSON)
WITH ( Field nchar(32) );

INSERT INTO @t ( field , len1       ,nfield, nlen1      ,vfield, vlen1      )
VALUES         ( 'efgh', len('efgh'),'efgh', len('efgh'),'efgh', len('efgh'))

SELECT
    field,
    len1,
    len(field)  as len2,
    nlen1,
    len(nfield) as nlen2,
    vlen1,
    len(vfield) as vlen2
FROM @t;

which outputs

field  len1  len2  nlen1  nlen2  vlen1  vlen2
------ ----- ----- ------ ------ ------ ------
abcd   4     4     4      32     4      4
efgh   4     4     4      4      4      4

The length of the value originating from JSON in the nchar(32) field is 32 while all others are the length of the string. This was a surprise to me so I'm looking for an underlying explanation.

What is happening that this one case of the 12 has a different result?

Answer 1

Much simpler repro:

DECLARE @t TABLE(f1 char(32), f2 nchar(32));

INSERT @t SELECT f AS f1, f AS f2 FROM OPENJSON('[
    { "f" : "abcd" }
]') WITH (f nchar(32));

SELECT f1, f2, f1l = LEN(f1), f2l = LEN(f2) FROM @t;

Output:

f1	f2	f1l	f2l
abcd	abcd	4	32

I don't quite know what's going on here, but Hannah may be onto something about data type / implicit conversion while data is being pulled from JSON and inserted into the table, and going through who knows how many translations in the inner bowels of JSON-land.

I did notice that when I ran this query in Azure Data Studio, the grid output looked the opposite of what I would have expected, e.g. with a length of 4 I expected the left column to be skinny, and the right column to be wide:

Of course LEN() doesn't really tell you how wide a string is when there are trailing spaces, since they are ignored.

If I copy the contents of those cells, it is a bit clearer what's going on (just not why). On the left, there are actually 32 characters. So why doesn't it do that on the right? Well, that string is padded with something that is definitely not spaces:

Other notes:

• DATALENGTH() works as expected (32 on the left, 64 on the right).

• Passing it through metadata discovery functions didn't yield anything funny or give any clues about the source of those non-printing characters:

    SELECT name, system_type_name
    FROM sys.dm_exec_describe_first_result_set
    (N'DECLARE @t TABLE(f1 char(32), f2 nchar(32));
  
    INSERT @t SELECT f AS f1, f AS f2 FROM OPENJSON(''[
        { "f" : "abcd" }
    ]'') WITH (f nchar(32));
  
    SELECT f1, f2 FROM @t;', NULL, 0);
  

  name	system_type_name
  f1	char(32)
  f2	nchar(32)

So then I went back and decided to check the right-most character of each string, and sure enough, in one case it's a space (char(32)), and in the other it's a terminator (nchar(0)):

DECLARE @t TABLE(f1 char(32), f2 nchar(32));

INSERT @t SELECT f AS f1, f AS f2 FROM OPENJSON('[
    { "f" : "abcd" }
]') WITH (f nchar(32));

SELECT f1, f2, ASCII(RIGHT(f1,1)),UNICODE(RIGHT(f2,1)) FROM @t;

f1	f2	-	-
abcd	abcd	32	0

So, I don't know why JSON is producing this artifact, but you can work around it using DATALENGTH instead of LEN, or perhaps by using nvarchar instead of nchar (which is arguably a better choice for strings that will obviously vary in length anyway).

Answer 2

Example

DECLARE @t table (f nchar(5) NOT NULL);

INSERT @t (f)
SELECT OJ.f 
FROM OPENJSON(N'[{ "f" : "abcd" }]')
    WITH (f nchar(5)) AS OJ;

SELECT 
    T.f,
    len_f = LEN(T.f),
    dl_f = DATALENGTH(T.f),
    bin_f = CONVERT(varbinary(10), f) 
FROM @t AS T;

f	len_f	dl_f	bin_f
abcd	5	10	0x61006200630064000000

Cause

When the data type of the JSON element exactly matches the target table, SQL Server incorrectly assumes no padding is needed.

In the example above, the declared type is nchar(5) but the source string is only 4 characters. Despite the type match, the string should still be padded with a space (ANSI_PADDING is always on for nchar).

The fact it appears padded with zeroes is likely due to buffer overflow protection and initializing the buffer with zeroes. It could also conceivably be erroneous padding as would be appropriate for binary values.

In any case, it is a product defect.

Workarounds

Either ensure the strings in the JSON match the declared type without the need for padding or cast the returned column to a slightly larger type so SQL Server applies the usual padding and truncation rules.

DECLARE @t table (f nchar(5) NOT NULL);

INSERT @t (f)
SELECT CONVERT(nchar(6), OJ.f) -- Workaround
FROM OPENJSON(N'[{ "f" : "abcd" }]')
    WITH (f nchar(5)) AS OJ;

SELECT 
    T.f,
    len_f = LEN(T.f),
    dl_f = DATALENGTH(T.f),
    bin_f = CONVERT(varbinary(10), f) 
FROM @t AS T;

f	len_f	dl_f	bin_f
abcd	4	10	0x61006200630064002000

You could also use nvarchar(5) in the WITH clause.