Sql Server XML columns substitute for Document DB?

Question

Is it possible to use Sql Server XML columns as a substitute for a real Document DB (such as Couch or Mongo) ?

If I were to create a table with a guid PK Id and an XML column for the document. What would be the main problems compared to using a document DB?

Sql Server supports indexing over XML columns so querying should not be completely horrible?

Adenoidectomy

Very good question! I have been using SQL XML columns as a poor mans NoSql solution for quite some time and it works well. I've been very tempted to try mongo, but adding an extra piece of infrastructure should not be taken lightly (backup strategy, updating the database itself, updating drivers...) – Imaginable

Answer 1

I'm doing some experimenting with this on: http://rogeralsing.com/2011/03/02/linq-to-sqlxml-projections/

Query speed is 'decent' , it's nothing I'd use for scaling. But the joy of schema free storage running on standard infrastructure is quite nice.

Adenoidectomy

"schema free storage running on standard infrastructure" - I 100% agree. For small projects that don't need the massive scale - I think this is a better tradeoff and reduces system complexity, if you already have sql in place. – Imaginable

Answer 2

You've got several questions in here:

Is it possible to use Sql Server XML columns as a substitute for a real Document DB (such as Couch or Mongo) ? Yes, you can use it as a substitute, but no, you probably wouldn't be satisfied with performance if you're exclusively storing XML and not leveraging any of SQL Server's relational tools.

If I were to create a table with a guid PK Id and an XML column for the document. What would be the main problems compared to using a document DB? In a nutshell, scaling out. SQL Server doesn't scale this kind of thing out well. You can do it with replication, but it's painful to manage relative to a "real" Document DB.

Sql Server supports indexing over XML columns so querying should not be completely horrible? The problem is that SQL Server's XML indexes can take several times the storage space of the original data. These indexes can't be maintained online (as in defrags), so you end up with locking issues during maintenance windows.

Stiltner

Thanks for the detailed answer. I'm currently trying out the concept in a small framework: Linq to Sql Xml -> rogeralsing.com/2011/02/28/linq-to-sqlxml – Adenoidectomy

Hi Brent, can you elaborate on the last sentence? "These indexes can't be maintained online (as in defrags), so you end up with locking issues during maintenance windows." – Scutellation

Sure. Depending on your SQL Server version and edition, you may not be able to rebuild indexes online depending on the type of fields that are involved. – Stiltner

Answer 3

Yes, you can. Storing a document inside a SqlServer XML column will work and if you use standard XML serialization that will leave you with a decent ACID complant key/value store. Also, it will allow you to do queries on it with relative ease and you can join the results to data that you store in a more relational way. We do so, it works. If you store content in XML fields, storage demands are a lot lower than using NTEXT and querying it will be more flexible and faster.

What SqlServer will not get you (comparing to mongo) is the seamless failover of replica-sets an the autosharding of mongo. Also, atomic operations like incrementing a specific property deep inside a document is hard (though not impossible with the XQuery update function). Updates tend to be faster on most NoSql databases, because they are more relaxed on the "data is only safe on disk" principle.

Niko

Answer 4

Yes, it is possible. As to whether it's a good idea, this is just my 2 cents...

Before the XML datatype came along I worked on a system storing XML in an NTEXT column - that wasn't pleasant, and to get any real use out of the data meant shredding some of that data out into relational form.

OK, the XML datatype now makes it easier to query an XML blob and to extract certain values/index them. But personally, in general, I wouldn't. I'm not saying never use XML as there are scenarios for that - rather if that's all your planning on doing then I'd be thinking "is this the right tool for the job". Using a RDBMS as a document database makes me feel a bit uneasy. Whereas something like MongoDB has been built from the ground up as a document database.

In all honesty, I haven't done any performance testing on storing data as XML so I can't give you an indication of what performance would be like. Would be interested to know how this performs at scale.

Soloma