SQL Server xml performance I have a table with 21 columns. The majority are numerical and the rest are nvarchar.

I need to add 4 columns that will contain XML data for each row. The XML data for each row can be anywhere from 200 to 2,000 lines.

The question is:


Does adding the XML typed columns to the same table change the speed of querying that table?
What are the performance benefits when I add the xml typed columns to another table and join both tables when doing queries?
Is it better to encode the XML data to shrink it and decode it in the application?
There are a lot of facets to this, so it really depends.


  Does adding the XML typed columns to the same table change the speed of querying that table?


More data potentially could be read (I/O), but when you say speed of querying, if you mean finding rows based on columns outside of the XML, this should usually be based on a index and so the xml should not be especially involved in any performance differences

xml columns can be stored in row or out of row, with obvious implications for number of rows which can be stored in a page, fragmentation etc...  http://technet.microsoft.com/en-us/library/ms189087(v=sql.105).aspx  Storing out of row should mean that more rows fit per page, so less data is read and discarded when you don't actually have the xml data stored in-row.


  What are the performance benefits when I add the xml typed
  columns to another table and join both tables when doing queries?


Well, this is basically equivalent to storing out of row.  You'll only get an administrative  benefit doing it yourself in terms of being able to manage the other table independently.


  Is it better to encode the XML data to shrink it and decode it in the application?


Perhaps.  If we're assuming that you don't need to query it in the database (xml data type with xml query techniques), then why stored it as xml at all instead of varbinary or varchar?