Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Performance impact of adding XML column?		  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

malachi151
Posting Yak Master

152 Posts
Posted - 10/08/2012 :  15:37:09  Show Profile  Visit malachi151's Homepage  Reply with Quote
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

USA
1974 Posts
Posted - 10/08/2012 :  15:57:38  Show Profile  Visit chadmat's Homepage  Reply with Quote
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
Go to Top of Page

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2179 Posts
Posted - 10/09/2012 :  01:47:27  Show Profile  Visit jackv's Homepage  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000