在哪裡可以找到 SQL Server 數據類型的大小？

這個問題已經在這裡被問過，但沒有一個答案真的有助於找出例如 XML 變數的大小。

declare @myVariable XML

我從這個問題中學到了： 如何在 sql server 中測試 XML = ‘’？

另請注意，雖然 XML 數據的內部表示可能會在版本之間發生變化，但我已經在 SQL Server 2008 R2、2012 和 2014 上進行了測試，空 XML 項的大小始終為 5。

如何找到每個 SQL Server 數據類型的大小？

如何證明 XML 的大小是 5？

這一切始於我調整儲存過程並使用 XML 作為參數 來提高性能 - 如何在 sql server 2005 中將表作為參數傳遞？

如何證明 XML 的大小是 5？

我認為這個問題最好表述為：“在哪裡可以找到可變長度SQL Server 數據類型的大小？”

可變長度類型的問題在於，沒有可以在任何地方記錄的特定大小，因為這完全取決於目前儲存在特定變數或列中的數據。知道目前大小的唯一方法是使用DATALENGTH內置函式。例如：

DECLARE @TestXML XML;
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'';
SELECT DATALENGTH(@TestXML);

SET @TestXML = N'g';
SELECT DATALENGTH(@TestXML);
GO

返回：

NULL
5
9

現在，XML數據類型不僅僅是為.value、.nodes、.query和提供方便的訪問功能.exist。它是“優化的”，因為通過創建標籤和屬性名稱的字典並將文本替換為可以查找的 ID 來減少物理儲存。因此，XML 文件的內部表示並不是您在查看 XML 文件時所看到的。

正如您在下面看到的，NVARCHAR由於是 UTF-16（Little Endian）並且有 47 個字元（如預期的那樣），字元串是 94 個字節。對於這 47 個字元（如預期的那樣），轉換為的相同值VARCHAR僅佔用 47 個字節。但是，一旦轉換為XML，以字節為單位的大小為 53。這是怎麼回事？好吧，“row”和“col”標籤的文本只記錄一次（在內部字典中），然後在文件的內部層次結構中使用 ID。

DECLARE @TestNVARCHAR NVARCHAR(500);
SET @TestNVARCHAR = N'<row><col>1</col><col>2</col><col>3</col></row>';

SELECT LEN(@TestNVARCHAR) AS [LEN], DATALENGTH(@TestNVARCHAR) AS [DATALENGTH];
-- 47   94

DECLARE @TestVARCHAR VARCHAR(500);
SET @TestVARCHAR = CONVERT(VARCHAR(500), @TestNVARCHAR);

SELECT LEN(@TestVARCHAR) AS [LEN], DATALENGTH(@TestVARCHAR) AS [DATALENGTH];
-- 47   47

DECLARE @TestXMLfromNVARCHAR XML;
SET @TestXMLfromNVARCHAR = CONVERT(XML, @TestNVARCHAR);

SELECT @TestXMLfromNVARCHAR, DATALENGTH(@TestXMLfromNVARCHAR) AS [DATALENGTH];
-- <row><col>1</col><col>2</col><col>3</col></row>  53

這也是為什麼忽略空格和格式並且XML選擇時值的格式可能與設置/插入時不完全相同的原因：

DECLARE @EmptyTag XML;
SET @EmptyTag = N'<test></test>             <test></test>';
SELECT @EmptyTag;
-- <test /><test />

然而，至少有兩種情況似乎DATALENGTH並不准確：數據和數據壓縮。該選項（在 SQL Server 2008 中引入）允許固定長度類型在 時佔用 0 個字節，但在 時它們的標準大小加上 4 個字節。但是，這裡顯示不正確：SPARSE``SPARSE``NULL``NOT NULL

DECLARE @TestSPARSE TABLE
(
 RowID INT NOT NULL,
 Col1 BIGINT SPARSE NULL,
 Col2 TINYINT SPARSE NULL
);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (1, 0, 0);
INSERT INTO @TestSPARSE ([RowID], [Col1], [Col2]) VALUES (2, NULL, NULL);

SELECT RowID,
      Col1,
      DATALENGTH([Col1]) AS [BytesCol1],
      Col2,
      DATALENGTH([Col2]) AS [BytesCol2]
FROM   @TestSPARSE;

回報：

RowID    Col1    BytesCol1    Col2    BytesCol2
1        0       8            0       1
2        NULL    NULL         NULL    NULL

在這種情況下，您需要使用DBCC PAGE來查看實際數據頁面中的實際行，以了解實際發生的情況。

SPARSE在對 報告的值的影響（或缺乏影響）方面，數據壓縮的行為與選項相同DATALENGTH。我想，這應該是有意義的，因為它報告了數據類型DATALENGTH占用的字節數（適用於變數和列），但選項和數據壓縮是儲存優化（僅適用於列）。SPARSE

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