为什么XML比VARCHAR(MAX)占用更多的存储空间？

Question

我们有大型表将XML数据存储为varchar(MAX)。这些数据是供参考/历史使用的，不需要查询。根据我所读过的内容，以XML数据类型存储而不是VARCHAR(MAX)应该会节省空间，但我的测试显示并非如此。参见下面，其中t1_XML的大小小于t1_NVARCHARMAX，但大于t1_VARCHARMAX。

set nocount on;

drop table t1_XML;
drop table t1_VARCHARMAX;
drop table t1_NVARCHARMAX;

create table t1_XML(col1 int identity primary key, col2 XML);
create table t1_VARCHARMAX(col1 int identity primary key, col2 varchar(max));
create table t1_NVARCHARMAX(col1 int identity primary key, col2 nvarchar(max));

go

declare @xml XML = '<root><element1>test</element1><element2>test</element2><element3>test</element3><element4>test</element4><element5>test</element5></root>'
    , @x int = 1;

while @x <= 10000
begin
    begin tran

    insert into dbo.t1_XML (col2) values (@xml);
    insert into dbo.t1_VARCHARMAX (col2) values (cast(@xml as varchar(max)));
    insert into dbo.t1_NVARCHARMAX (col2) values (cast(@xml as varchar(max)));

    commit tran

    set @x += 1;
end

exec sp_spaceused 'dbo.t1_XML';
exec sp_spaceused 'dbo.t1_VARCHARMAX';
exec sp_spaceused 'dbo.t1_NVARCHARMAX';

Answer 1

关于XML数据类型，有两件事需要知道，它们一起解释了您正在经历的事情：

1. 正如在@EvanCarroll的回答中所指出的，XML数据类型是经过优化的。这意味着，我们创建了一个字典/查找列表来存储每个唯一的名称一次，给定一个数字ID，这个ID用于填充文档的结构，而不是重复元素和属性名称(通常重复了相当多的元素和属性名称，这是很多人抱怨XML文档如此庞大的原因的很大一部分)。这就是为什么XML常常是存储XML文档的更好方法的原因。
2. 此外，XML数据类型使用UTF-16 (Little )存储字符串值(元素和属性名称以及任何实际的字符串内容)。这种数据类型不使用压缩，因此字符串实质上是每个字符的2或4个字节，大多数字符是2字节的类型。

查看您正在使用的特定测试XML文档，以及VARCHAR数据类型(每个字符1到2个字节，通常是1字节类型)，我们现在可以解释您所看到的结果：

1. 您的每个元素(root、element1等)只使用一次，因此将名称放入查找列表中的唯一节省就是将大小减少一半。但是，XML类型使用的是UTF-16，因此每个字符串的大小是UTF-16的两倍，从而取消了将元素名称移到查找列表中的节省。此时，如果只查看文档结构(即元素名称)，那么XML类型和VARCHAR版本实际上应该没有区别。
2. 但是，每个元素(即test)中的字符串内容占字节数的两倍:在XML中占8个字节，而在VARCHAR中占4个字节。假设每一行有5个"test“实例，即XML类型的每行增加20个字节。在10k行，即600,000字节差的200,000个额外字节。其余的是XML类型的内部开销，以及存储相同数量的数据所需的额外页面开销，因为每一行都稍微大一些。

为了更好地说明这种行为，请考虑以下两个XML数据变体:第一个与问题中的XML完全相同，第二个几乎相同，但所有元素都是相同的名称。在第二个版本中，所有元素名都是"element1“，因此它们与原始版本中的每个元素具有相同的长度。这是在这两种情况下VARCHAR数据长度相同的结果。但是第二个版本中的元素名称是相同的，这使得内部优化更加引人注目。

-- Original XML (unique element names -- "element1", "element2", ... "elementN"):
DECLARE @xml XML =  '<root><element1>test</element1><element2>test</element2>
<element3>test</element3><element4>test</element4><element5>test</element5></root>';
SELECT DATALENGTH(@xml) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml)) AS [VarcharBytes];

-- More "typical" XML (repeated element names -- all "element1"):
DECLARE @xml2 XML = '<root><element1>test</element1><element1>test</element1>
<element1>test</element1><element1>test</element1><element1>test</element1></root>';
SELECT DATALENGTH(@xml2) AS [XmlBytes],
       DATALENGTH(CONVERT(VARCHAR(MAX), @xml2)) AS [VarcharBytes];

结果：

ElementNames    XmlBytes    VarcharBytes
------------    --------    ------------
Unique          197         138
Non-Unique      109         138

Answer 2

从XML数据类型和列(服务器)上的文档

数据存储在保留数据的XML内容的内部表示中。这个内部表示包括有关包含层次结构、文档顺序以及元素和属性值的信息。具体来说，XML数据的InfoSet内容是保留的。有关InfoSet的更多信息，请访问http://www.w3.org/TR/xml-infoset。InfoSet内容可能不是文本XML的相同副本，因为以下信息没有保留:不重要的空格、属性顺序、命名空间前缀和XML声明。

binary_representation_size大致是data + information about the containment hierarchy, document order, and element and attribute values - insignificant white spaces, order of attributes, namespace prefixes, and XML declaration

如果您没有名称空间前缀，而空白您只是存储更多的数据，那就不是一个明确的胜利。

在文档中还明确提到，如果您只是存储而不关心特性或验证，则可能只想使用nvarchar(max)，

如果不满足这些条件需要高级功能，则应该使用关系数据模型。例如，如果您的数据是XML格式，但应用程序只是使用数据库来存储和检索数据，那么只需要一个[n]varchar(max)列。将数据存储在XML列中有其他好处。这包括让引擎确定数据是否格式良好或有效，还包括对细粒度查询和XML数据更新的支持。

Answer 3

Server 2016引入了压缩功能。将其应用于@所罗门的示例：

... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml))) AS [VarcharCompressed];

... DATALENGTH(COMPRESS(CONVERT(VARCHAR(MAX), @xml2))) AS [VarcharCompressed];

进一步节省空间如下：

ElementNames    XmlBytes    VarcharBytes  VarcharCompressed
------------    --------    ------------  -----------------
Unique          197         138           72
Non-Unique      109         138           49

值得注意的是，为唯一和重复的元素名称都节省了空间。