SQL Server 2008 VarChar（Max）仅返回8000个字符。为什么？

Question

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

这将返回窗口 7,997 个字符。

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

这将返回到网格中7,996个字符。打印窗口中的额外字符是CRLF。我的选项如下： 工具 → 选项 → 查询结果 → 结果转为文本 → 每列显示的最大字符数 = 8192

因此，我期望在网格中返回8,192个字符，并期望在我的窗口中返回11,001个字符。

然后有这个测试：

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

我在理解这里缺少了什么？它的行为似乎完全不符合我的预期？

- Gregory Hart

Answer 1

如果参数不是varchar(max)/nvarchar(max)类型，则复制函数的限制为8000个字符。

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

如果 string_expression 不是 varchar(max) 或 nvarchar(max) 类型，REPLICATE 将在 8,000 字节处截断返回值。要返回大于 8,000 字节的值，必须将 string_expression 显式转换为适当的大值数据类型。

- David דודו Markovitz

---

默认情况下，它会截断到8K，但是您可以将结果转换为更大。例如，像OP一样使用varchar(max)。 - S3S

强制转换不是针对结果，而是针对参数。 - David דודו Markovitz

实际上，在我看到它之前 - David דודו Markovitz

Answer 2

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

Returns 10000

- John Cappelletti

---

也许你应该添加一些解释。 - Juan Carlos Oropeza

@JuanCarlosOropeza Dudu最初的回答只是“8000个字符是复制函数的限制”。我正在说明有一个简单的解决方法。 - John Cappelletti

Answer 3

您在复制函数中使用的文本 '123456' 不是 varchar(max) 数据类型。正如 Dudu 所提到的，如果表达式不是 varchar(max) 数据类型，则复制函数的限制为 8000 个字符。

- Cham

Answer 4

Dudu讲得很清楚。下面的字符串有9,060个字符。

 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

这个返回结果和我预期的一样。

- Gregory Hart

---

好的，...这种方法可以工作？！?! 它在内存中正确地评估了它，但实际的打印语句和返回到网格的字符仍然限制在7,997个字符。我离题了，不关闭这个问题，因为我没有看到它已经得到答复。 - Gregory Hart

“Results to Grid”和“Results to Text”受您在OP中突出显示的选项的限制。非XML单元格的上限为[65,535个字符]（https://msdn.microsoft.com/en-us/library/ms190078.aspx）。[打印](https://msdn.microsoft.com/en-us/library/ms176047.aspx?f=255&MSPPError=-2147217396)限制为8,000个字符。据我所知，这是一个硬性限制，没有解决方法。 - David Rushton