Results of string functions are limited to 16K. This limit is independent of the server’s page size. In Transact-SQL string functions and string variables, literals can be as large as 16K even on a 2K page size.
If set string_rtruncation is on, a user receives an error if an insert or update truncates a character string. However, SQL Server does not report an error if a displayed string is truncated. For example:
select replicate("a", 16383) + replicate("B", 4000)
This shows that the total length would be 20383, but the result string is restricted to 16K.
