sql - varchar(MAX) 总是更可取吗？

Question

关于SQL Server，我理解:

• var 表示内存是延迟分配的，这意味着它完全适合数据(在插入时)。

• MAX 表示没有大小限制\限制。

那么，在使用 varchar 时是否总是最好使用 MAX，因为我们无论如何都不会分配整个大小？

只有当我们想要对此数据库列强制执行约束时，我们才应该使用常量大小吗？

Answer 1

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.

在文章中，他展示了几个示例，证明使用 varchar(n) 通常可以提高性能。

您可以找到整篇文章here .