首页 > 数据库 >对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型

对比 SQL Server中的VARCHAR(max) 与VARCHAR(n) 数据类型

时间：2024-01-03 21:44:22浏览次数：49  
标签：varchar max 数据类型 Employee VARCHAR 8000

开始之前： 设计某数据库表结构的过程中，收到了一个另外令人感到意外的建议：对于字符型数据类型，数据库里统一使用varchar(max)来存储，也就是所有字符数据类型都用varchar(max)字段类型，理由是ORM写代码方便？是的，你没有听错，为了ORM中写代码方便，所以建议数据库中字符型字段全部使用varchar(max)数据类型。这是中了ORM多深的毒！！！ 对于这个问题，真的非常非常非常意外，有人竟然提出这种“建议”，我第一反应是想反问：为啥你上下班通勤，开个小轿车，而不是开个载重80吨的重型卡车？重型卡车想对小轿车又结实，又能走烂路，又能更多地载重，牵引力又大，空间也大，也可以放下双肩包，笔记本电脑等，为什么不用重型卡车来上下班通勤？   刚好碰到一个对比VARCHAR(max) 与VARCHAR(n) 数据类型文章，于是就翻译了一下，以下为原文链接： https://www.sqlshack.com/varcharmax-data-type-walkthrough-and-its-comparison-with-varcharn-in-sql-server/   以下是译文：   我见过SQL开发人员在设计表或临时表时使用varchar(max)数据。我们可能不确定数据长度，或者我们想消除字符串或二进制截断错误。每次使用都使用varchar(max)是否是一个好习惯？ 我们可以为varchar（n）数据类型定义一个特定的范围，这是推荐的作法。为了了解这个数据类型，请阅读SQLvarchar（n）文章。 我们将在本文中讨论varchar max的使用及其影响，并与varchar(n)数据类型进行比较。  

VARCHAR(max) SQL Server数据类型概述

SQL Server 2005引入了varchar(max)数据类型。它取代了大型blob对象Text、NText和Image数据类型。所有这些数据类型最多可存储2 GB的数据。 您可能知道SQL Server中的基本存储单位是页。SQL Server中的页大小为8 KB（8192字节），而且是固定的。在页上，SQL Server使用96个字节作为页头。我们可以在SQL Server中存储8096个字节（8192-96个字节）的数据。除此之外，页还包含行开销和行偏移，并留下8000个字节用于数据存储。因此，我们最多可以使用varchar(8000)数据类型存储8000个字节的数据（译者注：varchar(n)中n额最大值为8000，如果是nvarchar(n)，n的最大值是4000）。 您可能会考虑使用varchar(max)数据类型来存储2 GB的数据，以解决字符串截断问题。 让我们创建一些具有不同varchar数据类型大小的示例表。我们还将创建一个具有varchar(max)数据类型的表。 
CREATE TABLE dbo.Employee_varchar_2000
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(2000)
);
 
CREATE TABLE dbo.Employee_Varchar_4500
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(4500)
);
 
CREATE TABLE dbo.Employee_Varchar_8000
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(8000)
);
 
CREATE TABLE dbo.Employee_Varchar_Max
(id           INT IDENTITY PRIMARY KEY,
Col1 VARCHAR(MAX)
);
让我们使用以下查询将记录插入到这些示例表中。 
INSERT INTO Employee_varchar_2000 (Col1)
SELECT REPLICATE('A', 2000);
 
INSERT INTO Employee_varchar_4500 (Col1)
SELECT REPLICATE('A', 4500);
 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 8000);
 
INSERT INTO Employee_varchar_max (Col1)
SELECT REPLICATE('A', 8000);
我们可以使用以下查询验证这些表中的数据长度。 
Use SQLShackDemo
go

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_2000;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_4500;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_8000;

SELECT LEN(col1) AS columnlength
FROM Employee_varchar_max;
现在，我们可以使用DMV sys.dm_db_index_physical_stats检查页面计数、行计数和分配单元等对象统计信息。 
SELECT OBJECT_NAME([object_id]) AS TableName,
       alloc_unit_type_desc,
       record_count,
       page_count,
       round(avg_page_space_used_in_percent,0) as avg_page_space_used_in_percent ,
       min_record_size_in_bytes,
       max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED')
