# NVARCHAR(MAX) 的最大长度

2019/04/10 10:10

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).

## 结论2：

print datalength(replicate(N'a',5000))
print datalength(replicate(N'a',5000)+replicate(N'a',5000))

declare @txt nvarchar(max)
set @txt= replicate(N'a',5000)
print datalength(@txt)

## 结论3：

declare @cmd varchar(max)
set @cmd = 'print /*' + replicate ('-', 7990);
set @cmd = @cmd + replicate ('-', 7990) + '*/ getdate()';
exec (@cmd)
print datalength (@cmd)

### 对结论3的应用：执行动态SQL，传递的TSQL脚本的最大字符数量是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

0
0 收藏

0 评论
0 收藏
0