SQL NVARCHAR和VARCHAR限制

Question

总之，我有一个大型（不可避免的）动态SQL查询。由于选择条件中的字段数量，包含动态SQL的字符串增加了4000个字符。现在，我了解到NVARCHAR（max）的最大值设置为4000，但是查看在服务器探查器中执行的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

你也可以使用nvarchar文本。这意味着你必须在你巨大的字符串前有一个“N”，就是这样！再也没有限制了

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

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

Answer 3

我知道NVARCHAR（max）

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

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

nvarchar [ ( n | max ) ]

|字符表示这些是可选的。i、 e.指定n或文本max。

如果选择指定一个特定的n，则该值必须介于1和4000之间，但使用max将其定义为一个大对象数据类型（替换已弃用的ntext）。

事实上，在SQL服务器2008中，似乎对于一个变量来说，2GB的限制可以无限期地超过，前提是temdb中有足够的空间（显示在这里）

关于你问题的其他部分

1. varchar（n）varchar（n）将截断为8000个字符。
2. nvarchar（n）nvarchar（n）将截断为4000个字符。
3. varchar（n）nvarchar（n）将截断为4000个字符。nvarchar具有更高的优先级，因此结果是nvarchar（4,000）
4. [n]varchar（max）[n]varchar（max）不会截断（对于

如果使用N前缀，且字符串为

如果不使用N前缀，并且字符串为

对于以上两种情况，如果字符串的长度为零，则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结果设置无限长度。处理指令位避免了字符的问题，例如