为什么不使用varchar(max)？

Question

在数据库设计方面，我有些老派，因此我完全支持在列中使用正确的数据大小。然而，在为朋友审查数据库时，我注意到他经常使用 varchar(max)。现在，我的第一反应是把它扔回给他，告诉他改变它。但是，然后我想了想，无法想出一个不让他使用它的好理由（如果你想知道，他使用了一种生成数据库的工具）。

我一直在研究 varchar(max) 的用法，但真的找不到任何不让他使用它的好理由。

他没有将这些列用于索引，坐落于数据库上的应用程序对输入有限制，因此它不会允许在字段中输入大量数据。

如有帮助，感激不尽，帮我说服他看到这个问题的重要性：)。

- AtaLoss

---

请参考此答案，了解另一个原因：https://dev59.com/-nI-5IYBdhLWcg3wHUdB#5654947 - Martin Smith

很有趣！可惜我之前没有发现这个。谢谢！ - AtaLoss

首先，你不能在 VARCHAR(MAX) 列上创建索引，这让我只有在绝对必要时才使用它。 - marc_s

我要指出的是，期望应用程序始终控制输入是愚蠢的。数据几乎总是比应用程序更持久，并且下一个版本的应用程序可能没有正确的限制。 - HLGEM

Answer 1

我的答案与使用Max无关，更关注VARCHAR(max)和TEXT之间的区别。

首先，除非你能确保只编码英文文本且不涉及外国地名等信息，否则应该使用NVARCHAR或NTEXT。

其次，它们允许你做什么。

相比于VARCHAR，TEXT难以更新，但你可以获得全文索引和许多聪明的东西的优势。

另一方面，VARCHAR(MAX)有些模糊，如果单元格大小小于8000个字符，则将其视为行数据。如果大于，则将其视为LOB以进行存储。 因为你无法在不查询RBAR的情况下知道这一点，这可能会对需要确认数据和读取成本的地方采用优化策略。

否则，如果你的用途相对平凡，并且不希望在数据大小方面遇到问题（例如你正在使用.Net，因此不必担心你的字符串/char*对象的大小），那么使用VARCHAR(max)就可以了。

- Russ Clarke

---

我注意到在阅读有关此事的文章时，当您查询表格时，它必须将内存中的记录缓冲区大小设置为最大值。这可能成为一个问题，但是在今天的环境中，多个吉字节（如果不是太字节）和随着内存变得便宜且易于升级，这基本上将使这个问题变得微不足道。 - AtaLoss

这个答案似乎暗示全文索引仅能用于 text 数据类型，但这并非事实。据我所知，text 是一个已被弃用的数据类型，与 varchar(max) 相比并没有任何优势。 - Martin Smith

不使用TEXT/NTEXT的另一个原因是它们已经被弃用了。 - Aaron Bertrand

你能引用一下吗？我只知道TEXT IN ROW已经被弃用了。 - Russ Clarke

微软 SQL Server 的未来版本将删除 ntext、text 和 image 数据类型。在新的开发工作中应避免使用这些数据类型，并计划修改当前使用它们的应用程序。请改用 nvarchar(max)、varchar(max) 和 varbinary(max) 数据类型。 - tibx

Answer 2

有一篇关于为什么不使用varchar max的博客文章在这里

编辑

基本区别在于数据存储的位置。SQL数据行的最大大小为8000字节（或者是8K）。然而，2GB的varchar(max)无法存储在数据行中。SQL Server将其存储在“Out of row”中。

因此，您可能会受到性能影响，因为数据不会在磁盘上的同一位置，参见：http://msdn.microsoft.com/en-us/library/ms189087.aspx

- Shiraz Bhaiji

---

是的，我看过了，但还是有些疑问。如果代码编写正确，应用程序中的数据受到限制，那么它就不应该有任何性能下降。索引不是问题，最后一点是关于设计的，我的朋友并不担心。 - AtaLoss

varchar(max)不会被存储在行外，除非存储在行中的数据超过了行的限制（是大约8k）。例如，如果您在具有3个列的表中将文本“hello world”存储在varchar max中，则很可能不会被存储在行外。 - AtaLoss

不建议使用它们的原因是它们无法被索引。除非您需要存储大量数据，否则使用nvarchar(max)或varchar(max)是一种不良习惯。 - HLGEM

并不总是会影响性能。如果行大小减小，表扫描将加速。如果查询中很少使用varchar(max)，将其移出行将提高性能。 - johnnycrash

当您无法预测可能需要插入行的字符串的大小时，性能下降是值得操作正确性的。 - binki

Answer 3

如果您在OLTP环境中工作，性能至关重要。从开销和调整问题到索引限制和查询瓶颈。使用varcahr（max）或任何其他LOB类型很可能违反大多数设计最佳实践，因此，除非存在某种特定的业务需求无法通过使用其他键入机制处理并且只有varchar（max）适合您的需求，否则为什么要让您的系统和应用程序受到LOB数据类型固有的开销和性能问题的影响呢？

另一方面，如果您在OLAP环境或带有描述符字段的星型模式DW环境中工作，则自然需要冗长的varchar（max），只要您不将其添加到索引中，则可能有用。即使如此，我仍然建议使用char（x）varchar（x），因为始终最好只使用绝对必要的资源来完成工作。

- Scott Johnston

Answer 4

Redgate写了一篇非常好的文章。
https://www.red-gate.com/simple-talk/sql/database-administration/whats-the-point-of-using-varcharn-anymore/

结论

