SQL Server 2008 VarChar(Max) returns only 8000 Characters. Why?

Question

PRINT Convert(VarChar(Max),Replicate('1234567890 ',1000))

This returns to the window 7,997 characters.

SELECT Convert(VarChar(Max),Replicate('1234567890 ',1000))

This returns to the grid 7,996 characters. The extra character in the print window is a CRLF. I have my options as such: Tools → Options → Query Results → Results to Text → Maximum number of characters displayed in each column = 8192

So, I would expect 8,192 characters to be returned in the grid, and I would expect 11,001 characters to be returned to my window.

Then there's this test:

DECLARE @VarCharMax VarChar(Max)
SET @VarCharMax = Replicate('123456',2000)
SELECT Right(@VarCharMax,3) -- returns 456
SELECT Right(Left(@VarCharMax,8000),3) -- returns 456
SELECT Right(Left(@VarCharMax,7999),3) -- returns 456
SELECT Right(Left(@VarCharMax,7998),3) -- returns 456
SELECT Right(Left(@VarCharMax,7997),3) -- returns 345
SELECT Right(Left(@VarCharMax,7996),3) -- returns 234

What am I missing in understanding here? It doesn't seem to behave at all as I would expect?

Answer 1

8000 characters is a limitation of the replicate function unless the argument is of type varchar(max)/nvarchar(max)

https://msdn.microsoft.com/en-us/library/ms174383.aspx

If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.

Answer 2

SELECT len(Replicate(cast('1234567890' as varchar(max)),1000))

Returns 10000