XML in SQL server
Question From NTSSUG - http://northtexas.sqlpass.org
I am working on an application that has object data mapped to tables. The mapping was done many years ago, before the likes of Hibernate came along. It basically maps a class to a table. Object properties with multiple values get their own tables. There are other interesting mappings, but that's the overall concept.
We have a guy that has been doing research on an alternate approach. He's storing the object data in an XML string. The XML string is stored in the only field of a table. The table is the only one in the system. He's reporting huge performance increases with this approach.
Have any of you heard of anything like this? What do you see are the pros and cons of such an approach?
Ok, so I come from a little different camp then most on this newsgroup. . . . I am a c# developer, not a DBA. Yes I am the enemy.
"XML String"???? I hope you mean the new XML data type and not a varchar.
However I strongly encourage storing data in a relational format in the database. Querying of XML data is more difficult and less performing then a traditional SQL query. There are XML query options available (http://msdn.microsoft.com/en-us/library/ms345122.aspx). However these require learning new syntax and might not be compatible with all 3rd party tools. (The first time I asked my DBA to create a table with an XML column, DB Artisan the tool they use instead of SQL Server Management Studio puked all over itself, then again it also converted my varchar(max) and varbinary(max) columns into varchar(1) and varbinary(1)).
That being said I do "persist" .NET objects to XML in the database in cases where my only goal is to provide long-term statefullness to my application.
As for space concerns of the XML in the DB, you have a few options (http://msdn.microsoft.com/en-us/library/ms345115.aspx) but "The XML data supplied by a user is stored internally in a binary format" so disk space concerns should be minimal.
As a developer I like XML persistence over Binary. Binary is defiantly faster to serialize and smaller "on the wire", however I think that in most cases the interoperability and human readability of XML outweighs the benefits of binary serialization. (Heck MSFT is using it for Office!)
NOTE your XML documents are limited to 2GB each.