SQL 总是使用 nvarchar(MAX) 有什么缺点吗？

Are there any disadvantages to always using nvarchar(MAX)?

提问by stucampbell

In SQL Server 2005, are there any disadvantages to making all character fields nvarchar(MAX) rather than specifying a length explicitly, e.g. nvarchar(255)? (Apart from the obvious one that you aren't able to limit the field length at the database level)

在 SQL Server 2005 中，将所有字符字段设为 nvarchar(MAX) 而不是显式指定长度，例如 nvarchar(255) 是否有任何缺点？（除了您无法在数据库级别限制字段长度这一显而易见的问题）

采纳答案by David Kreps

Same question was asked on MSDN Forums:

MSDN论坛上也有人问过同样的问题：

• Varchar(max) vs Varchar(255)

• Varchar(max) 与 Varchar(255)

From the original post (much more information there):

来自原始帖子（那里有更多信息）：

When you store data to a VARCHAR(N) column, the values are physically stored in the same way. But when you store it to a VARCHAR(MAX) column, behind the screen the data is handled as a TEXT value. So there is some additional processing needed when dealing with a VARCHAR(MAX) value. (only if the size exceeds 8000)

VARCHAR(MAX) or NVARCHAR(MAX) is considered as a 'large value type'. Large value types are usually stored 'out of row'. It means that the data row will have a pointer to another location where the 'large value' is stored...

当您将数据存储到 VARCHAR(N) 列时，值的物理存储方式相同。但是，当您将其存储到 VARCHAR(MAX) 列时，屏幕后面的数据将作为 TEXT 值处理。因此在处理 VARCHAR(MAX) 值时需要一些额外的处理。（仅当大小超过 8000 时）

VARCHAR(MAX) 或 NVARCHAR(MAX) 被视为“大值类型”。大值类型通常存储在“行外”。这意味着数据行将有一个指向存储“大值”的另一个位置的指针......

回答by alexmac

It's a fair question and he did state apart from the obvious…

这是一个公平的问题，除了显而易见的……

Disadvantages could include:

缺点可能包括：

Performance implications Query optimizer uses field size to determine most efficent exectution plan

性能影响 查询优化器使用字段大小来确定最有效的执行计划

"1. The space alloction in extends and pages of the database are flexible. Thus when adding information to the field using update, your database would have to create a pointer if the new data is longer than the previous inserted. This the database files would become fragmented = lower performance in almost everything, from index to delete, update and inserts. " http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

“1. 数据库的扩展和页面中的空间分配是灵活的。因此，当使用更新向字段添加信息时，如果新数据比之前插入的数据长，您的数据库将不得不创建一个指针。这将是数据库文件变得支离破碎 = 从索引到删除、更新和插入，几乎所有方面的性能都会降低。” http://sqlblogcasts.com/blogs/simons/archive/2006/02/28/Why-use-anything-but-varchar_2800_max_2900_.aspx

Integration implications - hard for other systems to know how to integrate with your database Unpredictable growth of data Possible security issues e.g. you could crash a system by taking up all disk space

集成影响 - 其他系统很难知道如何与您的数据库集成 不可预测的数据增长 可能的安全问题，例如您可能因占用所有磁盘空间而导致系统崩溃

There is good article here: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

这里有好文章：http: //searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html

回答by Tim Abell

Based on the link provided in the accepted answer it appears that:

根据接受的答案中提供的链接，似乎：

1. 100 characters stored in an nvarchar(MAX)field will be stored no different to 100 characters in an nvarchar(100)field - the data will be stored inline and you will not have the overhead of reading and writing data 'out of row'. So no worries there.

2. If the size is greater than 4000 the data would be stored 'out of row' automatically, which is what you would want. So no worries there either.

1. nvarchar(MAX)字段中存储的100 个字符将与字段中的 100 个字符存储相同nvarchar(100)- 数据将内联存储，您将不会有“行外”读取和写入数据的开销。所以不用担心。

2. 如果大小大于 4000，则数据将自动存储为“行外”，这正是您想要的。所以也不用担心。

However...

然而...

1. You cannot create an index on an nvarchar(MAX)column. You can use full-text indexing, but you cannot create an index on the column to improve query performance. For me, this seals the deal...it is a definite disadvantage to always use nvarchar(MAX).

3. 您不能在nvarchar(MAX)列上创建索引。您可以使用全文索引，但不能在列上创建索引来提高查询性能。对我来说，这决定了交易......总是使用 nvarchar(MAX) 是一个明显的缺点。

Conclusion:

结论：

If you want a kind of "universal string length" throughout your whole database, which can be indexed and which will not waste space and access time, then you could use nvarchar(4000).

如果您想要一种贯穿整个数据库的“通用字符串长度”，它可以被索引并且不会浪费空间和访问时间，那么您可以使用nvarchar(4000).

回答by Bill Karwin

Sometimes you want the data type to enforce some sense on the data in it.

有时您希望数据类型对其中的数据施加某种意义。

Say for example you have a column that really shouldn't be longer than, say, 20 characters. If you define that column as VARCHAR(MAX), some rogue application could insert a long string into it and you'd never know, or have any way of preventing it.

例如，假设您有一列的长度不应超过 20 个字符。如果您将该列定义为 VARCHAR(MAX)，则某些流氓应用程序可能会在其中插入一个长字符串，而您永远不会知道或有任何方法来阻止它。

The next time your application uses that string, under the assumption that the length of the string is modest and reasonable for the domain it represents, you will experience an unpredictable and confusing result.

下次您的应用程序使用该字符串时，假设该字符串的长度对于它所代表的域来说是适中且合理的，您将遇到不可预测且令人困惑的结果。

回答by QMaster

I checked some articles and find useful test script from this: http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspxThen changed it to compare between NVARCHAR(10) vs NVARCHAR(4000) vs NVARCHAR(MAX) and I don't find speed difference when using specified numbers but when using MAX. You can test by yourself. Hope This help.

我检查了一些文章并从中找到了有用的测试脚本：http: //www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx然后将其更改为比较 NVARCHAR(10) vs NVARCHAR(4000) vs NVARCHAR(MAX ) 并且我在使用指定数字时没有发现速度差异，但在使用 MAX 时没有发现速度差异。你可以自己测试。希望这有帮助。

SET NOCOUNT ON;

--===== Test Variable Assignment 1,000,000 times using NVARCHAR(10)
DECLARE @SomeString NVARCHAR(10),
        @StartTime DATETIME;
--=====         
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = 'ABC'
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime='10', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(4000)
DECLARE @SomeString NVARCHAR(4000),
        @StartTime DATETIME;
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = 'ABC'
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime='4000', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO
--===== Test Variable Assignment 1,000,000 times using NVARCHAR(MAX)
DECLARE @SomeString NVARCHAR(MAX),
        @StartTime DATETIME;
 SELECT @startTime = GETDATE();
 SELECT TOP 1000000
        @SomeString = 'ABC'
   FROM master.sys.all_columns ac1,
        master.sys.all_columns ac2;
 SELECT testTime='MAX', Duration = DATEDIFF(ms,@StartTime,GETDATE());
GO

回答by Alex

Think of it as just another safety level. You can design your table without foreign key relationships - perfectly valid - and ensure existence of associated entities entirely on the business layer. However, foreign keys are considered good design practice because they add another constraint level in case something messes up on the business layer. Same goes for field size limitation and not using varchar MAX.

将其视为另一个安全级别。您可以设计没有外键关系的表 - 完全有效 - 并确保完全在业务层上存在关联实体。然而，外键被认为是很好的设计实践，因为它们添加了另一个约束级别，以防业务层出现问题。字段大小限制和不使用 varchar MAX 也是如此。

回答by Nick Kavadias

A reason NOT to use max or text fields is that you cannot perform online index rebuildsi.e. REBUILD WITH ONLINE= ON even with SQL Server Enterprise Edition.

不使用 max 或 text 字段的一个原因是您无法执行联机索引重建，即 REBUILD WITH ONLINE= ON 即使使用 SQL Server Enterprise Edition。

回答by Tom H

The job of the database is to store data so that it can be used by the enterprise. Part of making that data useful is ensuring that it is meaningful. Allowing someone to enter an unlimited number of characters for their first name isn't ensuring meaningful data.

数据库的工作是存储数据，以便企业可以使用。使这些数据有用的一部分是确保它是有意义的。允许某人为其名字输入无限数量的字符并不能确保数据有意义。

Building these constraints into the business layer is a good idea, but that doesn't ensure that the database will remain intact. The only way to guarantee that the data rules are not violated is to enforce them at the lowest level possible in the database.

将这些约束构建到业务层中是一个好主意，但这并不能确保数据库将保持完整。保证不违反数据规则的唯一方法是在数据库中尽可能低的级别强制执行它们。

回答by RichardOD

Bad idea when you know the field will be in a set range- 5 to 10 character for example. I think I'd only use max if I wasn't sure what the length would be. For example a telephone number would never be more than a certain number of characters.

当您知道该字段将在设定范围内时（例如 5 到 10 个字符），这是个坏主意。我想如果我不确定长度是多少，我只会使用 max 。例如，电话号码永远不会超过一定数量的字符。

Can you honestly say you are that uncertain about the approximate length requirements for every field in your table?

老实说，您对表中每个字段的大致长度要求不确定吗？

I do get your point though- there are some fields I'd certainly consider using varchar(max).

不过我确实明白你的意思——有些字段我肯定会考虑使用 varchar(max)。

Interestingly the MSDN docssum it up pretty well:

有趣的是，MSDN 文档总结得很好：

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.

当列数据条目的大小差异很大时，请使用 varchar。当列数据条目的大小差异很大，并且大小可能超过 8,000 字节时，请使用 varchar(max)。

There's an interesting discussion on the issue here.

关于这个问题这里有一个有趣的讨论。

回答by mattruma

The only problem I found was that we develop our applications on SQL Server 2005, and in one instance, we have to support SQL Server 2000. I just learned, the hard waythat SQL Server 2000 doesn't like the MAX option for varchar or nvarchar.

我发现的唯一的问题是，我们发展我们的SQL Server 2005上的应用程序，并且在一个情况下，我们要支持SQL Server 2000中我了解到，该硬盘的方式是SQL Server 2000不像VARCHAR或MAX选无功字符。