何时使用 nvarchar(Max) = nvarchar(4000)？

Question

有很多在SO和其他网站上的帖子明确说明nvarchar（max）的最大长度为2GB。然而，我在互联网和现实生活中也看到了很多困惑，认为它实际上是Unicode的8000/4000。

我想知道有哪些因素可能改变这个事实，或者可能导致某人错误地假设。

一些我已经收集到的建议/部分答案：

1. Are there older SQL Server versions which did only support a maximum of 4000?

2. When assigning nvarchar(max) variable/column to a concatenation of non-max-sized components, must we convert everything to nvarchar(max) explicitly? Here is something showcasing a strange example, where a text-returning function requires converting, whereas the N for the literal can be omitted:

   declare @s nvarchar(max) 
   select @s = convert(nvarchar(max), replicate('.', 8000)) + N'Hi!'
   select len(@s) -- returns 8003
   
   declare @s nvarchar(max) 
   select @s = replicate('.', 8000) + N'Hi!' 
   select len(@s) -- returns 4000
   
   declare @s nvarchar(max) 
   select @s = convert(nvarchar(max), replicate('.', 8000)) + 'Hi!' 
   select len(@s) -- returns 8003
   

3. Are there ways to disable the functionality? Does sp_tableoption @OptionName=large value types out of row or OBJECTPROPERTY(id,'TableTextInRowLimit') have anything to do with this?

   Clarification: My aim is not to use this functionality, but be aware of its existence, which may be indeed have been used by a higher privilege user that will prevent me from using the max size.

4. Any other points gladly welcome

- George Menoutis

---

相关 - varchar(max)变量的最大大小 - Damien_The_Unbeliever

从来没有出现过 nvarchar(max) 是 4000 的情况。曾经的情况是 在这种情况下不存在 max 关键字，您可以指定的最大数字是 4000，但这并不完全相同。此外，如果您受到此影响，那么您将面临更大的问题，因为最后一个不支持 max 的版本是 SQL Server 2000，而该版本已经很久没有得到支持了。 - Damien_The_Unbeliever

还有许多有关字符串连接的问题，最终会导致返回此文档："如果连接的字符串的结果超过 8,000 字节的限制，则结果将被截断。但是，如果连接的字符串中至少有一个是大型值类型，则不会发生截断。" - Damien_The_Unbeliever

1. 哇，甚至超过2GB？有趣。虽然我对2GB很满意，所以我暂时不会研究它。
2. v2000是第一个具有（或最后一个没有？）nax支持的小贴士对我很重要，也许有答案？因为我在微软文档中找不到相关链接，所以我永远无法确定。

- George Menoutis

@GeorgeMenoutis，你应该阅读文档，而不是零散的答案，这些答案可能与你的问题无关。没有混淆。文档对所有这些都非常清楚。BLOB/CLOB类型从来没有任何4K字符限制。text/ntext/image自从很久以前就已经被弃用了。几乎没有提到这些古老类型的参考资料，因为它们永远不应该被使用。那些参考资料说它们不应该被使用。 - Panagiotis Kanavos

@GeorgeMenoutis 我猜你找到了一些使用旧类型的古老遗留代码，然后感到困惑了？这个更改发生得太久远了，以至于人们实际上已经忘记了它们。当时这些更改在很多本地用户组和微软活动中都有解释。那是19年前的事情了。人们不再谈论它，就像他们不再谈论Visual Basic 6类型系统一样。 - Panagiotis Kanavos

Answer 1

这里有几个要点，因为我放不下在评论里。

1. 是的。在 SQL Server 2005 中引入了(n)varchar(MAX)。以前，您必须使用text、ntext和image来处理varchar(MAX)、nvarchar(MAX)和varbinary(MAX)。老数据类型现已被弃用，您不应该再使用它们。
2. 在组合数据时，数据类型优先级用于计算最终数据类型。当涉及长度时，使用长度的组合值（连接一个varchar(10)和一个varchar(100)将返回一个varchar(110)）。但是，请注意，要实现MAX长度的使用，至少一个字符串必须是(n)varchar(MAX)。SELECT REPLICATE(N'A',3000) + REPLICATE(N'A',3000) AS S将返回一个4000个字符的字符串。+ (String Concatenation) (Transact-SQL) - Remarks：

   如果连接的字符串的结果超过8,000个字节的限制，则结果将被截断。但是，如果连接的字符串中至少有一个是大值类型，则不会发生截断。

3. 禁用什么功能？使用(n)varchar(MAX)？为什么？如果您想阻止人们使用一种数据类型，请阻止他们使用(n)text和image。但是，说真的，您无法停止使用一种数据类型。也许您可以通过DDL触发器变得“聪明”，但我不建议这样做。
   
   为了回答编辑，不能使用sp_tableoption来停止某人使用MAX长度的数据类型；我的上述观点仍然适用。引用文档（sp_tableoption (Transact-SQL) - Arguments：

   大值类型超出行：
   1 = 表中的varchar(max)、nvarchar(max)、varbinary(max)、xml和大型用户定义类型（UDT）列存储在行外，并带有一个16字节的指针到根。
   
   0 = 直接将表中的varchar(max)、nvarchar(max)、varbinary(max)、xml和大型UDT值存储在数据行中，最多可达到8000个字节的限制，并且只要该值可以适合记录中。如果该值不适合记录，则在行内存储指针，并将其余部分存储在LOB存储空间中。0是默认值。
   
   大型用户定义类型（UDT）适用于：SQL Server 2008到SQL Server 2017。
   
   使用CREATE TABLE的TEXTIMAGE_ON选项来指定大数据类型的存储位置。

4. 太泛了，不适合在SO上发布。

- Larnu

---

由于OP的评论表明他们在MS文档中找不到1的答案，我冒昧地添加了一个链接到你的答案。 - Damien_The_Unbeliever

谢谢@Damien_The_Unbeliever。我看到你也找到了+的文档，希望你不介意我借用这个引用。 - Thom A

我已经对3进行了澄清。 - George Menoutis

为您@GeorgeMenoutis进一步澄清了第3点，但并不改变答案。 - Thom A