varchar(MAX) 总是可取的吗? [英] Is varchar(MAX) always preferable?

问题描述

关于 SQL Server，我理解:

• var 表示内存是惰性分配的，这意味着它完全适合数据(插入时).

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

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

只有当我们想对这个 DB 列实施约束时，我们才应该使用常量大小吗?

解决方案

SO 用户 @Remus Rusanu 有一篇关于这个主题的非常好的文章.这是我偷的一个片段，但我建议你阅读全文:

<块引用>

处理 MAX 类型(varchar、nvarchar 和varbinary) 不同于处理它们的代码路径等效的非最大长度类型.非最大类型可以在内部是表示为普通的指针和长度结构.但最大类型不能在内部存储为连续的内存区域，因为它们可能会增长到 2Gb.所以他们必须由一个流接口，类似于 COM 的 IStream.这延续到每个涉及最大类型的操作，包括简单的赋值和比较，因为这些操作比较复杂通过流媒体接口.最大的影响在代码中可见分配和分配最大类型的变量(我的第一个测试)，但是每次操作都可以看到影响.

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

您可以在此处找到整篇文章.>

Regarding SQL Server, I understand :

• var means the memory is lazy allocated, meaning it fits to the data exactly (on insertion).

• MAX means there is no size restriction\limitation.

Then, is it always preferable to use MAX when using varchar, as we don't allocate the whole size anyhow?

Should we use a constant size only if there is a constraint we want to enforce on this DB column?

解决方案

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:

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.

In the article he shows several examples that demonstrate that using varchar(n) typically improves performance.

You can find the entire article here.

这篇关于varchar(MAX) 总是可取的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！