String concatenation truncated to 4000 characters.

One of my clients experienced a problem with concatenated strings in SQL server 2008. They tried to concatenate strings two large strings into a nvarchar(max) variable but only got the first 4000 characters. The problem was the limits of concatenation operator (the + sign).

“If the result of the concatenation of strings exceeds the limit of 8,000 bytes, the result is truncated.” (quote from SQL server books online)

Since the nvarchar type takes 2 bytes per character, the limit was reached at 4000 characters. You can try the code below and see what happens. In the first select you will only get a list of A and not a single B  In the second select you will get the expected result.