11

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?

Roger Johansson
  • 22,764
  • 18
  • 97
  • 193
  • 1
    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...) – Jon Kragh Sep 09 '11 at 12:04

4 Answers4

7

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.

Brent Ozar
  • 13,174
  • 14
  • 68
  • 91
  • Thanks for the detailed answer. I'm currently trying out the concept in a small framework: Linq to Sql Xml -> http://rogeralsing.com/2011/02/28/linq-to-sqlxml/ – Roger Johansson Feb 28 '11 at 12:50
  • 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." – Dan Ling Aug 05 '13 at 15:58
  • 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. – Brent Ozar Aug 05 '13 at 19:04
3

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.

Roger Johansson
  • 22,764
  • 18
  • 97
  • 193
  • 1
    "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. – Jon Kragh Sep 09 '11 at 12:01
2

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.

Teun D
  • 5,045
  • 1
  • 34
  • 44
0

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.

AdaTheDev
  • 142,592
  • 28
  • 206
  • 200