Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
malachi151
Posting Yak Master
152 Posts |
 Posted - 2012-10-08 : 15:37:09
|
| What are the performance impacts, if any, of adding an XML column to a table, in relation to queries against the non-XML columns?In other words, if an add an XML column to a table, will it impact queries against the non-XML columns?I know it has an impact on distributed queries, i.e. if you try to query a table across a Linked Server that has an XML column in it you can't do that, even if you don't include the XML column in the query, etc.So let's say that I added an XML column, and its mostly NULL, how would this impact performance, how exactly is it stored on the pages, etc?Thanks--------------------------------------------------My blog: http://practicaltsql.net/Twitter: @RGPSoftware |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2012-10-08 : 15:57:38
|
| This behavior is somewhat configureable, but by default, I think XML is stored In Row. So it would affect your page density (Less rows per page). If they are mostly null, then the effect would be minimal.-Chad |
 |
|
|
jackv
Master Smack Fu Yak Hacker
2179 Posts |
Posted - 2012-10-09 : 01:47:27
|
| There are ways of configuring XML to be stored off-row check http://www.sqlserver-dba.com/2012/09/sql-server-large-value-types-out-of-row-and-performance.html, which replaces the data row with a 16 byte text pointer. Your potential performance hit may be large scans being forced therefore high IO. It depends on what portion of your queries are referencing the XM column.Jack Vamvas--------------------http://www.sqlserver-dba.com |
|
|
|
|
|
|
|
|
Performance impact of adding XML column?