WHERE OBJECT_NAME([object_id]) LIKE 'Employee_varchar%';
我们可以看到，所有表都包含分配单元IN_ROW_Data。SQL Server将所有数据存储在IN_ROW_Data分配单元中。

我们不能在varchar(n)数据类型中插入超过8000字节的数据。如果尝试这样做，将得到以下错误消息。 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 8001);
Go
 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE('A', 10000);
它成功插入数据，但将值截断为8000个字符。对于包含varchar(max)数据类型的Employee_varchar_max表也会发生类似的截断。

我们需要将值转换为varchar(max)并插入超过8000个字符的长度。在尝试向Employee_varchar_8000表中插入记录时，我们得到了错误消息。 
INSERT INTO Employee_varchar_8000 (Col1)
SELECT REPLICATE(CONVERT(VARCHAR(max), 'x'), 8001);

它成功地在Employee_varchar_max表中插入了记录。

重新运行查询以检查分配单元。我们获取LOB_Data分配单元，以便在Employee_Varchar_Max表中存储超过8000字节的数据。我们在IN_Row_DATA分配单元中有一个指向此数据的指针。

我们可以得出以下结论：

如果数据小于或等于8000字节，SQL Server使用IN_ROW_DATA页来存储varchar(max)数据类型的数据。
如果数据超过8000字节，SQL Server使用LOB_DATA页来存储varchar(max)数据类型的数据。

varchar(max)和varchar(n)数据类型之间的性能比较

让我们将10,000条记录插入到我们之前创建的每个表中。我们想检查数据插入时间。您可以使用ApexSQL Generate工具插入数据，而无需编写T-SQL代码。

在下面的屏幕截图中，您可以注意到以下几点。 译者注：我们无需关注原文作者使用的工具本身，只需要看他的测试方法和得到的结论

Employee_varchar_2000插入时间0.08秒
Employee_varchar_4500插入时间0.19秒
Employee_varchar_8000插入时间0.31秒
Employee_varchar_Max插入时间2.72秒

 

在 VARCHAR(N) and VARCHAR(MAX) 列上创建索引

作为DBA，您可能不会设计表。但是，需要在表上创建一个索引来提高查询的性能。 我们可以在持有varchar(n)数据类型的表的键列上创建索引。
CREATE INDEX IX_Employee_varchar_2000_1
ON dbo.Employee_varchar_2000(col1)
GO
当我们尝试对varchar(max)数据类型执行相同的操作时，会收到以下错误消息（译者注：SQLserver中varchar(max)类型字段不允许创建索引）： 
CREATE INDEX IX_Employee_varchar_max
ON dbo.Employee_varchar_max(col1)
GO

Msg 1919, Level 16, State 1, Line 23 Column ‘col1’ in table ‘dbo.Employee_varchar_max’ is of a type that is invalid for use as a key column in an index.
我们可以使用varchar(max)列作为索引中的包含列，但不能对这一列执行索引查找。它还需要额外的存储空间。因此，应避免使用varchar(max)数据类型创建索引。  

执行计划(Execution plan)对比

让我们比较两个select语句的执行计划。

在第一个查询中，我们想从Employee_Varchar_2000表中检索数据并获取实际的执行计划。

在实际的执行计划中，我们可以看到一个非聚集索引查找操作符。

如果我们使用varchar(max)数据类型运行相同的查询，它会使用聚集索引扫描操作符，并且根据表中的行数，它可能是一个资源密集型操作符。

select col1 from Employee_varchar_max where col1 like ‘xxxx%’

让我们使用SSMS的Compare Showplan选项比较执行计划。

要比较两个执行计划，右键单击其中一个执行计划并选择Save Execution Plan As，然后提供要保存该计划的路径。在另一个查询执行计划中，右键单击并选择Compare Showplan。它打开一个窗口，您可以在其中指定先前保存的执行计划的路径。在下面的屏幕截图中，您可以看到两个执行计划之间的比较。

