在SQL Server 2005中，使所有字符字段都为nvarchar（MAX）而不是显式指定长度（例如， nvarchar（255）？ （除了显而易见的那一句，您无法在数据库级别限制字段长度）

评论

我只是不明白为什么您要让别人输入8000多个字符的名称。
相同的逻辑可以应用于编程语言。为什么不回到我们所有数据的旧VB6版本呢？我认为在多个地方拥有制衡并不一定是坏事。
检查此：stackoverflow.com/questions/2009694/…
您的更新应该是该问题的答案。
由于原始作者尚未完成，因此将问题答案移至正确答案。 stackoverflow.com/a/35177895/10245我认为7年的时间足够:-)

#1 楼

在MSDN论坛上提出了相同的问题：

Varchar（max）vs Varchar（255）

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

当您将数据存储到VARCHAR（N）列时，值以相同的方式物理存储。但是，当您将其存储到VARCHAR（MAX）列时，在屏幕后面会将数据作为TEXT值处理。因此，在处理VARCHAR（MAX）值时，需要一些其他处理。 （仅当大小超过8000时）。
VARCHAR（MAX）或NVARCHAR（MAX）被视为“大值类型”。大值类型通常存储在“行外”。这意味着数据行将具有指向另一个存储“大值”的位置的指针...


评论

所以问题应该是，使用N / VARCHAR（MAX）和N / TEXT有区别吗？

–未切片
08/09/29在12:29
如果我没记错的话，难道它们不是仅在大小超过8k时才存储在行外吗？

–山姆·舒特（Sam Schutte）
09年3月2日在15:33
我将答案读为“不，使用N / VARCHAR（MAX）没有缺点”，因为“只有当大小超过8000时才有”附加处理。因此，仅在必要时才产生成本，并且数据库的限制较少。我读错了吗？似乎您几乎总是想要N / VARCHAR（MAX）而不是N / VARCHAR（1-8000）...

–肯特·布加（Kent Boogaart）
2010-2-20在11:38


上方的无效链接-MSDN上该问题的工作链接为social.msdn.microsoft.com/Forums/en-US/sqlgetstarted/thread / ...

–贾格德
10 Mar 8 '10在22:01
不幸的是，这个答案有很多问题。它使8k边界看起来像一个魔术数字，不是真的，该值基于更多因素（包括sp_tableoptions：msdn.microsoft.com/en-us/library/ms173530.aspx）被推出行外。 VARCHAR（255）类型也可以从行中移出，其中提到的“开销”对于MAX和255可能是完全相同的。当MAX类型与TEXT类型变得不同时，它将MAX类型与TEXT类型进行比较（完全不同的API进行操作，不同的存储空间等）。它没有提到实际的差异：没有索引，没有对MAX类型的在线操作

–雷木斯·鲁萨努（Remus Rusanu）
2011年8月25日15:53

#2 楼

这是一个很公平的问题，除了明显的问题外，他确实做了陈述。

缺点可能包括：

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

“ 1.在扩展名和数据库页中的空间分配是灵活的。因此，当使用更新将信息添加到字段时，如果新数据的长度比数据库长，则数据库将必须创建一个指针。数据库文件将变得碎片化–从索引到删除，更新和插入，几乎所有内容的性能都下降。“
http://sqlblogcasts.com/blogs/simons/archive/2006/02/28 /Why-use-anything-but-varchar_2800_max_2900_.aspx

集成的含义-其他系统很难知道如何与数据库集成
数据的不可预测的增长
可能的安全问题例如您可能会占用所有磁盘空间来使系统崩溃

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

评论

+1涉及集成和安全性。这些是大多数其他答案谈论绩效时要考虑的原始角度。与集成相关的问题是，如果所有列均为varchar（max），则使用元数据提供合理的默认控件大小的任何工具（例如报表编写者或表单设计者）都将需要进行大量工作。

–幻灭
2014年7月7日在13:09
据我所知，通过数据库进行集成是最荒谬的事情。如果只是一次导入，则可以先通过LEN函数检查数据。

– Maxim
16-10-28在21:01

#3 楼

根据接受的答案中提供的链接，看来：在nvarchar(MAX)字段中存储的100个字符将与在nvarchar(100)字段中存储的100个字符相同-数据将被存储内联，您将无需在“行外”读写数据。所以在那里没有后顾之忧。
如果大小大于4000，则数据将自动“存储在行外”，这正是您想要的。因此，那里也不用担心。

但是...



不能在nvarchar(MAX)列上创建索引。您可以使用全文索引，但是不能在列上创建索引以提高查询性能。对我来说，这很划算...始终使用nvarchar（MAX）是绝对不利的。

