varchar和nvarchar SQL Server数据类型之间的主要性能差异是什么?

varchar和nvarchar SQL Server数据类型之间的主要性能差异是什么?

What are the main performance differences between varchar and nvarchar SQL Server data types?

我正在使用SQL Server 2005在我的学校为一个小型网络应用程序开发数据库。
我在varchar vs nvarchar的问题上看到了几个思想流派:

  • 除非您处理大量国际化数据,否则请使用varchar,然后使用nvarchar
  • 只需使用nvarchar即可。
  • 我开始看到视图2的优点。我知道nvarchar确实占用了两倍的空间,但这不一定是一个大问题,因为这只是为几百名学生存储数据。 对我而言,似乎最简单的是不要担心它并且只允许一切使用nvarchar。 还是有什么我想念的?


    磁盘空间不是问题...但内存和性能将是。
    双页读取,双索引大小,奇怪的LIKE和=常量行为等

    您需要存储中文等脚本吗?是还是不是...

    从MS BOL"Unicode的存储和性能影响"

    编辑:

    最近的SO问题突出了nvarchar性能有多糟糕......

    在nvarchar字符串中搜索时,SQL Server使用高CPU


    始终使用nvarchar。

    对于大多数应用程序,您可能永远不需要双字节字符。但是,如果您需要支持双字节语言,并且数据库模式中仅支持单字节,那么回头修改整个应用程序确实非常昂贵。

    将一个应用程序从varchar迁移到nvarchar的成本将比您在大多数应用程序中将使用的额外磁盘空间少得多。


    始终如一!将VARCHAR加入到NVARCHAR中会有很大的性能影响。


    nvarchar将在内存,存储,工作集和索引编制上有大量开销,因此,如果规范指示确实不需要,则不要打扰。

    我不会有一个坚硬而快速的"永远nvarchar"规则,因为在许多情况下它可能是完全浪费 - 特别是来自ASCII / EBCDIC的ETL或通常是键和外键的标识符和代码列。

    另一方面,有很多列的情况,我肯定会提前问这个问题,如果我没有立即得到一个快速的答案,我会把列设为nvarchar。


    对于您的应用程序,nvarchar很好,因为数据库大小很小。说"总是使用nvarchar"是一个巨大的过度简化。如果你不需要存储像汉字或其他疯狂字符这样的东西,使用VARCHAR,它将使用更少的空间。我的前任在我目前的工作中使用NVARCHAR设计了一些不需要的东西。我们最近将其切换为VARCHAR,仅在该表上保存了15 GB(已被高度写入)。此外,如果您在该表上有一个索引,并且想要包括该列或创建一个复合索引,则只需增加索引文件的大??小即可。

    考虑一下您的决定;在SQL开发和数据定义中,似乎很少有"默认答案"(当然,除了不惜一切代价避免使用游标)。


    我犹豫在这里添加另一个答案,因为已经有很多答案了,但是需要提出一些尚未提出或没有明确提出的观点。

    第一:不要总是使用NVARCHAR。这是一种非常危险且往往代价高昂的态度/态度。最好不要说"从不使用游标",因为它们有时是解决特定问题的最有效方法,而执行WHILE循环的通常解决方法几乎总是比正确完成的游标要慢。

    你唯一应该使用"永远"这个词的时候就是建议"总是做最适合这种情况的事情"。当然,这通常很难确定,尤其是在试图平衡开发时间的短期收益时(经理:"我们需要这个功能-您直到一周前才知道的功能!")定期维护成本(最初向团队施加压力,要求其在3周的冲刺中完成3个月的项目的经理:"我们为什么会遇到这些性能问题?我们怎么可能做X却没有灵活性?我们负担不起一两个冲刺来解决这个问题。我们可以在一周内完成什么工作,以便我们可以回到我们的优先项目?我们肯定需要在设计上花更多的时间,所以这不会继续发生!")。

    第二:@gbn的答案涉及一些非常重要的要点,当路径不是100%清晰时,在做出某些数据建模决策时要考虑。但是,还有更多需要考虑的问题:

  • 事务日志文件的大小
  • 复制所需的时间(如果使用复制)
  • ETL所需的时间(如果是ETL)
  • 将日志传送到远程系统并还原所需的时间(如果使用日志传送)
  • 备份大小
  • 完成备份所需的时间
  • 进行还原所需的时间(这一天可能很重要;-)
  • tempdb所需的大小
  • 触发器的性能(用于存储在tempdb中的已插入和已删除表)
  • 行版本控制的性能(如果使用SNAPSHOT ISOLATION,因为版本存储在tempdb中)
  • 当首席财务官说他们去年在SAN上花费了100万美元时,就可以获取新的磁盘空间,因此他们不会再授权25万美元用于额外的存储
  • 执行INSERT和UPDATE操作所需的时间长度
  • 维护索引所需的时间
  • 等等等
  • 浪费空间对整个系统产生巨大的级联效应。我写了一篇文章,详细介绍了该主题:磁盘便宜! ORLY? (需要免费注册;抱歉,我不控制该政策)。

    第三:虽然一些答案错误地侧重于"这是一个小应用程序"方面,而一些答案正确地建议"使用适当的东西",但没有一个答案为OP提供了真正的指导。问题中提到的重要细节这是他们学校的网页。大!因此,我们建议:

  • 学生和/或学院名称的字段应该为NVARCHAR,因为随着时间的流逝,来自其他文化的名称将越来越有可能出现在那些地方。
  • 但是街道地址和城市名称呢?该应用程序的目的没有说明(它会有所帮助)但假设地址记录(如果有的话)仅适用于特定地理区域(即单一语言/文化),则使用VARCHAR和相应的代码页面(根据字段的整理确定)。
  • 如果存储状态和/或国家ISO代码(不需要存储INT / TINYINT,因为ISO代码是固定长度,人类可读,以及标准:),使用CHAR(2)表示两个字母代码,CHAR(3)表示使用3个字母代码。并考虑使用二进制校对,如Latin1_General_100_BIN2
  • 如果存储邮政编码(即邮政编码),请使用VARCHAR,因为这是国际标准,切勿使用A-Z以外的任何字母。是的,即使仅存储美国邮政编码而不是INT,也仍要使用VARCHAR,因为邮政编码不是数字,它们是字符串,并且其中一些带有前导" 0"。并考虑使用二进制校对,如Latin1_General_100_BIN2
  • 如果存储电子邮件地址和/或URL,请使用NVARCHAR,因为这两者现在都可以包含Unicode字符。
  • 等等....
  • 第四:既然你有NVARCHAR数据占用的空间是它所需的空间的两倍,那么数据很适合VARCHAR("很好地适应"=不会变成"?")并且不知何故,好像通过魔术,应用程序确实增长,现在至少有一个字段中有数百万条记录,其中大多数行是标准ASCII,但有些包含Unicode字符,所以你必须保留NVARCHAR,请考虑以下内容:

  • 如果您使用的是SQL Server 2008 - 2016 RTM且在Enterprise Edition上,或者如果使用SQL Server 2016 SP1(在所有版本中都提供了数据压缩)或更新版本,则可以启用数据压缩。数据压缩可以(但不会"始终")压缩NCHARNVARCHAR字段中的Unicode数据。决定因素是:

  • NCHAR(1 - 4000)NVARCHAR(1 - 4000)使用Unicode的标准压缩方案,但仅在SQL Server 2008 R2中启动,并且仅适用于IN ROW数据,而不是OVERFLOW!这似乎比常规的ROW / PAGE压缩算法更好。
  • NVARCHAR(MAX)XML(我猜也是VARBINARY(MAX)TEXTNTEXT)数据是IN ROW(不是LOB或OVERFLOW页面中的行),至少可以进行PAGE压缩,但不是ROW压缩。当然,PAGE压缩取决于行内值的大小:我使用VARCHAR(MAX)进行了测试,发现6000个字符/字节的行不会压缩,但是4000个字符/字节的行会压缩。
  • 任何OFF ROW数据,LOB或OVERLOW =没有您的压缩!
  • 如果使用SQL Server 2005或2008 - 2016 RTM而不是Enterprise Edition,则可以有两个字段:一个VARCHAR和一个NVARCHAR。例如,假设您存储的URL大部分都是基本ASCII字符(值0 - 127),因此适合VARCHAR,但有时会包含Unicode字符。您的架构可以包括以下3个字段:

    1
    2
    3
    4
    5
    6
    7
    8
      ...
      URLa VARCHAR(2048) NULL,
      URLu NVARCHAR(2048) NULL,
      URL AS (ISNULL(CONVERT(NVARCHAR([URLa])), [URLu])),
      CONSTRAINT [CK_TableName_OneUrlMax] CHECK (
                        ([URLa] IS NOT NULL OR [URLu] IS NOT NULL)
                    AND ([URLa] IS NULL OR [URLu] IS NULL))
    );

    在此模型中,您只能从[URL]计算列中进行选择。对于插入和更新,您可以通过查看转换是否更改传入值(您必须为NVARCHAR类型)来确定要使用的字段:

    1
    2
    3
    4
    5
    INSERT INTO TableName (..., URLa, URLu)
    VALUES (...,
            IIF (CONVERT(VARCHAR(2048), @URL) = @URL, @URL, NULL),
            IIF (CONVERT(VARCHAR(2048), @URL) <> @URL, NULL, @URL)
           );
  • 您可以将传入的值GZIP转换为VARBINARY(MAX),然后在出路时解压缩:

  • 对于SQL Server 2005-2014:可以使用SQLCLR。 SQL#(我写的一个SQLCLR库)附带免费版的Util_GZip和Util_GUnzip
  • 对于SQL Server 2016及更高版本:您可以使用内置的COMPRESSDECOMPRESS函数,它们也是GZip。
  • 如果使用SQL Server 2017或更高版本,则可以考虑将表格设置为Clustered Columnstore Index。

  • 虽然这还不是一个可行的选择,但SQL Server 2019在VARCHAR / CHAR数据类型中引入了对UTF-8的本机支持。当前有太多的错误需要使用,但是,如果它们已修复,则在某些情况下是一种选择。请参阅我的帖子"SQL Server 2019中的原生UTF-8支持:救世主还是假先知?",详细分析了这一新功能。

  • 好。


    由于您的应用程序很小,使用nvarchar而不是varchar基本上没有明显的成本增加,如果您需要存储unicode数据,您可以节省潜在的麻烦。


    一般来说;从约束最少的最昂贵的数据类型开始。把它投入生产。如果性能开始成为问题,请找出实际存储在那些nvarchar列中的内容。那里是否有不适合varchar的字符?如果没有,请切换到varchar。在您知道痛苦在哪里之前,请勿尝试进行预优化。我的猜测是nvarchar / varchar之间的选择并不会在可预见的未来减缓你的应用程序。应用程序的其他部分将进行性能调优,为您带来更多好处。


    我可以凭经验说,谨防nvarchar。除非绝对需要,否则此数据字段类型会破坏较大数据库的性能。我继承了一个在性能和空间方面都受到损害的数据库。我们能够将30GB的数据库大小减少70%!还进行了其他一些修改以帮助提高性能,但是我敢肯定varchar也会大大改善性能。如果您的数据库有可能将表增长到100万+记录,则不惜一切代价远离nvarchar


    在过去的几年里,我们所有的项目都使用了NVARCHAR,因为所有这些项目都是多语言的。在插入数据库之前,从外部源(例如ASCII文件等)导入的数据被上转换为Unicode。

    我还没有遇到来自较大索引等的任何与性能相关的问题。索引确实使用了更多内存,但内存很便宜。

    无论您是使用存储过程还是动态构造SQL,都要确保所有字符串常量都以N为前缀(例如SET @foo = N'Hello world。';),因此常量也是Unicode。这样可以避免在运行时进行任何字符串类型转换。

    因人而异。


    我经常在工作中处理这个问题:

    • 清单和价格的FTP提要-varchar正常工作时,项目描述和其他文本在nvarchar中。将这些转换为varchar减少了几乎一半的文件大小,真的有助于上传。

    • 在有人在商品说明中添加特殊字符之前,上述方案可以正常工作(也许是商标,不记得了)

    我仍然不会每次都使用nvarchar over varchar。如果对特殊字符有任何疑问或可能,我使用nvarchar。当我100%控制填充字段的内容时,我发现我主要使用varchar。


    为什么在所有讨论中都没有提到UTF-8?能够存储完整的unicode字符范围并不意味着必须始终为每个字符分配两个字节(或"代码点"以使用UNICODE术语)。所有ASCII都是UTF-8。 SQL Server是否检查文本是严格ASCII的VARCHAR()字段(即顶部字节位为零)?我希望不会。

    如果那时你想存储unicode并希望与旧的仅ASCII应用程序兼容,我认为使用VARCHAR()和UTF-8将是神奇的子弹:它只在需要时使用更多空间。

    对于那些不熟悉UTF-8的人,我可能建议您阅读入门。


    在某些特殊情况下,您需要故意限制数据类型以确保它不包含特定集合中的字符。例如,我有一个需要将域名存储在数据库中的方案。域名国际化在当时并不可靠,因此最好限制基层的输入,并有助于避免任何潜在的问题。


    如果仅由于系统存储过程需要nvarchar而使用nvarchar,最频繁出现的事件莫名其妙地是sp_executesql,而动态SQL则很长,那么从性能角度考虑,最好进行所有字符串操作(串联,替换)然后在varchar中将最终结果转换为nvarchar并将其输入proc参数。因此,不总是使用nvarchar


    推荐阅读