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

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?

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).

StackExchange Database Administrators Q#323519, answer score: 6