结论：

如果您想要一种“通用字符串”整个数据库中的“长度”，可以对其进行索引并且不会浪费空间和访问时间，则可以使用nvarchar(4000)。

评论

仅供参考，这是对原始问题的修改，应将其发布为答案

–蒂姆·阿贝尔
16-2-3在13:04
谢谢，对我来说，这是最终答案。我问过自己同样的问题-为什么不一直使用nvarchar（max）-像C＃中的字符串一样？ -但第3点）（索引问题）给出了答案。

– SQL警察
16-3-13在15:28
添加了修改。作为一种“通用字符串长度”，您可以始终使用nvarchar（4000）

– SQL警察
16-3-13在15:48
@SQLGeorge请参见Martin Smith的出色答案，即声明比以往任何时候都宽的列对查询性能的影响

–billinkc
16年3月13日在15:55
@billinkc谢谢，那是一篇很棒的文章。好的，因此大小确实会影响性能。我将再次编辑答案。

– SQL警察
16年3月13日在16:09

#4 楼

有时，您希望数据类型对其中的数据施加某种意义。例如，您有一列的长度实际上不应超过20个字符。如果将该列定义为VARCHAR（MAX），则某些流氓应用程序可能会在其中插入一个长字符串，而您永远不会知道，或者有任何防止它的方法。

下一次您的应用程序使用该字符串时，假设该字符串的长度对于它表示的域而言是适度且合理的，则您将遇到无法预测且令人困惑的结果。

评论

我同意这一点以及其他一些评论，但我仍然坚持认为这是业务层的责任。到达数据库层时，无论长度多么荒谬，它都应该向致敬并存储值。我认为这里真正发挥作用的是，我认为大约90％的开发人员指定varchar（255）时，他的意图并不是真正的255个字符，而是一些未指定的中间长度值。考虑到我的数据库中的过大值与无法预料的异常之间的平衡，我将采用大值。

–克里斯·贝伦斯（Chris B. Behrens）
09年6月8日在19:55
如果他们指定VARCHAR（255）来指示一些未知的长度，那是他们没有正确研究他们正在设计的内容的错误。解决方案是让开发人员完成工作，而不是让数据库允许不合理的值。

–汤姆·H
09年6月8日在20:45
对作者没有帮助。他明确排除了您回答的问题。

–usr
09-10-10在20:40
// @克里斯·B·贝伦斯：我不同意；数据库模式是业务逻辑的一部分。表，关系，字段和数据类型的选择都是业务逻辑-使用RDBMS强制执行此业务逻辑的规则是值得的。由于一个原因，很少只有一个应用程序层访问数据库。例如，您可能具有绕过主要业务层的数据导入和提取工具，这意味着您确实需要数据库来执行规则。

–文斯·鲍德伦（Vince Bowdren）
13年4月29日在8:31


如果您不需要或确实不希望存储长字符串，则最好在数据上增强意义。例如，如果存储一个PostCode字段，您是否允许某人输入成百上千个字符，最大长度应为10个。-应该验证所有级别，客户端，业务层和数据库的最大大小。如果使用诸如C＃和Entity Framework之类的“模型优先”方法，则可以在模型上定义maxsize并将其应用于数据库，业务逻辑和客户端验证（例如jquery验证）。仅在确实需要时使用nvarchar（max）

– Peter Kerr
17年2月10日在15:20

#5 楼

我检查了一些文章，并从中找到了有用的测试脚本：http://www.sqlservercentral.com/Forums/Topic1480639-1292-1.aspx
然后将其更改为在NVARCHAR（10）与NVARCHAR（4000）之间进行比较与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


评论

那很有意思。在我的盒子上，最大MAX似乎慢了4倍。

– stucampbell
2014年10月3日，11：20
SQL Server 2012的新结果：10比4k慢两倍，MAX比4k慢5.5倍。

– Cassandrad
16年2月10日在16:48
大多数时间是从varchar隐式转换为nvarchar（max）。试试这个：DECLARE \ @SomeString NVARCHAR（MAX），\ @abc NVARCHAR（max）= N'ABC'，\ @StartTime DATETIME; SELECT @startTime = GETDATE（）; SELECT TOP 1000000 \ @SomeString = \ @abc from master.sys.all_columns ac1，master.sys.all_columns ac2; SELECT testTime ='MAX'，持续时间= DATEDIFF（ms，\ @ StartTime，GETDATE（））;必须在变量前插入\才能发布。

– Kvasi
16-09-30在9:23


SSD上的SQL Server 2014：150、156、716（10、4000，MAX）。

