问题描述
在过去的任何时候，如果有人问我 varchar(max) 的最大大小，我会说 2GB，或者查找更准确的 图(2^31-1 或 2147483647).
At any time in the past, if someone had asked me the maximum size for a varchar(max), I'd have said 2GB, or looked up a more exact figure (2^31-1, or 2147483647).
然而，在最近的一些测试中，我发现 varchar(max) 变量显然可以超过这个大小:
However, in some recent testing, I discovered that varchar(max) variables can apparently exceed this size:
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) 变量的实际限制是多少?
So, given that I now know that a variable can exceed the 2GB barrier - does anyone know what the actual limit is for a varchar(max) variable?
(以上测试在 SQL Server 2008(不是 R2)上完成.我很想知道它是否适用于其他版本)
(Above test completed on SQL Server 2008 (not R2). I'd be interested to know whether it applies to other versions)
推荐答案
据我所知，2008 年没有上限.
As far as I can tell there is no upper limit in 2008.
在 SQL Server 2005 中，您问题中的代码在分配给 @GGMMsg 变量时失败了
In SQL Server 2005 the code in your question fails on the assignment to the @GGMMsg variable with
尝试将 LOB 增长到超过最大允许大小 2,147,483,647字节.
下面的代码失败了
REPLICATE:结果的长度超过了长度限制(2GB)目标大类型.
不过，这些限制似乎已悄然解除.2008年
However it appears these limitations have quietly been lifted. On 2008
DECLARE @y VARCHAR(MAX) = REPLICATE(CAST('X' AS VARCHAR(MAX)),92681);
SET @y = REPLICATE(@y,92681);
SELECT LEN(@y)
退货
8589767761
我在我的 32 位台式机上运行这个，所以这个 8GB 字符串超出了可寻址内存
I ran this on my 32 bit desktop machine so this 8GB string is way in excess of addressable memory
运行
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 计算没有增加这个.
so I presume this all just gets stored in LOB pages in tempdb with no validation on length. The page count growth was all associated with the SET @y = REPLICATE(@y,92681); statement. The initial variable assignment to @y and the LEN calculation did not increase this.
之所以提到这一点，是因为页数比我预期的要多得多.假设一个 8KB 的页面，那么它的大小为 16.36 GB，这显然或多或少是看起来需要的两倍.我推测这可能是由于字符串连接操作效率低下，需要复制整个巨大的字符串并将一个块附加到末尾而不是能够添加到现有字符串的末尾.不幸的是，目前 .WRITE 方法 不支持 varchar(max) 变量.
The reason for mentioning this is because the page count is hugely more than I was expecting. Assuming an 8KB page then this works out at 16.36 GB which is obviously more or less double what would seem to be necessary. I speculate that this is likely due to the inefficiency of the string concatenation operation needing to copy the entire huge string and append a chunk on to the end rather than being able to add to the end of the existing string. Unfortunately at the moment the .WRITE method isn't supported for varchar(max) variables.
添加
我还测试了连接 nvarchar(max) + nvarchar(max) 和 nvarchar(max) + varchar(max) 的行为.这两者都允许超过 2GB 的限制.尝试将其结果存储在一个表中然后失败，但是错误消息 Attempting to grow LOB 超过最大允许大小 2147483647 字节..脚本如下(可能需要很长时间才能运行).
I've also tested the behaviour with concatenating nvarchar(max) + nvarchar(max) and nvarchar(max) + varchar(max). Both of these allow the 2GB limit to be exceeded. Trying to then store the results of this in a table then fails however with the error message Attempting to grow LOB beyond maximum allowed size of 2147483647 bytes. again. The script for that is below (may take a long time to run).
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
这篇关于varchar(max) 变量的最大大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！