Can someone please explain why this is happening on SQL Server 2008:
declare @sql Nvarchar(max);
set @sql =N'';
select @sql = @sql +replicate('a',4000) + replicate('b', 6000);
select len(@sql)
Returns: 8000
Multiple sites suggest that as long as first variable is of type NVARCHAR(MAX), truncation should not occur, but it still does.
ps2goat :
Because 'a' and 'b' are not of type NVARCHAR(MAX)\n\nLike this, it should work:\n\ndeclare @sql Nvarchar(max),\n @a nvarchar(max),\n @b nvarchar(max);\n\nselect @sql =N'', @a = N'a', @b = N'b';\n\nselect @sql = @sql +replicate(@a,4000) + replicate(@b, 6000);\n\nselect len(@sql)\n\n\nThis is the link to Microsoft's REPLICATE function information:\nhttps://msdn.microsoft.com/en-us/library/ms174383.aspx\n\nIn there, it says:\n\n\n If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.\n",
2015-04-28T23:36:07