NVARCHAR(MAX) : Concatenation yields silent truncation - by AaronBertrand

Status : 

  Won't Fix<br /><br />
		Due to several factors the product team decided to focus its efforts on other items.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


5
2
Sign in
to vote
ID 283368 Comments
Status Closed Workarounds
Type Bug Repros 0
Opened 6/19/2007 6:28:13 AM
Access Restriction Public

Description

When concatenating values > 4000 characters, it seems that the concatenation operation converts the values to NVARCHAR(4000).  This to me is potentially unacceptable data loss.  I am observing this behavior in SQL 2005 and in the June CTP of Katmai (SQL 2008).  Initially I thought the problem only had to do with 80 compatibility level (as I found this researching another issue) but that is not the case.
Sign in to post a comment.
Posted by Microsoft on 6/20/2007 at 1:41 PM
Hi
The behavior is, as you have guessed, related to backward compatibility. SQL Server 2000 and previous releases would let you concatenate nvarchar(4000) values and would not give an error if the result was larger than 4000 characters, instead it would truncate.

To preserve that behavior and minimize breaking applications, the behavior of concatenating nvarchar(<= 4000) values will truncate the result.

If you want the result to not truncate, one of the values being concatenated should be typed as nvarchar(max).

Note also that if one of the arguments is a constant with more than 8000 bytes then the constant will be typed as 'max' and you will get the results you expect.

This behavior is by design to preserve application compatibility and will not be changed in SQL Server 2008.

Thank you for the feedback.

- Christian Kleinerman