总是使用nvarchar(max)有什么缺点吗?

在msdn论坛上也提出了同样的问题:

• Varchar(max) vs Varchar(255)

从原来的帖子(更多信息在那里):

当您将数据存储到varchar(n)列时,这些值的物理存储方式相同。但当您将其存储到varchar(max)列时,在屏幕后面的数据将作为文本值处理。因此,在处理varchar(max)值时需要一些额外的处理。(仅当尺寸超过8000时)

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

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

缺点可能包括:

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

“1.数据库扩展和页面中的空间分配是灵活的。因此,当使用update向字段添加信息时,如果新数据比先前插入的数据长,则数据库必须创建一个指针。这样,数据库文件将变得支离破碎=从索引到删除、更新和插入,几乎所有内容的性能都会降低。” 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

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

比如说,你有一个列,实际上不应该超过20个字符。如果您将该列定义为varchar(max),那么一些恶意应用程序可能会在其中插入一个长字符串,您永远不会知道,或者有任何方法可以阻止它。

下一次应用程序使用该字符串时,假设字符串的长度对于它所表示的域来说是适当和合理的,那么您将遇到一个不可预测和混乱的结果。

根据接受答案中提供的链接,似乎:

1. 100个字符存储在 nvarchar(MAX) 字段将存储在 nvarchar(100) 字段-数据将以内联方式存储,您将没有“行外”读取和写入数据的开销。所以不用担心。

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

然而。。。

3. 不能在上创建索引 NVARCHAR(MAX) 列。可以使用全文索引,但不能在列上创建索引以提高查询性能。对我来说,这意味着交易……总是使用nvarchar(max)是一个明显的缺点。

结论:

如果您希望在整个数据库中有一种“通用字符串长度”,它可以被索引,并且不会浪费空间和访问时间,那么您可以使用 nvarchar(4000) .

我查阅了一些文章,从中找到了有用的测试脚本: 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

把它看作是另一个安全级别。您可以设计不带外键关系的表(完全有效),并确保关联实体完全存在于业务层上。但是,外键被认为是很好的设计实践,因为它们添加了另一个约束级别,以防业务层出现混乱。同样适用于字段大小限制,而不使用varchar max。

不使用max或text字段的原因是您无法执行 online index rebuilds 即,即使使用SQL Server Enterprise Edition,也使用online=on重新生成。

我发现的唯一问题是我们在SQL Server 2005上开发应用程序,在一个实例中,我们必须支持SQL Server 2000。我刚学会 硬路 SQL Server 2000不喜欢varchar或nvarchar的max选项。

当您知道字段将在一个设置范围内时,这是个坏主意,例如-5到10个字符。我想我只有在不确定长度的情况下才使用max。例如,电话号码永远不会超过特定的字符数。

您能诚实地说,您对表中每个字段的近似长度要求有那么不确定吗?

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

有趣的是 MSDN docs 总结得很好:

当 列数据条目变化很大。 当 列数据条目变化很大, 大小可能超过8000字节。

有 an interesting discussion on the issue here .

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

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

一个问题是,如果您必须使用多个版本的SQL Server,那么max并不总是有效的。因此,如果您使用的是遗留数据库或任何其他涉及多个版本的情况,那么最好非常小心。

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

但是,在选择n/varchar(max)而不是n/varchar(n)时,至少还有一个其他因素需要考虑。数据是否要被索引(例如,姓氏)?由于max定义被视为LOB,因此任何定义为max的内容都不可用于索引。在没有索引的情况下,任何涉及到WHERE子句中的数据作为谓词的查找都将被强制进行完整的表扫描,这对于数据查找来说是最糟糕的性能。

1)在处理nvarchar(max)和nvarchar(n)时,SQL Server必须使用更多的资源(分配的内存和CPU时间),其中n是特定于字段的数字。

2)这对于绩效意味着什么?

在SQLServer2005上,我从一个表中查询了13000行数据,其中有15个nvarchar(max)列。 我反复计时查询,然后将列更改为nvarchar(255)或更低。

优化前的查询平均为2.0858秒。更改后的查询平均返回1.90秒。这是对基本select*查询的184毫秒改进。这是8.8%的改善。

3)我的结果与其他几篇文章一致,这些文章指出存在性能差异。根据您的数据库和查询,改进的百分比可能有所不同。如果您没有很多并发用户或记录,那么性能差异就不会成为您的问题。但是,性能差异将随着更多记录和并发用户的增加而增加。

我有一个UDF,它填充字符串并将输出放到varchar(max)中。如果直接使用它而不是将其铸造回所调整的柱的适当尺寸,则性能非常差。最后,我用一个大注释将UDF设置为任意长度,而不是依赖UDF的所有调用方将字符串重新转换为较小的大小。

有趣的链接: Why use a VARCHAR when you can use TEXT?

这是关于postgresql和mysql的,所以性能分析是不同的,但是“明确性”的逻辑仍然成立:为什么强迫自己总是担心一些与时间相关的事情?如果您将电子邮件地址保存到变量中,您将使用“字符串”而不是“限制为80个字符的字符串”。

传统系统支持。如果您有一个使用数据的系统,并且该系统的长度应该是一定的,那么数据库是强制使用该长度的好地方。这并不理想,但遗留系统有时也不理想。= P

如果一行中的所有数据(对于所有列)永远不会合理地占用8000个或更少的字符,那么数据层的设计应该强制执行这一点。

数据库引擎可以更有效地将所有内容从blob存储中保存出来。限制行越小越好。你能在一页中塞进的行越多越好。当必须访问更少的页面时,数据库的性能会更好。

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

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;

我能看到的主要缺点是,假设你有:

哪一个提供了关于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]

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

这让我想到 data structure alignment 在C语言中,意识到对齐通常被认为是一件好事(tm)。相似的想法,不同的背景。

用于MSDN页面 Pages and Extents

用于MSDN页面 Row-Overflow Data

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

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