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

2024-01-03 21:39 由 MSSQL123 发表于 #数据库

开始之前：

设计某数据库表结构的过程中，收到了一个另外令人感到意外的建议：对于字符型数据类型，数据库里统一使用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好不好处理的问题。
错误的做法没有造成特别明显的问题，这并不是说明这就是可行的，而是是因为数据量没到，不要把错误的经验当成经验使用。

热门相关：我的黑月光女友诛天至极惊世第一妃：魔帝，宠上身！万道龙皇万道龙皇