• 如果设计良好，应使用VARCHAR(n)，而不是VARCHAR(MAX)，原因是VARCHAR(MAX)数据不压缩。
• 存储大字符串比存储小字符串需要更长时间。
• 将行内VARCHAR(MAX)值从低于8,000更新到超过8,000相对较慢，但单个事务的差异可能无法测量。
• 将行内VARCHAR(MAX)值从超过8,000更新到低于8,000，要比将表设置为存储数据时更快。
• 对于VARCHAR(MAX)使用离散选项会导致较慢的写入，直到字符串变得非常长。

- Donny V.

Answer 5

为什么不使用varchar(max)呢？以下是一些原因：

1. 就像旧的BLOB一样，SQL Server无法对varchar(max)列进行索引。
2. 过度分配空间是浪费且懒惰的行为，特别是对于varchar(max)，因为它至少会为每行分配8个字节。我曾经看到开发人员将“max”分配给单字节二进制（True / False）变量，只有后来才发现系统在使用这些值区分数据时非常缓慢。
3. 你无法推断正在保存哪种数据类型。明显的用例异常是保存多达8K的实际大文本块。

- alejandrob

Answer 6

除非您期望处理大量数据，否则不应使用它们，以下是原因（直接来自Books Online）：

LOB数据类型ntext、text、varchar(max)、nvarchar(max)、varbinary(max)、xml或image的列不能被指定为索引的关键列。

如果您想降低性能，请将所有内容都使用nvarchar。

- HLGEM

---

但是如果你从一开始就不会按那列键排序怎么办呢？如果你正在存储一个文本块，则可能通过不同的关键列查找它。有必要在可能被存储为自由格式文本并且除了检查IS NULL之外可能永远不会出现在WHERE中的列上放置索引吗？ - binki

@binki，建议不要将varchar（max）用于所有情况，因为您需要索引某些字段。它仅适用于当您需要大量数据时。 - HLGEM

仅仅因为它来自在线书籍并不意味着它是正确的 :) 另一个有时正确使用 (MAX) 的例子是当你从未定大小的源（例如 blobs、NOSQL 字段）存储字符串时，你不知道它可能有多大。我同意 @binki 的观点，通常有一些列你知道永远不会索引。 - 8forty

Answer 7

下面是差异：
VARCHAR(X) 可以被索引
VARCHAR(MAX) 无法被索引

- Vlad Kirov

---

如果不是在 .mdf 文件中，你认为 MS SQL 究竟将 LOB 数据存储在哪里？ - user565869

Answer 8

现在认为应用程序只会向数据库传递短字符串并且这样做没问题，这种想法有点过时了。

现代情况下，你必须预见到数据库主要由当前应用程序访问，但未来版本的应用程序可能也会访问它（那个版本的开发人员是否知道将字符串保持在某个长度以下？）

你必须预见到Web服务、ETL进程、LYNC到SQL以及任何其他已经存在或尚未存在的技术都将用于访问你的数据库。

一般来说，我尽量不超过varchar(4000)，因为毕竟它是四千个字符。如果我超过了这个限制，那么我就会寻找其他数据类型来存储我想要存储的内容。Brent Ozar写了一些非常好的文章。

尽管如此，当你在工作中时，评估当前设计方法对当前需求的适用性非常重要。了解各个部分的工作原理，理解各种方法的权衡，并解决手头的问题。盲目遵循某些伟大的公理可能会使你变成一个旅鼠。

- Stephen Lauzon

---

对于每个长度为4000的varchar类型，你都会强制SQL优化器为索引目的预分配2000个内存块。即使是在varchar(max)之外也没有免费的午餐。2000字节的长度听起来像是一个无法进行索引的自由格式数据列。我99%确定这不是这种情况，无论是为未来过度提供还是其他原因。 - alejandrob

Answer 9

我不知道SQL Server如何从性能、内存和存储的角度处理大型（声明的）varchar字段，但是假设它像处理较小的声明的varchar字段一样高效，仍然有完整性约束的好处。

在数据库上运行的应用程序应该对输入进行限制，但是如果应用程序在这方面存在漏洞，数据库可以正确报告错误。

- at.

---

这是一个很好的观点，特别是如果您没有使用托管语言来读取字符串。 - Russ Clarke

这是一个很好的观点。我会提到这一点，它是用C# 3.5或4开发的（我相信，我应该问问他）。 - AtaLoss

顺便说一下：你的假设是不正确的。 - Martin Smith

@RussClarke 变长字符串真的只是托管环境中的一件事吗？听起来您从未在非托管环境中遇到过堆分配的情况 :-p。 - binki

@MartinSmith 这些只是非常微小的性能改进。此外，博主在比较和WHERE子句中使用了VARCHAR(MAX)。我期望VARCHAR(MAX)的使用场景是在SQL中不进行比较或WHERE的情况下，例如博客文章内容块、某物的自由文本描述等。如果你想要进行比较或WHERE，那么限制长度当然会有所帮助。他举的例子'abc'甚至可以转换为两个整数的比较。这不是放在VARCHAR(MAX)中的数据类型... - binki

@binki 我理解你的观点并大多数同意，但不要低估其他人的用例。我已经花了几个月的时间从云 blob 和其他 NOSQL 数据源中提取数据，这些数据源不会对字符串进行大小调整，并且有几个字段的值在 where 子句中非常有用，但是有一些特别长的独行侠值。可以说这是应用程序错误，但我无法修复该应用程序并且必须存储数据。 - 8forty