1，对于相似的查询，varchar(max)数据类型的估计CPU成本更高
2，对于varchar(max)，它使用聚集索引扫描操作符并扫描所有记录。您可以看到，估计的行数是10000行，而在varchar(2000)数据类型中，它使用索引查找操作符，估计的行数是1.96078行
3，估计的行大小4035 B大于varchar(max)中的1011 B，与varchar(2000)数据类型相比

译者注：这部分原作者想表达的是：对于varchar(max)类型字段，数据库优化器在编译SQL生成执行计划的时候会需要更多的CPU资源，同时对于数据行的预估也没有varchar(n)准确

varchar(max) and varchar(n) 数据类型的不同之处

varchar(max) varchar(n)
这种数据类型最多可以存储2gb的数据 这种数据类型最多可以存储8000字节的数据
它使用分配单元IN_ROW_Data最多8000字节的数据。如果数据大于8000字节，则使用LOB_Data页，并将其指针存储在IN_ROW_Data页中 它将数据存储在标准数据页中
不能在varchar(max)数据类型的键列上创建索引 可创建索引
不能压缩LOB数据 可压缩
LOB数据的数据检索和更新相对较慢 在varchar(n)数据类型中不会遇到这样的问题
 

总结

在本文中，我们演示了varchar(max)数据类型，并探讨了varchar(max)和varchar(n)数据类型之间的几个差异。您应该使用适当的数据类型。我们应该考虑数据库设计、性能、压缩和索引。您应该审查数据库中的数据类型，并在需要时进行适当的测试和更改。  

 

笔者补充

1，执行计划差异

鉴于varchar(max)字段不支持创建索引，其实已经完全没有进一步做性能测试的必要了，为了把问题说清楚，那么就在没有索引的情况下继续对比测试下去，单纯地比较两种字段类型在生产执行计划时的差异。 进步一对比对比varchar(max)和varchar(n)对执行计划的影响，先创建一个测试环境。 
create table sb_test1
(
    c1 int identity(1,1),
    c2 varchar(50),
    c3 varchar(50),
    c4 varchar(50),
    c5 varchar(50),
    c6 varchar(50),
    c7 varchar(50),
    c8 varchar(50),
    c9 varchar(50),
    c10 datetime2
);


create table sb_test2
(
    c1 int identity(1,1),
    c2 varchar(max),
    c3 varchar(max),
    c4 varchar(max),
    c5 varchar(max),
    c6 varchar(max),
    c7 varchar(max),
    c8 varchar(max),
    c9 varchar(max),
    c10 datetime2
);


declare @i int = 0;
begin tran
    while @i<1000000
    begin
        insert into sb_test1 values (newid(),newid(),newid(),newid(),newid(),newid(),newid(),newid(),sysdatetime())
        set @i = @i + 1;
    end
commit
go 10

insert into sb_test2 
select c2,c3,c4,c5,c6,c7,c8,c9,c10 from sb_test1

以上新建两张表结构和数据一样的表，sb_test1表字段用varchar(50),sb_test2表字段用varchar(max)，都是1000W行数据。

 

1，对于同一个查询，执行计划的Memory Grant明显不一样，varchar(max)类型字段的表的执行计划内存（memory grant）明显要高很多。

2，对于同一个查询，谓词predicate过滤的时机也不一样，varchar(n)可以再扫描的过程中实现谓词过滤（边扫描边过用where条件滤），而varchar(max)只能在将表扫描完之后，在内存中单独执行谓词过滤（完全扫描之后再用where条件过滤）

3，对于同一个查询，执行结果中的的CPU资源消耗明显不一样（基于上述中的2，执行计划无法再扫描的时候进行谓词过滤），varchar(max)类型字段的表的查询要varchar(n)高2倍多。

再总结

不单单是字符型数据，包括整型（tinyint，smallint，int，bigint），时间类型（date,time,smalldatetime,datetime,datetime2）等等，具体的类型选择是基于业务的，不是基于ORM好不好处理的问题。
错误的做法没有造成特别明显的问题，这并不是说明这就是可行的，而是是因为数据量没到，不要把错误的经验当成经验使用。

 

标签：varchar,max,数据类型,Employee,VARCHAR,8000
From： https://www.cnblogs.com/wy123/p/17943363

