Guy Glantser hits on a pain point in SQL Server when dealing with long strings:
Now, let’s talk about concatenation. What do you think would be the data type of the following expression?
N’ABCD’ + N’EFG’
Correct! It’s NVARCHAR(7). Everything is making sense. Isn’t it great?
Now, let’s complicate things just a little bit. Suppose you have an expression that is a concatenation of two string literals – one of them contains 3,000 characters and the other contains 2,000 characters.
Guy also has a function to print beyond 4000 Unicode characters:
Sometimes, you want to print a long string. For example, you might want to print the definition of a long stored procedure. Or you might have a very long dynamic batch that you are going to execute, but you want to print it first for debug purposes.
The problem with the PRINT statement is not only that it prints up to the first 8,000 bytes. It also truncates your text without even generating a warning.
This is a long-running frustration of mine, especially when writing out complicated dynamic SQL. I think PRINT should have been changed 15 years ago to handle MAX types.