– Maxim
16-10-28在21:14
感谢您为此次讨论添加一些实数。我们经常忘记构建测试用例是了解问题的最快方法。

– David C
16-11-22在15:10

#6 楼

将其视为另一个安全级别。您可以设计没有外键关系的表-完全有效-并确保关联实体完全存在于业务层上。但是，外键被认为是良好的设计习惯，因为外键在业务层发生混乱时会增加另一个约束级别。字段大小限制也是如此，并且不使用varchar MAX。

#7 楼

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

评论

TEXT字段类型也有相同的限制，因此您仍应使用VARCHAR（MAX）而不是TEXT。

–剃须刀
09年6月25日在17:39
因此，我们无法重建聚簇索引。在将列提取到自己的表中之前，这花费了我们大量的磁盘空间（我们无法将表锁定超过7秒）

–Choco Smith
15年1月13日在13:32

#8 楼

我发现的唯一问题是，我们在SQL Server 2005上开发应用程序，并且在一个实例中，我们必须支持SQL Server2000。我刚刚了解到，SQL Server 2000不喜欢varchar或MAX选项的困难方式。 nvarchar。

评论

那么，为什么不仅仅在最低的公分母上发展呢？

– Binki
16年1月27日在20:13

#9 楼

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

老实说，您不确定表中每个字段的近似长度要求是否不确定吗？

不过，我的意思是正确的-我肯定会考虑使用varchar（max）的一些字段。

有趣的是，MSDN文档对其进行了很好的总结：


当
列数据条目的大小相差很大时，请使用varchar。
当
列数据条目的大小相差很大时，请使用varchar（max）。大小可能超过8,000个字节。


这里有一个有趣的讨论。

评论

对于电话号码之类的东西，我更愿意使用char字段而不是varchar。只要您保持存储中的标准，并且不必担心来自不同国家/地区的电话号码，就永远不需要变量字段来存储电话号码（10个不带任何格式）或邮政编码（5个）或9-10（如果加上最后四位数字），等等。

–TheTXI
09年6月8日在19:41
我指的是长度可能会有所不同的电话号码。也许我应该把这个答案。任何固定长度的东西我都会使用char字段。

–RichardOD
09年6月8日在19:55
或者，也许我应该在我的评论中说nchar或char。 :-)

–RichardOD
09年6月8日在19:59
电话号码中的字符数几乎是一项业务要求。如果要求您将国际标准代码与数字一起存储，则可能会超过10。或者，世界上某些地区的电话号码可能有10位数以上。想象一下从IPV4到IPV6过渡的情况。没有人会争辩说，在IPV4的旧版本中，我们需要超过12位数字。如果IPV6成为流行，它可能无法保持良好状态。这又是一段时间内的业务规则更改。可以这么说，变化是我们唯一可以期待的事情：)

–铅笔
09年8月10日在22:39
假设您知道电话号码字段中可以包含多少个字符或它们将成为哪种字符，请小心。除非系统使用该数据实际拨出（在这种情况下您必须严格遵守格式），否则用户可能会合法地在其中合理地放入长字符串，例如“ 0123 456 78910要求接待分机45，然后转移到James”。

–文斯·鲍德伦（Vince Bowdren）
13年4月29日在8:21

#10 楼

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

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

评论

IMO，数据长度限制完全基于业务规则，随着应用程序的增长，业务规则可能会在一段时间内发生变化。在业务逻辑上更改业务规则比在数据库级别上更容易。因此，我认为数据库应具有足够的灵活性，不应与诸如最大允许的名字长度之类的业务规则捆绑在一起，这在很大程度上取决于用户所居住的世界部分。

–铅笔
09年8月10日在22:35

#11 楼

如上所述，这主要是存储和性能之间的折衷。至少在大多数情况下。

但是，在选择n / varchar（Max）而不是n / varchar（n）时，还应该考虑至少一个其他因素。是否要对数据建立索引（例如姓氏）？由于MAX定义被认为是LOB，因此任何定义为MAX的内容均不可用于索引。而且没有索引，任何涉及WHERE子句中的谓词数据的查找都将被强制进行全表扫描，这对于数据查找而言可能是最差的性能。

#12 楼

一个问题是，如果您必须使用多个版本的SQL Server，则MAX不会始终有效。因此，如果您正在使用旧版DB或涉及多个版本的任何其他情况，则最好非常小心。

评论

我认为OP的潜行假设是他将完全处理2005+实例，并且他的应用程序不需要在2000（或更低版本）的版本上运行。不过，如果需要支持旧版本，我完全同意！

