Beware of nvarchar(max) data loss in SQL Server

This problem happens when you’re trying to concatenate from varchar(4001) until varchar (8000) in SQL Server, the concatenated string will lose the data from character 4001 onwards in the declared nvarchar(max) variable.

So, this is a little proof for the above problem:

DECLARE @x nvarchar(max) = replicate('a',8000)
SELECT len(@x) as RESULT

From the above code, the result of the query is 8000, as we expected.

Now let’s try to do almost the same thing but now with concatenate:

DECLARE @x nvarchar(max) = ''
SET @x += replicate('a',8000)
SELECT len(@x) as RESULT

We’re expecting the result will be 8000, but as you can see, the result is 4000 instead, it’s weird isn’t it? as we all know the nvarchar(max) should be able to concatenate all character given, but it will only take until 4000 instead.

Actually this has been reported to the Microsoft, and they have given a feedback in this hyperlink. As their reply, they suggested us to cast one of the expressions above to nvarchar(max) or varchar(max) in order to make it work.