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?

David דודו Markovitz · Answer 1 · 2016-11-15T15:10:05.547

4

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.

edited Nov 15 '16 at 15:10

answered Nov 15 '16 at 15:02

David דודו Markovitz

42,900
6
64
88

1

By default it truncates to 8K, but you can cast the results larger. i.e. using varchar(max) as OP did – S3S Nov 15 '16 at 15:05
1

The cast is not to the result but to the argument – David דודו Markovitz Nov 15 '16 at 15:06
Actually before I saw it – David דודו Markovitz Nov 15 '16 at 15:08

score 2 · Answer 2 · answered Nov 15 '16 at 15:03

2

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

Returns 10000

answered Nov 15 '16 at 15:03

John Cappelletti

79,615
7
44
66

3

Maybe you should add some explanation. – Juan Carlos Oropeza Nov 15 '16 at 15:04
@JuanCarlosOropeza Dudu's initial answer was simply "8000 characters is a limitation of the replicate function". I was illustrating that there is an easy work around. – John Cappelletti Nov 15 '16 at 15:20

score 1 · Answer 3 · answered Nov 15 '16 at 15:10

1

your '123456' text inside the replicate function is not varchar(max) data type. As Dudu mentioned replicate function as limitation for 8000 chars if expression is not varchar(max) data type.

answered Nov 15 '16 at 15:10

Cham

113
1
9

Gregory Hart · Answer 4 · 2016-11-15T15:24:41.827

Dudu explained it clearly. Below string is 9,060 characters.

 DECLARE @VarCharMax VarChar(Max)
 SET @VarCharMax = 'This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!This is a test!'
 PRINT Right(@VarCharMax,3)  -- returns st!
 PRINT Right(Left(@VarCharMax,8000),3) -- returns is 
 PRINT Right(Left(@VarCharMax,7999),3) -- returns his
 PRINT Right(Left(@VarCharMax,7998),3) -- returns Thi
 PRINT Right(Left(@VarCharMax,7997),3) -- returns !Th
 PRINT Right(Left(@VarCharMax,7996),3) -- returns t!h

This returns as I would expect.

OK, ... this kind of works?!?! It properly evaluates it in memory, but the actual print statement and the return to the grid is still limited to 7,997 characters. I digress to not close this question, as I don't see that it has been answered yet. — Gregory Hart, Nov 15 '16 at 15:28
*Results to Grid* and *Results to Text* are limited by the options you highlighted, in the OP. Non-XML cells have an upper cap of [65,535 characters](https://msdn.microsoft.com/en-us/library/ms190078.aspx). [Print](https://msdn.microsoft.com/en-us/library/ms176047.aspx?f=255&MSPPError=-2147217396) is limited to 8,000 characters. As far as I can tell; this is a hard limit, without workaround. — David Rushton, Nov 15 '16 at 15:46

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

4 Answers4