為什麼 XML 比 VARCHAR(MAX) 佔用更多的儲存空間？

我們有將 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';

關於數據類型有兩件事需要了解XML，它們共同解釋了您所遇到的情況：

1. 如@EvanCarroll 的回答中所述，XML數據類型已優化。意義，而不是重複元素和屬性名稱（它們通常會重複很多次，這也是為什麼這麼多人，有時是理所當然地抱怨 XML 文件如此龐大的原因），而是創建了一個字典/查找列表來給定一個數字 ID，將每個唯一名稱儲存一次，該​​ ID 用於填充文件的結構。這就是為什麼XML數據類型通常是儲存 XML 文件的更好方法的原因。
2. 此外，該XML數據類型使用 UTF-16 (Little Endian) 來儲存字元串值（元素和屬性名稱以及任何實際的字元串內容）。此數據類型不使用壓縮，因此字元串本質上是每個字元 2 或 4 個字節，大多數字元是 2 字節變體。

查看您正在使用的特定測試 XML 文件和VARCHAR數據類型（每個字元 1 到 2 個字節，最常見的是 1 字節種類），我們現在可以解釋您所看到的結果是：

1. 您的每個元素（root、element1等）僅使用一次，因此將名稱放入查找列表的唯一節省是將大小恰好減少一半。但是，XML 類型使用 UTF-16，因此每個字元串的大小是原來的兩倍，從而抵消了將元素名稱移動到查找列表中的節省。此時，如果僅查看文件結構（即元素名稱），那麼實際上XML類型和VARCHAR版本之間應該沒有區別。
2. 但是，每個元素（即test）中的字元串內容佔用了兩倍的字節數：8 字節 inXML而 4 字節 in VARCHAR。鑑於每行有 5 個“測試”實例，即該類型每行​​有 20 個額外字節XML。在 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

引用自：https://dba.stackexchange.com/questions/190585