–约翰·鲁迪（John Rudy）
2009年6月8日19:30
约翰·鲁迪（John Rudy）：我想是这样的，我只是知道我在自己不打算去的时候遇到了那些障碍。

–TheTXI
2009年6月8日19:31
实际上，由于SQL CE 4不支持MAX列类型，这仍然是现代问题的普遍问题，因此互操作性很麻烦。

– JohnC
2012年4月5日在21:10

#13 楼

1）处理nvarchar（max）与nvarchar（n）时，SQL服务器将不得不利用更多的资源（分配的内存和cpu时间），其中n是特定于该字段的数字。

2）这对性能意味着什么？

在SQL Server 2005上，我从具有15个nvarchar（max）列的表中查询了13,000行数据。 br />我反复对查询进行计时，然后将列更改为nvarchar（255）或更小。

优化之前的查询平均为2.0858秒。更改后的查询平均返回1.90秒。与基本的select *查询相比，这大约提高了184毫秒。那是8.8％的改善。

3）我的结果与其他几篇表明性能存在差异的文章一致。根据您的数据库和查询，改进百分比可能会有所不同。如果您没有很多并发用户或很多记录，那么性能差异对您来说就不是问题。但是，随着更多记录和并发用户的增加，性能差异也会增加。

#14 楼

我有一个udf，它会填充字符串并将输出放入varchar（max）。如果直接使用此方法而不是将其转换为要调整的色谱柱的合适尺寸，则性能会很差。我最终将udf放到带有大音符的任意长度上，而不是依靠udf的所有调用者将字符串重新投射为较小的大小。

#15 楼

旧版系统支持。如果您有一个正在使用数据的系统，并且预期长度是一定的，则数据库是实施长度的好地方。这不是理想的选择，但是传统系统有时并不理想。 = P

#16 楼

如果连续的所有数据（对于所有列）绝不会占用8000个字符或更少的字符，则数据层的设计应强制执行此操作。

数据库引擎在保留所有内容方面效率更高超过了Blob存储空间。您可以限制行越小越好。您可以在页面中填充的行越多越好。当数据库必须访问较少的页面时，其性能会更好。

#17 楼

我的测试表明选择时存在差异。

CREATE TABLE t4000 (a NVARCHAR(4000) NULL);

CREATE TABLE tmax (a NVARCHAR(MAX) NULL);

DECLARE @abc4 NVARCHAR(4000) = N'ABC';

INSERT INTO t4000
SELECT TOP 1000000 @abc4
    FROM
    master.sys.all_columns ac1,
    master.sys.all_columns ac2;

DECLARE @abc NVARCHAR(MAX) = N'ABC';

INSERT INTO tmax
SELECT TOP 1000000 @abc
    FROM
    master.sys.all_columns ac1,
    master.sys.all_columns ac2;

SET STATISTICS TIME ON;
SET STATISTICS IO ON;

SELECT * FROM dbo.t4000;
SELECT * FROM dbo.tmax;


#18 楼

有趣的链接：为什么可以使用TEXT时为什么要使用VARCHAR？

是关于PostgreSQL和MySQL的，所以性能分析有所不同，但是“显式”的逻辑仍然成立：为什么要强迫自己总是担心关于一些与时间相关的事情？如果您将电子邮件地址保存到变量，则将使用“字符串”而不是“限于80个字符的字符串”。

评论

这类似于认为您不应该具有检查约束来确保一个人的年龄不是负数。

–乔纳森·艾伦
17年2月14日在19:15
我发现数据正确性和性能优化之间存在差异。

–orip
17年2月15日在7:04

#19 楼

我可以看到的主要缺点是，假设您拥有以下内容：

哪个可以为您提供有关UI所需数据的最多信息？

此

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](MAX) NULL,
                [CompanyName] [nvarchar](MAX) NOT NULL,
                [FirstName] [nvarchar](MAX) NOT NULL,
                [LastName] [nvarchar](MAX) NOT NULL,
                [ADDRESS] [nvarchar](MAX) NOT NULL,
                [CITY] [nvarchar](MAX) NOT NULL,
                [County] [nvarchar](MAX) NOT NULL,
                [STATE] [nvarchar](MAX) NOT NULL,
                [ZIP] [nvarchar](MAX) NOT NULL,
                [PHONE] [nvarchar](MAX) NOT NULL,
                [COUNTRY] [nvarchar](MAX) NOT NULL,
                [NPA] [nvarchar](MAX) NULL,
                [NXX] [nvarchar](MAX) NULL,
                [XXXX] [nvarchar](MAX) NULL,
                [CurrentRecord] [nvarchar](MAX) NULL,
                [TotalCount] [nvarchar](MAX) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]


