Where to find the size of SQL Server data types?
How can I prove that the size of an XML is 5?


I believe this question is better stated as: "Where to find the size of variable-length SQL Server data types?"

The issue with variable-length types is that there is no specific size that can be documented anywhere as it all depends on the data currently being stored in a particular variable or column. And the only way to know the current size is to use the DATALENGTH built-in function. For example:

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

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

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


Which returns:

NULL
5
9


Now, the XML datatype does more than just provide convenient access functions for .value, .nodes, .query, and .exist. It is "optimized" in that the physical storage is reduced by creating a dictionary of the tag and attribute names and replacing the text with an ID that can be looked up. So the internal representation of an XML document is not what you see when looking at the XML document.

As you can see below, the NVARCHAR string is 94 bytes due to being UTF-16 (Little Endian) and having 47 characters (as expected). That same value converted to VARCHAR takes up only 47 bytes for those 47 characters (as expected). However, once converted to XML, the size in bytes is 53. How is that? Well, the text of the "row" and "col" tags is recorded only once (in the internal dictionary) and then IDs are used in the internal hierarchy of the document.

DECLARE @TestNVARCHAR NVARCHAR(500);
SET @TestNVARCHAR = N'&lt;row&gt;&lt;col&gt;1&lt;/col&gt;&lt;col&gt;2&lt;/col&gt;&lt;col&gt;3&lt;/col&gt;&lt;/row&gt;';

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];
-- &lt;row&gt;&lt;col&gt;1&lt;/col&gt;&lt;col&gt;2&lt;/col&gt;&lt;col&gt;3&lt;/col&gt;&lt;/row&gt;  53


This is also why white space and formatting is ignored and the format of the XML value when selected might not be exactly as it was when it was set / inserted:

DECLARE @EmptyTag XML;
SET @EmptyTag = N'&lt;test&gt;&lt;/test&gt;             &lt;test&gt;&lt;/test&gt;';
SELECT @EmptyTag;
-- &lt;test /&gt;&lt;test /&gt;


There are at least two cases, however, where DATALENGTH does not appear to be accurate: SPARSE data and Data Compression. The SPARSE option (introduced in SQL Server 2008) allows fixed-length types to take up 0 bytes when NULL, but then their standard size plus 4 bytes when NOT NULL. However, that doesn't show up correctly here:

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;


Returns:

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


In cases like these you would need to use DBCC PAGE to view the actual row in the actual data page to see what is really happening.

Data Compression behaves the same as the SPARSE option in terms of the effect (or lack thereof) on the value reported by DATALENGTH. This, I suppose, should make sense given that DATALENGTH reports the number of bytes taken up by the datatype (which applies to both variables and columns), but the SPARSE option and Data Compression are storage optimizations (which only apply to columns).