相关文章

  • 3dmax全景图用什么渲染 全景图云渲染使用教程
    在给客户展示设计概念时，应用3dsMax创建的全景图是一个高效直观的方法。这种方式不仅可以全方位地呈现整个空间，让客户沉浸式地感受设计师的创意，而且在展现大型空间设计，如展览馆或者会议室等，效果尤其显著，帮助客户完整地理解设计意图。3dmax全景图用什么渲染？在制作3dsMax全景图......
  • 神通数据库的varchar和nvarchar的验证
    神通数据库的varchar和nvarchar的验证登录神通数据库isql注意神通数据库的默认密码是szoscar55Welcometoisql2.0.56interactiveterminal,ClientDefaultEncodingisUTF8.Logintime:2023-12-2516:00:36Passwordfordefaultuser:Connectto:神通数据库7.0.8.1......
  • 达梦数据库varchar和nvarchar的验证
    达梦数据库varchar和nvarchar的验证测试SQLcreatetablespacezhaobshdatafile'/opt/dmdbms/data/DAMENG/zhaobsh.dbf'size128#需要注意达梦数据库的大小限制为:#第1行附近出现错误[-2422]:数据文件[/opt/dmdbms/data/DAMENG/zhaobsh.dbf]大小无效，取值范围为(128~6710886......
  • ZHS16GBK字符集下面Oracle数据库varchar与nvarchar的验证
    ZHS16GBK字符集下面Oracle数据库varchar与nvarchar的验证背景周末分析了SQLServermysql等数据库想着继续分析一下oracle数据库这边oracle使用的是ZHS16GBK的字符集.所以比较特殊.还是建议得使用UTF-8字符集.能让系统干的活就让系统干,自己干国际化太费劲了.处理思路使......
  • MYSQL varchar和nvarchar一些学习
    MYSQLvarchar和nvarchar一些学习背景先试用utfmb3的格式进行一下简单验证注意脚本都是一样的.createdatabasezhaobsh;usezhaobsh;createtablezhaobsh(zhaobshvarcharvarchar(30),zhaobshnvarcharnvarchar(30));insertintozhaobshvalues('123abc','123ab......
  • SQLServer的varchar与nvarchar的学习之二
    SQLServer的varchar与nvarchar的学习之二背景昨天简单总结了多种数据库varchar和nvarchar的区别与关系今天想着能够分析一下数据库文件.计划使用winhex查看数据文件,简单进行学习过程创建数据库和表,然后进行一些测试,进行简单测试判断.createdatabasezhaobshcreateta......
  • 凡是有但是-varchar和nvarchar的初步学习之一
    凡是有但是-varchar和nvarchar的初步学习之一背景高应用开发,在涉及到国内国外的问题时,重要的事情有两个:时区转换,字符集转换.时区转换虽然是很难理清楚,各种规范不统一的事情但是字符集转换更加扯淡.如果是一种数据库,一种字符集可能还好说现在面临的是多种数据库:......
  • python学习笔记6（基本数据类型2、数据类型之间的转换、eval()函数）
    （一）基本数据类型21、字符串的索引和切片索引，查找某个字符，包括正向和反向索引，正向从0开始，负向从-1开始s='hello'print(s[0])print(s[-5])切片，字符串中了串检索，注意下面的输出是he，也就是第二个参数是不包含参数，正向反向规则一样，n或m可以节省s='hello'print(s[0:2])2、字符串类型xn或......
  • Cesium常用数据类型加载方式
    WMTSlettdtIMGLabel=newCesium.WebMapTileServiceImageryProvider({//影像注记url:"http://t{s}.tianditu.gov.cn/cia_w/wmts?service=wmts&request=GetTile&version=1.0.0&LAYER=cia&tileMatrixSet=w&TileMatr......
  • python学习笔记5（保留字、标识符、变量、基本数据类型）
    上一节学习了print复杂语法、input、注释、代码缩进本节内容：（一）保留字特殊意义的词，区分大小写#查看保留字importkeywordprint(keyword.kwlist)（二）标识符1、名字，组成：字母、下划线和数字数字不能开头注意不能有殊殊字符一般不以_开头，有特殊意义一般不以中文开头2、代码规范模块名称，a......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99