NVARCHAR(MAX) 的最大长度
悦光阴 人气:4本文使用的环境是SQL Server 2017， 主机是64位操作系统。
大家都知道，Micorosoft Docs对 max参数的定义是：max 指定最大的存储空间是2GB，该注释是不严谨的：
nvarchar [ ( n | max ) ]
Variable-size string data. n defines the string size in byte-pairs and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^30-1 characters (2 GB).
结论1：
当定义数据表的一个nvarchar(max)类型的数据列时，该类型存储数据的最大空间才是2GB。
结论2：
当定义一个nvarchar(max)类型的变量时，变量的最大存储空间并不会达到2GB，最多是8000B。
declare @txt nvarchar(max) -- 8000B
做一个简单的测试，对一个Unicode 字符（占2B）重复5000次，当把该值复制给nvarchar(max)类型的变量时，该变量的长度是8000B：
declare @txt nvarchar(max) set @txt= replicate(N'a',5000) print datalength(@txt)
结论3：
当定义一个nvarchar(max)类型的变量时，把多个nvarchar(max)类型的变量进行拼接，能够突破8000B的限制，最大可以达到2GB。
例如，下面的示例，输出的结果是1600，这代表@cmd变量存储的字节数量是1600。
declare @cmd varchar(max)
set @cmd = 'print /*' + replicate ('-', 7990);
set @cmd = @cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@cmd)
print datalength (@cmd)
对结论3的应用：
当用到多于8000B的字符变量时，可以把多个字符进行拼接，进而突破8000B的闲置，最大达到最大2GB的存储空间。
举个例子，从数据库中查询所有数据表的结构，并获取该列的一个样本值：
declare @sql nvarchar(max)
;with cte_table as
(
select s.name as table_schema
,o.name as table_name
,c.name as column_name
,t.name as data_type
from sys.tables o
inner join sys.schemas s
on o.schema_id=s.schema_id
inner join sys.columns c
on o.object_id=c.object_id
inner join sys.types t
on c.user_type_id=t.user_type_id
)
select @sql=coalesce(@sql+N'union ',N'')+formatmessage(N'select top 1 table_name=''%s'',column_name=''%s'',data_type=''%s'',sample=cast(%s as nvarchar(max)) from %s'
+nchar(10),table_name, column_name, data_type, column_name, table_schema + '.' + table_name)
from cte_table
where table_schema='xxx'
print datalength(@sql)
print @sql
结论4：
当用到多于8000B的字符变量时，也可以考虑xml变量，该变量的最大存储空间是2GB。把xml变量强转为nvarchar(max)变量，这样也可以突破8000B的限制：
declare @txt xml
举个例子，从数据库中查询所有数据表的结构，并获取该列的一个样本值：
declare @sql nvarchar(max)
;with cte_table as
(
select s.name as table_schema
,o.name as table_name
,c.name as column_name
,t.name as data_type
from sys.tables o
inner join sys.schemas s
on o.schema_id=s.schema_id
inner join sys.columns c
on o.object_id=c.object_id
inner join sys.types t
on c.user_type_id=t.user_type_id
)
select @sql=cast((
select formatmessage(N'union select top 1 table_name=''%s'',column_name=''%s'',data_type=''%s'',sample=cast(%s as nvarchar(max)) from %s'
+nchar(10) ,table_name, column_name, data_type, column_name, table_schema + '.' + table_name)
from cte_table
where table_schema='xxx'
for xml path('')
) as nvarchar(max))
select @sql=substring(@sql, 7,len(@sql)-7)
exec (@sql)
参考文档：
加载全部内容