SQL varchar(MAX) 总是可取的吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8512164/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is varchar(MAX) always preferable?
提问by Elad Benda
Regarding SQL Server, I understand :
关于 SQL Server，我理解：
varmeans the memory is lazy allocated, meaning it fits to the data exactly (on insertion).MAXmeans there is no size restriction\limitation.
var意味着内存是延迟分配的，这意味着它完全适合数据（插入时）。MAX意味着没有大小限制\限制。
Then, is it always preferable to use MAXwhen using varchar, as we don't allocate the whole size anyhow?
那么，在使用MAX时是否总是更可取varchar，因为我们无论如何都不分配整个大小？
Should we use a constant size only if there is a constraint we want to enforce on this DB column?
仅当我们想对这个 DB 列实施约束时，我们才应该使用常量大小吗？
采纳答案by Abe Miessler
There is a very good article on this subject by SO User @Remus Rusanu. Here is a snippit that I've stolen but I suggest you read the whole thing:
SO 用户@Remus Rusanu 有一篇关于这个主题的非常好的文章。这是我偷的一个片段，但我建议你阅读整篇文章：
The code path that handles the MAX types (varchar, nvarchar and varbinary) is different from the code path that handles their equivalent non-max length types. The non-max types can internally be represented as an ordinary pointer-and-length structure. But the max types cannot be stored internally as a contiguous memory area, since they can possibly grow up to 2Gb. So they have to be represented by a streaming interface, similar to COM's IStream. This carries over to every operation that involves the max types, including simple assignment and comparison, since these operations are more complicated over a streaming interface. The biggest impact is visible in the code that allocates and assign max-type variables (my first test), but the impact is visible on every operation.
处理 MAX 类型（varchar、nvarchar 和 varbinary）的代码路径与处理其等效非最大长度类型的代码路径不同。非最大类型可以在内部表示为普通的指针和长度结构。但是 max 类型不能在内部存储为连续的内存区域，因为它们可能会增长到 2Gb。所以它们必须由一个流接口来表示，类似于 COM 的 IStream。这会延续到涉及 max 类型的每个操作，包括简单的赋值和比较，因为这些操作在流接口上更加复杂。最大的影响在分配和分配最大类型变量的代码中可见（我的第一个测试），但影响在每个操作中都是可见的。
In the article he shows several examples that demonstrate that using varchar(n) typically improves performance.
在文章中，他展示了几个示例，证明使用 varchar(n) 通常可以提高性能。
You can find the entire article here.
您可以在此处找到整篇文章。
回答by Garrett Vlieger
Look here for a good answer:
看看这里有一个很好的答案：
Should I use varchar(n) or varchar(MAX)?
我应该使用 varchar(n) 还是 varchar(MAX)？
The short answer is that from a storage perspective it's the same, but from a query optimization perspective, it's better to use varchar(N).
简短的回答是，从存储的角度来看它是一样的，但从查询优化的角度来看，最好使用 varchar(N)。
回答by O.O
Here's what microsoft recommends:
以下是微软的建议：
- Use char when the sizes of the column data entries are consistent.
- Use varchar when the sizes of the column data entries vary considerably.
- Use varchar(max) when the sizes of the column data entries vary considerably, and the size might exceed 8,000 bytes.
- 当列数据条目的大小一致时使用 char。
- 当列数据条目的大小差异很大时，请使用 varchar。
- 当列数据条目的大小差异很大，并且大小可能超过 8,000 字节时，请使用 varchar(max)。
回答by Shane
Just to be quite explicit on this one, the answer is NO. It is always preferable to use varchar(N), and if you know the size will not vary, then char(N). The MAX types do not and cannot support most of the native SQL features so you cannot add indexes, perform joins nor do effective searches on those types. Incidentally, this is one reason why the full-text search capability exists in SQL Server.
只是在这个问题上非常明确，答案是否定的。最好使用 varchar(N)，如果您知道大小不会变化，则使用 char(N)。MAX 类型不支持也不能支持大多数本机 SQL 功能，因此您无法添加索引、执行连接或对这些类型进行有效搜索。顺便说一下，这也是 SQL Server 中存在全文搜索功能的原因之一。
In addition, varchar(max) prevents the ability to perform online indexes against the entire tablewhich contains the varchar(max) field. This will significantly impact performance of your system.
此外，varchar(max) 阻止对包含 varchar(max) 字段的整个表执行联机索引的能力。这将显着影响系统的性能。
Varchar(max) should only ever be used if the size of the field is known to be over 8K. In every other instance, the size must be specified. Failure to do so is poor design and willlead to performance issues on any but the most trivial of systems.
仅当已知字段大小超过 8K 时才应使用 Varchar(max)。在所有其他情况下，必须指定大小。不这样做是糟糕的设计，将导致除最微不足道的系统之外的任何系统的性能问题。
Some references:
一些参考：
