SQL NVARCHAR 和 VARCHAR 限制 [英] SQL NVARCHAR and VARCHAR Limits

问题描述

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

All, I have a large (unavoidable) dynamic SQL query. Due to the number of fields in the selection criteria the string containing the dynamic SQL is growing over 4000 chars. Now, I understand that there is a 4000 max set for NVARCHAR(MAX), but looking at the executed SQL in Server Profiler for the statement

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

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

Seems to work(!?), for another query that is also large it throws an error which is associated with this 4000 limit(!?), it basically trims all of the SQL after this 4000 limit and leaves me with a syntax error. Despite this in the profiler, it is showing this dynamic SQL query in full(!?).

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

What exactly is happening here and should I just be converting this @SQL variable to VARCHAR and get on with it?

感谢您的时间.

附言.能够打印出超过 4000 个字符来查看这些大查询也很好.以下限制为4000

Ps. It would also be nice to be able to print out more than 4000 chars to look at these big queries. The following are limited to 4000

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

还有其他很酷的方法吗?

is there any other cool way?

推荐答案

我了解 NVARCHAR(MAX)

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

Your understanding is wrong. nvarchar(max) can store up to (and beyond sometimes) 2GB of data (1 billion double byte characters).

从在线书籍中的nchar 和 nvarchar 中，语法是>

From nchar and nvarchar in Books online the grammar is

nvarchar [ ( n | max ) ]

| 字符表示这些是替代项.即您指定要么 n 或文字max.

The | character means these are alternatives. i.e. you specify either n or the literal max.

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

If you choose to specify a specific n then this must be between 1 and 4,000 but using max defines it as a large object datatype (replacement for ntext which is deprecated).

事实上，在 SQL Server 2008 中，对于 变量，如果 tempdb (此处显示)

In fact in SQL Server 2008 it seems that for a variable the 2GB limit can be exceeded indefinitely subject to sufficient space in tempdb (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) 并且会发生截断.

1. varchar(n) + varchar(n) will truncate at 8,000 characters.
2. nvarchar(n) + nvarchar(n) will truncate at 4,000 characters.
3. varchar(n) + nvarchar(n) will truncate at 4,000 characters. nvarchar has higher precedence so the result is nvarchar(4,000)
4. [n]varchar(max) + [n]varchar(max) won't truncate (for < 2GB).
5. varchar(max) + varchar(n) won't truncate (for < 2GB) and the result will be typed as varchar(max).
6. varchar(max) + nvarchar(n) won't truncate (for < 2GB) and the result will be typed as nvarchar(max).
7. nvarchar(max) + varchar(n) will first convert the varchar(n) input to nvarchar(n) and then do the concatenation. If the length of the varchar(n) string is greater than 4,000 characters the cast will be to nvarchar(4000) and truncation will occur.

字符串文字的数据类型

如果您使用 N 前缀并且字符串长度为 <= 4,000 个字符，它将被输入为 nvarchar(n) 其中 n 是字符串的长度.例如，N'Foo' 将被视为 nvarchar(3).如果字符串超过 4,000 个字符，它将被视为 nvarchar(max)

Datatypes of string literals

If you use the N prefix and the string is <= 4,000 characters long it will be typed as nvarchar(n) where n is the length of the string. So N'Foo' will be treated as nvarchar(3) for example. If the string is longer than 4,000 characters it will be treated as nvarchar(max)

如果您不使用 N 前缀并且字符串长度为 <= 8,000 个字符，它将被输入为 varchar(n) 其中 n 是字符串的长度.如果长为 varchar(max)

If you don't use the N prefix and the string is <= 8,000 characters long it will be typed as varchar(n) where n is the length of the string. If longer as varchar(max)

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

For both of the above if the length of the string is zero then n is set to 1.

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

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

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

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

The above returns 8000 for both methods of concatenation.

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

You are getting truncation either because you are concatenating two non max datatypes together or because you are concatenating a varchar(4001 - 8000) string to an nvarchar typed string (even nvarchar(max)).

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

To avoid the second issue simply make sure that all string literals (or at least those with lengths in the 4001 - 8000 range) are prefaced with N.

为了避免第一个问题改变分配

To avoid the first issue change the assignment from

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

到

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

以便 NVARCHAR(MAX) 从一开始就参与连接(因为每个连接的结果也将是 NVARCHAR(MAX) 这将传播)

so that an NVARCHAR(MAX) is involved in the concatenation from the beginning (as the result of each concatenation will also be NVARCHAR(MAX) this will propagate)

确保您有结果到网格"；模式选择然后你可以使用

Make sure you have "results to grid" mode selected then you can use

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

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

The SSMS options allow you to set unlimited length for XML results. The processing-instruction bit avoids issues with characters such as < showing up as <.

这篇关于SQL NVARCHAR 和 VARCHAR 限制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！