SQL NVARCHAR 和 VARCHAR 限制

Question

所有，我有一个大型(不可避免的)动态 SQL 查询。由于选择标准中的字段数量较多，包含动态 SQL 的字符串将增长到超过 4000 个字符。现在，我了解到 NVARCHAR(MAX) 的最大设置为 4000，但在 Server Profiler 中查看执行的 SQL 语句

DELARE @SQL NVARCHAR(MAX);
SET @SQL = 'SomeMassiveString > 4000 chars...';
EXEC(@SQL);
GO

似乎有效(!？)，对于另一个也很大的查询，它会抛出一个与此 4000 限制相关的错误(!？)，它基本上会修剪此 4000 限制之后的所有 SQL，并给我留下一个语法错误。尽管如此，在探查器中，它仍以完整(!？)的形式显示此动态 SQL 查询。

这里到底发生了什么？我应该将这个 @SQL 变量转换为 VARCHAR 并继续使用它吗？

感谢您的宝贵时间。

诗。如果能够打印出 4000 多个字符来查看这些大查询，那就太好了。以下限量4000个

SELECT CONVERT(XML, @SQL);
PRINT(@SQL);

还有其他很酷的方法吗？

Answer 1

I understand that there is a 4000 max set for NVARCHAR(MAX)

你的理解是错误的。 nvarchar(max)最多可存储(有时甚至超过)2GB 数据(10 亿个双字节字符)。

来自nchar and nvarchar在线书籍中的语法是

nvarchar [ ( n | max ) ]

|性格意味着这些是替代品。即您指定任一个 n或字面值 max .

如果您选择指定特定的n那么这必须在 1 到 4,000 之间，但使用 max将其定义为大对象数据类型(替换已弃用的 ntext )。

事实上，在 SQL Server 2008 中，对于变量来说，如果 tempdb 中有足够的空间，则可以无限期地超过 2GB 限制。 (Shown here)

关于您问题的其他部分

连接时的截断取决于数据类型。

1. varchar(n) + varchar(n)将在 8,000 个字符处截断。
2. nvarchar(n) + nvarchar(n)将在 4,000 个字符处截断。
3. varchar(n) + nvarchar(n)将在 4,000 个字符处截断。 nvarchar具有更高的优先级，因此结果是 nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max)不会截断(< 2GB)。
5. varchar(max) + varchar(n)不会截断(对于 < 2GB)，结果将键入为 varchar(max) .
6. varchar(max) + nvarchar(n)不会截断(对于 < 2GB)，结果将键入为 nvarchar(max) .
7. nvarchar(max) + varchar(n)首先会转换varchar(n)输入nvarchar(n)然后进行串联。 如果varchar(n)的长度字符串大于 4,000 个字符，则强制转换为 nvarchar(4000)并且会发生截断。

字符串文字的数据类型

如果您使用N前缀且字符串长度 <= 4,000 个字符，它将被键入为 nvarchar(n)哪里n是字符串的长度。所以N'Foo'将被视为 nvarchar(3)例如。如果字符串长度超过 4,000 个字符，它将被视为 nvarchar(max)

如果您不使用N前缀且字符串长度 <= 8,000 个字符，它将被键入为 varchar(n)哪里n是字符串的长度。如果长如varchar(max)

对于上述两种情况，如果字符串的长度为零，则 n设置为 1。

较新的语法元素。

1.CONCAT函数在这里没有帮助

DECLARE @A5000 VARCHAR(5000) = REPLICATE('A',5000);

SELECT DATALENGTH(@A5000 + @A5000), 
       DATALENGTH(CONCAT(@A5000,@A5000));

以上两种连接方法都返回 8000。

2.小心 +=

DECLARE @A VARCHAR(MAX) = '';

SET @A+= REPLICATE('A',5000) + REPLICATE('A',5000)

DECLARE @B VARCHAR(MAX) = '';

SET @B = @B + REPLICATE('A',5000) + REPLICATE('A',5000)


SELECT DATALENGTH(@A), 
       DATALENGTH(@B);`

返回

-------------------- --------------------
8000                 10000

请注意@A遇到截断。

如何解决您遇到的问题。

您会被截断，因为您连接了两个非 max数据类型在一起或者因为您连接 varchar(4001 - 8000)字符串到 nvarchar输入字符串(甚至 nvarchar(max) )。

为了避免第二个问题，只需确保所有字符串文字(或至少那些长度在 4001 - 8000 范围内的字符串文字)都以 N 开头。 .

为了避免第一个问题，请更改分配

DECLARE @SQL NVARCHAR(MAX);
SET @SQL = 'Foo' + 'Bar' + ...;

至

DECLARE @SQL NVARCHAR(MAX) = ''; 
SET @SQL = @SQL + N'Foo' + N'Bar'

这样一个NVARCHAR(MAX)从一开始就参与串联(因为每个串联的结果也将是 NVARCHAR(MAX) 这将传播)

查看时避免截断

确保您选择了“结果到网格”模式，然后就可以使用

select @SQL as [processing-instruction(x)] FOR XML PATH 

SSMS 选项允许您为 XML 设置无限长度结果。 processing-instruction bit 避免了诸如 < 等字符的问题显示为 < .