2

Given a variable nvarchar(max), the input is 'aaaaa...' with 16000 length. The value of the variable has no problem with this setup.

If I break down the input into 3 smaller ones let's say (7964,4594,3442) the variable truncates the concatenation of them.

On the other hand, if at least 1 variable is over 8000 size, the concatenation works without an issue.

Is there any documentation regarding the mentioned behavior?

Emka
  • 340
  • 6
  • 16

2 Answers2

4

Taken from the docs:

If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated. However, if at least one of the strings concatenated is a large value type, truncation does not occur.

Operations between varchar and nvarchar are limited to 8000 and 4000 characters respectively, unless you treat any of the involved data types as MAX. Please be very cautious with the order of the operations, this is a very good example from the docs:

DECLARE @x varchar(8000) = replicate('x', 8000)
DECLARE @y varchar(max) = replicate('y', 8000)
DECLARE @z varchar(8000) = replicate('z',8000)

SET @y = @x + @z + @y

-- The result of following select is 16000
SELECT len(@y) AS y

The result is 16k and not 24k because the first operation is @x + @z which is truncated at 8000 because neither of them are MAX. Then the result is concatenated to a type that is MAX, thus breaking the restriction of 8000 as limit, which adds another 8000 characters from @y. In the result, the characters from variable @z are lost at the first concatenation.

EzLo
  • 13,780
  • 10
  • 33
  • 38
  • Thank you very much for your answer!!! I did not know about the exception of Large Objects. – Emka Jan 23 '19 at 13:04
2

If your using CONCAT function

If none of the input arguments has a supported large object (LOB) type, then the return type truncates to 8000 characters in length, regardless of the return type. This truncation preserves space and supports plan generation efficiency.

try 

CONCAT(CAST('' as VARCHAR(MAX)),@var1,@var2)

or 

CAST(@var1 as VARCHAR(MAX)) + @var2
Stanislav Kundii
  • 2,874
  • 1
  • 11
  • 17
  • Thank you very much for your answer! – Emka Jan 23 '19 at 13:03
  • Tip: It's good practice to include a link to the documentation (or other examples) that you're citing, e.g. [Concat](https://learn.microsoft.com/en-us/sql/t-sql/functions/concat-transact-sql?view=sql-server-2017#remarks). It makes it easier for readers to learn more and avoids anyone claiming that you are pretending to be the author of the cited material. – HABO Jan 23 '19 at 14:16