SQL Server and String Concatenation

Slava Murygin walks us through the rules around string concatenation with the + operator in SQL Server:

Pretty recently hit very interesting problem of how SQL Server concatenates strings.

The very same SELECT clause in some cases produced VARCHAR(MAX), but in same cases it cut the result to VARCHAR(8000) ( or NVARCHAR(4000) )

After a research I’ve discovered following:

Read on to see what Slava has discovered.