Post reply

What's going on with nvarchar(max)?

  • November 23, 2011 at 9:15 pm

    #263387

    DECLARE @V1 NVARCHAR(MAX),@V2 NVARCHAR(MAX)

    SET @V2 = N'test'

    --Method 1

    SET @V1 = @V2 + REPLICATE('B',5000)

    PRINT LEN(@V1)

    --Method 2

    SET @V1 = REPLICATE('B',5000)

    SET @V1 = @V2 + @V1

    PRINT LEN(@V1)

    I tried to run the above code. Method1 gave result of 4004 where as method2 gave result of 5004:w00t:. I tried the same code on SQL2005/2008/2008R2 all with the same behaviours. I couldn't understand why? Can someone please explain this? Thanks.

  • November 23, 2011 at 10:06 pm

    #1412546

    Looks like either define @V2 as VARCHAR(MAX) or convert it to VARCHAR(MAX) in the concatination will give the correct results, but not NVARCHAR(MAX). Still don't know why though.

  • November 23, 2011 at 11:49 pm

    #1412562

    In Method 1 the result type of the REPLICATE function will be implicitly VARCHAR(5000) because the input expression is VARCHAR.

    Because it is concatenated with an NVARCHAR(MAX) expression this

    VARCHAR(5000) expression will first be implicitly converted to NVARCHAR(4000) (8000 bytes, the maximum byte length for non-MAX data types)

    before concatenation. If you cast the input constant 'B' to NVARCHAR(MAX) explicitly, the REPLICATE will return an result of type

    NVARCHAR(MAX) which will not be truncated before concatenation.

    In Method 2 the result type of the REPLICATE function will also be VARCHAR(5000). But because it is assigned to an NVARCHAR(MAX) variable

    it will be implicitly converted to NVARCHAR(MAX) and not truncated.

  • November 24, 2011 at 7:16 am

    #1412697

    Thanks Peter.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply