一个varchar(max)变量的最大大小是多少？

Question

一个varchar(max)变量的最大大小是多少？

104

过去的任何时候，如果有人问我varchar(max)的最大大小，我会说是2GB，或者查找更精确的数字（2^31-1，或2147483647）。

然而，在最近的一些测试中，我发现varchar(max)变量似乎可以超过这个大小：

create table T (
    Val1 varchar(max) not null
)
go
declare @KMsg varchar(max) = REPLICATE('a',1024);
declare @MMsg varchar(max) = REPLICATE(@KMsg,1024);
declare @GMsg varchar(max) = REPLICATE(@MMsg,1024);
declare @GGMMsg varchar(max) = @GMsg + @GMsg + @MMsg;
select LEN(@GGMMsg)
insert into T(Val1) select @GGMMsg
select LEN(Val1) from T

结果：

(no column name)
2148532224
(1 row(s) affected)
Msg 7119, Level 16, State 1, Line 6
Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.
The statement has been terminated.

(no column name)
(0 row(s) affected)

既然我现在知道一个变量可以超过2GB的限制，那么有人知道一个varchar(max)变量的实际限制是什么吗？

(上述测试在SQL Server 2008（非R2）上完成。我很想知道它是否适用于其他版本)

- Damien_The_Unbeliever

声明 @x varchar(max) = 'XX'; SELECT LEN(REPLICATE(@x,2147483647)) 对我来说返回的是 4294967294，但运行时间很长 - 即使在 SELECT 已经返回后仍然需要很长时间，所以不确定额外的时间花费在做什么。 - Martin Smith

请查看以下链接：https://dev59.com/I14b5IYBdhLWcg3wiSTV - vicky

Paul White在这里提供了一些细节 https://dba.stackexchange.com/a/315189/3690 - Martin Smith

1

@MartinSmith - 谢谢。可能值得将其编辑进你的回答中，不是吗？ - Damien_The_Unbeliever

2个回答

9

编辑: 经过进一步调查，我最初的假设是错误的，即declare @var datatype = value语法的异常（bug?）。

我修改了你的脚本以适用于2005版本，因为那个语法不被支持，然后在2008上尝试了修改后的版本。在2005中，我得到了Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. 错误消息。而在2008中，修改后的脚本仍然成功运行。

declare @KMsg varchar(max); set @KMsg = REPLICATE('a',1024);
declare @MMsg varchar(max); set @MMsg = REPLICATE(@KMsg,1024);
declare @GMsg varchar(max); set @GMsg = REPLICATE(@MMsg,1024);
declare @GGMMsg varchar(max); set @GGMMsg = @GMsg + @GMsg + @MMsg;
select LEN(@GGMMsg)

- Joe Stefanelli

脚本总是会产生一个错误（尝试进行表插入），但在2008年，我总是在第一个结果集中得到一个结果，表明变量确实存在，并且长度超过了2^31-1。 - Damien_The_Unbeliever

@Damien_The_Unbeliever：我将脚本缩小到仅变量部分，现在得到与您相同的结果。2005年，在“set @GGMMsg=…”语句上会出现“尝试增长…”错误。在2008年，脚本成功执行。 - Joe Stefanelli

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Martin Smith · Accepted Answer

据我所知，SQL Server 2008 没有上限限制。在 SQL Server 2005 中，你问题中的代码在为变量 @GGMMsg 赋值时会失败，并显示以下错误信息：

Attempting to grow LOB beyond maximum allowed size of 2,147,483,647 bytes.

同时，以下代码会失败并显示以下错误信息：

REPLICATE: The length of the result exceeds the length limit (2GB) of the target large type.

然而，似乎这些限制在 SQL Server 2008 中已经被取消了。

DECLARE @y VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),92681); 

SET @y = REPLICATE(@y,92681);

SELECT LEN(@y)

返回结果

8589767761

我在我的32位台式机上运行了这个程序，因此这个8GB的字符串远远超出可寻址内存

正在运行

select internal_objects_alloc_page_count
from sys.dm_db_task_space_usage
WHERE session_id = @@spid

返回

internal_objects_alloc_page_co 
------------------------------ 
2144456

我认为这些数据都被存储在tempdb的LOB页面中，长度没有得到验证。增长的页面计数都与SET @y = REPLICATE(@y,92681);语句相关。初始变量赋值给@y和LEN计算不会增加其大小。

提及这一点的原因是页面计数比我预期的要多得多。假设8KB一页，则这相当于16.36GB，显然比必要的要多或少一倍。我猜测这可能是由于字符串连接操作的低效性需要复制整个巨大字符串并附加一个块到结尾，而不能将其添加到现有字符串的结尾。不幸的是，目前.WRITE方法不支持 varchar(max) 变量。

Paul White在此处证实了上述推测，并提供了有关如果<= 512KB则完全保留变量在内存中，对于大于该值的值则更改为基于tempdb的备份方案的信息。

补充内容

我还测试了将nvarchar(max) + nvarchar(max)和nvarchar(max) + varchar(max)连接的行为。这两种情况都允许超过2GB的限制。然而，尝试将其结果存储在表中，则会导致错误消息Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes.。以下是该脚本（可能需要很长时间才能运行）。

DECLARE @y1 VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),2147483647); 
SET @y1 = @y1 + @y1;
SELECT LEN(@y1), DATALENGTH(@y1)  /*4294967294, 4294967292*/


DECLARE @y2 NVARCHAR(MAX) = REPLICATE(CAST('X' AS NVARCHAR(MAX)),1073741823); 
SET @y2 = @y2 + @y2;
SELECT LEN(@y2), DATALENGTH(@y2)  /*2147483646, 4294967292*/


DECLARE @y3 NVARCHAR(MAX) = @y2 + @y1
SELECT LEN(@y3), DATALENGTH(@y3)   /*6442450940, 12884901880*/

/*This attempt fails*/
SELECT @y1 y1, @y2 y2, @y3 y3
INTO Test