Why is my nvarchar(max) string truncated to 4000 characters in SQL Server 2005?

Truncation of Nvarchar(Max) String in SS 2005

Why is a string declared as nvarchar(max) only retrieving 4000 characters in TSQL in SS 2005?

Solution

Despite declaring @SQL1 as nvarchar(max), it initially stores a collection of short strings (@alldate, @alldateprevweek) with each below 4000 characters. The string concatenation process treats these as non-max strings and subsequently populates @SQL1 with a maximum of 4000 characters.

To resolve this issue, ensure that the entire right-hand side of the assignment to @SQL1 is nvarchar(max). For instance, initialize @SQL1 as an empty string before concatenating the nvarchar(max) constant:

SET @SQL1 = ''
SET @SQL1 = @SQL1 + 'SELECT DISTINCT Venue...

The assignment operator has the lowest precedence, similar to integer division in other languages. Hence, datatype precedence is determined by assignment, not concatenation.

The above is the detailed content of Why is my nvarchar(max) string truncated to 4000 characters in SQL Server 2005?. For more information, please follow other related articles on the PHP Chinese website!