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限制有关的错误(!?)，它基本上会在这个限制之后截断所有SQL语句，并使我面临语法错误。尽管如此，在分析器中显示了这个动态SQL查询的完整(!?)内容。

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

谢谢你的时间。

附言：能够打印超过4000字符以查看这些大型查询也将是不错的。以下内容均限于4000个字符。

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

还有其他酷炫的方法吗？

- MoonKnight

---

MAX并不是4000限制的同义词，它表示1..4000 或者 MAX。 - Alex K.

打印命令将在4000个字符（对于Unicode）或8000个字符（对于单字节编码）处进行连接。我怀疑这是混淆的根源。 - redcalx

Answer 1

我知道NVARCHAR(MAX)设置了4000的最大值。

你的理解是错误的。nvarchar(max)可以存储多达2GB的数据（有时甚至更多，相当于10亿双字节字符）。

根据书籍在线中的nchar和nvarchar语法规则。

nvarchar [ ( n | max ) ]

|字符表示这些是备选项，即您可以指定n或文字max。
如果选择指定特定的n，则此值必须介于1和4000之间，但使用max定义它为大型对象数据类型（代替已弃用的ntext）。
实际上，在SQL Server 2008中，对于变量而言，可以无限制地超越2GB限制，前提是tempdb有足够的空间。（显示在此处）
关于您问题的其他部分：

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

1. varchar(n) + varchar(n)将在8000个字符处截断。
2. nvarchar(n) + nvarchar(n)将在4000个字符处截断。
3. varchar(n) + nvarchar(n)将在4000个字符处截断。由于nvarchar的优先级更高，因此结果为nvarchar(4000)
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)字符串的长度大于4000个字符，则将强制转换为nvarchar(4000)并进行截断 。

字符串字面值的数据类型

如果使用N前缀，并且字符串< = 4000个字符长，则它将被视为nvarchar(n)，其中n是字符串的长度。例如，N'Foo'将被视为nvarchar(3)。如果字符串长度超过4000个字符，则将其视为nvarchar(max)

如果未使用N前缀，并且字符串<= 8000个字符长，则它将被视为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 位避免了像<这样的字符显示为<时出现的问题。

- Martin Smith

---

@Killercam - 你可能在某个地方得到了一个隐式转换为 nvarchar(4000)。如果一个字符串字面量少于4,000个字符，则被视为 nvarchar(x)。将另一个 nvarchar(x) 值连接到其后会截断而不是升级为 nvarchar(max)。 - Martin Smith

@Killercam - 可能是根据我之前的评论导致截断。尝试更改分配到 DECLARE @SQL NVARCHAR(MAX) = ''; SET @SQL = @SQL + ，以便在连接中涉及 NVARCHAR(MAX)。 - Martin Smith

@Killercam - 可能你有一个长度在4,000到8,000个字符之间的字符串。使用N前缀，它将被视为nvarchar(max)，否则它将被视为varchar(n)，然后在连接到nvarchar时会隐式转换为nvarchar(4000)。 - Martin Smith

我对这个答案感到启发。 - Mudassir Hasan

非常棒的回答。非常感谢！ - John Bell

Answer 2

好的，所以如果“沿着线路”后面出现问题是因为您有一个查询超过了允许的大小（如果它不断增长可能会发生），那么您必须将其分成块并执行字符串值。假设您有以下存储过程：

CREATE PROCEDURE ExecuteMyHugeQuery
    @SQL VARCHAR(MAX) -- 2GB size limit as stated by Martin Smith
AS
BEGIN
    -- Now, if the length is greater than some arbitrary value
    -- Let's say 2000 for this example
    -- Let's chunk it
    -- Let's also assume we won't allow anything larger than 8000 total
    DECLARE @len INT
    SELECT @len = LEN(@SQL)

    IF (@len > 8000)
    BEGIN
        RAISERROR ('The query cannot be larger than 8000 characters total.',
                   16,
                   1);
    END

    -- Let's declare our possible chunks
    DECLARE @Chunk1 VARCHAR(2000),
            @Chunk2 VARCHAR(2000),
            @Chunk3 VARCHAR(2000),
            @Chunk4 VARCHAR(2000)

    SELECT @Chunk1 = '',
           @Chunk2 = '',
           @Chunk3 = '',
           @Chunk4 = ''

    IF (@len > 2000)
    BEGIN
        -- Let's set the right chunks
        -- We already know we need two chunks so let's set the first
        SELECT @Chunk1 = SUBSTRING(@SQL, 1, 2000)

        -- Let's see if we need three chunks
        IF (@len > 4000)
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, 2000)

            -- Let's see if we need four chunks
            IF (@len > 6000)
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, 2000)
                SELECT @Chunk4 = SUBSTRING(@SQL, 6001, (@len - 6001))
            END
              ELSE
            BEGIN
                SELECT @Chunk3 = SUBSTRING(@SQL, 4001, (@len - 4001))
            END
        END
          ELSE
        BEGIN
            SELECT @Chunk2 = SUBSTRING(@SQL, 2001, (@len - 2001))
        END
    END

    -- Alright, now that we've broken it down, let's execute it
    EXEC (@Chunk1 + @Chunk2 + @Chunk3 + @Chunk4)
END

- Mike Perrenoud

Answer 3

你必须使用nvarchar文本。这意味着你只需要在你的大字符串前面加上“N”即可！从此不再有限制。

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

- Max

---

这并不是完整的情况说明。如果你在字符串前面使用N前缀并且字符长度小于或等于4,000个，则该字符串将被视为nvarchar(n)，其中n是字符串长度。例如，N'Foo'将被视为nvarchar(3)。如果字符串长度超过4,000，则将被视为nvarchar(max)。如果你没有使用N前缀，并且字符串长度小于或等于8,000个，则该字符串将被视为varchar(n)，其中n是字符串长度。如果超过8,000，则将被视为varchar(max)。对于上述两种情况，如果字符串长度为零，则n将被设置为1。 - MoonKnight

Answer 4

接受的答案对我有所帮助，但在连接涉及case语句的varchar时我遇到了问题。我知道OP的问题不涉及case语句，但我认为将其发布在这里对于像我一样在构建涉及case语句的长动态SQL语句时苦苦挣扎的其他人会有所帮助。

使用字符串连接的case语句中，接受的答案中提到的规则适用于case语句的每个部分独立地。

declare @l_sql varchar(max) = ''

set @l_sql = @l_sql +
case when 1=1 then
    --without this correction the result is truncated
    --CONVERT(VARCHAR(MAX), '')
 +REPLICATE('1', 8000)
 +REPLICATE('1', 8000)
end

print len(@l_sql)

- Joe

Answer 5

declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 100000)

 insert @local values(cast(@p as varchar(max)))
 select DATALENGTH(col) as collen, col from @local

result collen > 8000, length col value is more than 8000 chars

- Heta77

Answer 6

这是在搜索Azure Data Studio (ADS)解决方案时出现的。ADS还默认将每个文本列限制在约65K左右。

去年，@alanrenmsft提供了在ADS中调整此设置的功能。现在增加此设置可以相应地返回完整的JSON（或文本）。

参考：https://github.com/microsoft/azuredatastudio/issues/392#issuecomment-1239773578

-- MUST SET the following to pull back the full JSON payload.
-- "mssql.query.maxCharsToStore": 2147483647

- Jim