SQL性能：使用NVarchar（MAX）而不是NVarChar（200）是否有任何性能损失 [英] SQL performance: Is there any performance hit using NVarchar(MAX) instead of NVarChar(200)

问题描述

我想知道在定义nvarchar（max）类型的列时是否有任何缺点，而不是给它一个（更小的）最大大小。

I am wondering if there is any disadvantage on defining a column of type nvarchar(max) instead of giving it a (smaller) maximum size.

我在某处阅读如果列值超过4？KB，剩余数据将被添加到溢出区域，这没关系。

I read somewhere that if the column value has more than 4?KB the remaining data will be added to an "overflow" area, which is ok.

我正在创建一个表格大部分时间文本都是几行，但我想知道设置下限是否有任何优势，然后添加验证以避免违反该限制。

I'm creating a table where most of the time the text will be of a few lines, but I was wondering if there's any advantage in setting a lower limit and then adding a validation to avoid breaking that limit.

使用nvarchar（max）列创建索引是否有任何限制，或者是否需要在大小限制上添加限制？

Is there any restriction on the creation of indexes with nvarchar(max) column, or anything that pays for having to add the restriction on the size limit?

谢谢！

推荐答案

严格来说， MAX 类型总是比非MAX类型，请参阅 varc的性能比较har（max）vs. varchar（N）。但是这种差异在实践中永远不可见，它只会在由IO驱动的整体性能中产生噪音。

Strictly speaking the MAX types will always be a bit slower than the non-MAX types, see Performance comparison of varchar(max) vs. varchar(N). But this difference is never visible in practice, where it just becomes noise in the overall performance driven by IO.

您的主要关注点不应该是MAX与非MAX。你应该关注这个问题这个列可能需要存储超过8000个字节吗？如果答案是肯定的，即使是非常不太可能，那么答案是显而易见：使用MAX类型，稍后将此列转换为MAX类型的痛苦不值得非MAX类型的次要性能优势。

Your main concern should not be performance of MAX vs. non-MAX. You should be concerned with the question it will be possible that this column will have to store more than 8000 bytes? If the answer is yes, even by if is a very very unlikely yes, then the answer is obvious: use a MAX type, the pain to convert this column later to a MAX type is not worth the minor performance benefit of non-MAX types.

其他问题（可能性）为了索引该列，具有MAX列的表的ONLINE索引操作的不可用性已经由Denis的回答解决。

Other concerns (possibility to index that column, unavailability of ONLINE index operations for tables with MAX columns) were already addressed by Denis' answer.

BTW，剩余4KB以上的列的信息溢出区域中的数据是错误的。正确的信息在表和索引组织中：

BTW, the information about the columns over 4KB having remaining data in an overflow area is wrong. The correct information is in Table and Index Organization:

ROW_OVERFLOW_DATA分配单元

对于表$使用的每个分区b $ b（堆或聚簇表），索引或
索引视图，有一个
ROW_OVERFLOW_DATA分配单元。
此分配单元包含零（0）
页，直到
IN_ROW_DATA中具有变量
长度列（varchar，nvarchar，
varbinary或sql_variant）的数据行为止分配单元超过
8 KB行大小限制。当达到
大小限制时，SQL Server
将具有该行最大
宽度的列移动到
ROW_OVERFLOW_DATA分配单元中的页面。原始页面上保留了一个
24字节指向此行外数据
的指针。

For every partition used by a table (heap or clustered table), index, or indexed view, there is one ROW_OVERFLOW_DATA allocation unit. This allocation unit contains zero (0) pages until a data row with variable length columns (varchar, nvarchar, varbinary, or sql_variant) in the IN_ROW_DATA allocation unit exceeds the 8 KB row size limit. When the size limitation is reached, SQL Server moves the column with the largest width from that row to a page in the ROW_OVERFLOW_DATA allocation unit. A 24-byte pointer to this off-row data is maintained on the original page.

所以不是超过4KB的列，是不适合页面上可用空间的行，而不是'剩余'，是整个列。

So is not columns over 4KB, is rows that don't fit in the free space on the page, and is not the 'remaining', is the entire column.

这篇关于SQL性能：使用NVarchar（MAX）而不是NVarChar（200）是否有任何性能损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！