或者这吗？

            CREATE TABLE [dbo].[BusData](
                [ID] [int] IDENTITY(1,1) NOT NULL,
                [RecordId] [nvarchar](50) NULL,
                [CompanyName] [nvarchar](50) NOT NULL,
                [FirstName] [nvarchar](50) NOT NULL,
                [LastName] [nvarchar](50) NOT NULL,
                [ADDRESS] [nvarchar](50) NOT NULL,
                [CITY] [nvarchar](50) NOT NULL,
                [County] [nvarchar](50) NOT NULL,
                [STATE] [nvarchar](2) NOT NULL,
                [ZIP] [nvarchar](16) NOT NULL,
                [PHONE] [nvarchar](18) NOT NULL,
                [COUNTRY] [nvarchar](50) NOT NULL,
                [NPA] [nvarchar](3) NULL,
                [NXX] [nvarchar](3) NULL,
                [XXXX] [nvarchar](4) NULL,
                [CurrentRecord] [nvarchar](50) NULL,
                [TotalCount] [nvarchar](50) NULL,
                [Status] [int] NOT NULL,
                [ChangeDate] [datetime] NOT NULL
            ) ON [PRIMARY]


评论

我希望业务逻辑告诉我公司名称最多可以包含50个字符，而不是依赖数据库表获取该信息。

–可汗
2012年4月17日在17:40
我同意杰夫的观点。我认为持久性存储不是定义业务规则的正确位置。而且在分层体系结构中，您的UI甚至都不了解持久层。

– stucampbell
2012年4月18日14:40在
当然，除非您使用的值限制为特定大小，例如国家/地区的ISO代码。

–本
2012年4月28日在17:13
与表def有什么关系？您仍然可以具有业务逻辑。我认为您的观点与表格的设计无关。如果您仍然想在业务层中设计某种定义，那就去吧。尽管更有意义，但无论如何还是在业务层中使用存储的proc；没有表def？

–卡洛斯·马丁尼
2012年5月8日20:55


似乎不受欢迎，但我同意carlos的看法，如果数据库设置了最大大小，那么您可以在其上可能要处理的所有层上感到舒服。如果您有多个系统要写入数据库，那么这尤其重要。

–蒂姆·阿贝尔
16年2月3日，12：05

#20 楼

一个缺点是您将围绕一个不可预测的变量进行设计，并且您可能会忽略而不是利用内部SQL Server数据结构，而该内部SQL Server数据结构由行，页和范围逐渐组成。

这让我想到了C语言中的数据结构对齐方式，并且意识到对齐方式通常被认为是一件好事（TM）。相似的想法，不同的上下文。

页面和范围的MSDN页面

行溢出数据的MSDN页面

#21 楼

首先我考虑了一下，然后又想了一遍。这涉及性能，但是同样可以作为一种文档形式来了解字段的实际大小。当该数据库位于更大的生态系统中时，它确实会强制执行。我认为关键是要允许，但只能在合理范围内。

好吧，这只是我对业务和数据层逻辑问题的看法。这取决于，如果您的数据库是共享业务逻辑的系统之间的共享资源，那么执行此逻辑当然是很自然的地方，但这不是执行此逻辑的最佳方式，最佳方式是提供一个API，这允许被测试的交互，并将业务逻辑保持在其所属的位置，使系统保持解耦，使系统中的各层保持解耦。但是，如果您的数据库仅应服务于一个应用程序，那么让AGILE来思考一下，现在到底是什么？现在设计。如果以及何时需要这种访问，请提供对该数据的API。

显然，这只是理想选择，如果您使用的是现有系统，则可能是至少在短期内需要以不同的方式进行操作。

#22 楼

这将导致性能问题，尽管如果数据库很小，则可能永远不会导致任何实际问题。每个记录将占用硬盘驱动器上的更多空间，并且如果您一次搜索大量记录，则数据库将需要读取磁盘的更多扇区。例如，一条小记录可以容纳50个扇区，大记录可以容纳5个扇区。使用大记录，您需要从磁盘读取10倍的数据。

评论

-1。存储在nvarchar（max）列中的长度为100的字符串所占用的磁盘空间不会比nvarchar（100）列中的磁盘空间更多。

–马丁·史密斯
11年4月22日在0:43
如果存储的数据的大小更大，那么您所描述的是正确的，但是这个问题是关于数据类型是否影响性能或其他考虑因素。

–user565869
2014年12月23日在18:13

#23 楼

这将使屏幕设计更加困难，因为您将无法再预测控件的宽度。