In one of the tables in our production database I have recently added XML datatype column. My concern is that will the DB size increase or performance will be slow because of adding it. Can anyone please tell me how to monitor the increase in DB size since the addition of XML column and also the advantages/disadvantages of using XML data type.
It's storage depend on the XML schema used.
Generally tables with small, fixed column widths are the fastest performers.
Consider storing large data (like XML, images, binaries, ...) in their own tables and just FOREIGN KEY link them to the original table. This design is especially helpful if the large data is accessed less frequently than the small-size data.
Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012
Another thing to remember is that XML is a "large value type", and so you get to choose how they are stored. The default is for small XML columns to be stored in each row (up to 8000 bytes) to be stored on the data row.
If you frequently access this table without touching the XML column then you should consider setting the "large value types out of row" table option using sp_tableoption.
- Edited by davidbaxterbrowneMicrosoft employee Tuesday, November 19, 2013 1:41 PM
XML datatype on its own is not too bad, but you really have to watch out with XML indexes. You can expect your table to end up between 2 and 5 times its original size if you start adding XML indexes.
Selective XML Indexes available from SQL 2012 are more compact and can be used independently.
Regarding performance, it's just as possible to write bad XQuery as it is bad T-SQL. Can you provide some examples of the type of thing you are doing?