A while back I was thinking about SQL Server’s capabilities of returning data as XML with FOR XML and it occurred to me that maybe returning 10.000 results as xml would be faster that the way SQL server returns them now in Tabular Data Stream (TDS). After 10 seconds I dismissed it as nonsense because if that were so the xml would be used by default. But it piqued my curiosity about what would be the actual size overhead of returning XML compared to TDS. I knew there was overhead but i didn’t think it would be so high.

So i decided to test it. I tried 2 queries which got same data from AdventureWorks.Person.Address table and for each I just changed the TOP N value to: 1, 10, 100, 1000, 10000 and no TOP N to get all rows.

Query 1:

SELECT * FROM Person.Address

Query 2:

Here I used alias A and root P to minimize the XML size. The AUTO option creates an XML item for each row and columns are treated as attributes the PATH wraps the row items with the root P item. This appears to be the smallest XML you can generate with unchanged column names.

SELECT * FROM Person.Address as A FOR XML AUTO, ROOT('P')

These are the results in table and graph form.

Rows TDS size (Kb) XML size (Kb) 1 0.36 0.53 10 1.23 3.82 100 10.18 37.59 1000 104.14 382.3 10000 1024.71 3839.93 19614 (All) 2008.28 7526.28       We can see that compared to TDS the XML rises much more exponentially with increasing row numbers.