本文使用的环境是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).

实际上，只有当定义数据表的一个nvarchar(max)类型的数据列时，该类型存储数据的最大空间才是2GB。当定义一个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)

有时需要用到多于8000B的字符变量时，可以考虑xml变量，该变量的最大存储空间是2GB。

declare @txt xml

举个例子，从数据库中查询所有数据表的结构，并获取该列的一个样本值，当获取到执行的TSQL脚本时，需要手动对脚本进行修改，删除第一个union，然后执行Query，就可以得到所需的结果：

;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 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('')

 

参考文档：