Sometimes you tend to forget the very basic thing in the heat of the moment and struggle for a while. When you realize it, you have nothing but a big grin. This happened to me few days back 🙂
One of my colleague was building a dynamic SQL whose length exceeded 8000 bytes. So I asked him to use VARCHAR(MAX) instead of VARCHAR(8000). But the query was still getting truncated and hence the execution failed with syntax error. The query looked very simple as below but still did not work
After spending sometime, I figured out the reason. What happens is when SQL Server concatenated the strings , say VARCHAR & NVARCHAR, it converts the concatenated string as NVARCHAR (which has higher precedence over VARCHAR). Since NVARCHAR can store only 4000 bytes (unless you use MAX), if your concatenated string is more than 4000, it will get truncated.
This happens not only with different data type but with same data type also. That is when two strings of same data type are concatenated, SQL Server truncates the concatenated string based on the max size of the data type.
Let us look at some example to understand this.
When I looked at the above screenshot, my first thought was that @str1 should have the length as 9000 since it is a VARCHAR(MAX). But what SQL did was it first evaluated the expression in the right hand side. The expression contains same data type , VARCHAR. So no need of conversion. But its max size is 8000. So SQL had to truncate the concatenated result of 9000, to 8000.
The same thing happened in my colleague’s dynamic SQL.
Then, how do we fix it?.
Very simple. Convert the result into one data type which can hold the maximum value. Here is what I did.
In this case, the expression in the right hand side contains two data types, VARCHAR & VARCHAR(MAX). So SQL does an implicit conversion of the concatenated result into VARCHAR(MAX) and also no truncation needed as VARCHAR(MAX) can hold 2 GB of data.