Sql Server XML 列替代文档数据库？
是否可以使用 Sql Server XML 列来替代真正的文档数据库（例如 Couch 或 Mongo）？
如果我要创建一个带有 guid PK Id 和文档的 XML 列的表。 与使用文档数据库相比，主要问题是什么？
Sql Server 支持在 XML 列上建立索引，因此查询不应该是完全可怕的吗？
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（4）
您在这里有几个问题：
是否可以使用 Sql Server XML 列来替代真正的文档数据库（例如 Couch 或 Mongo）？ 是的，您可以使用它作为替代品，但是，如果您只存储 XML 并且不利用 SQL Server 的任何关系工具，您可能不会对性能感到满意。
如果我要创建一个带有 guid PK Id 和文档的 XML 列的表。与使用文档数据库相比，主要问题是什么？ 简而言之，就是横向扩展。 SQL Server 不能很好地扩展这种事情。您可以通过复制来做到这一点，但相对于“真实的”文档数据库来说，管理起来很痛苦。
Sql Server 支持在 XML 列上建立索引，因此查询应该不会很糟糕？ 问题是 SQL Server 的 XML 索引可能会占用原始数据数倍的存储空间。这些索引无法在线维护（如在碎片整理中），因此您最终会在维护窗口期间遇到锁定问题。
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.
我正在对此进行一些实验：
http://rogeralsing.com/2011/03/02/linq- to-sqlxml-projections/
查询速度“不错”，我不会用它来扩展。
但是在标准基础设施上运行无模式存储的乐趣是非常好的。
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.
是的，你可以。将文档存储在 SqlServer XML 列中是可行的，如果您使用标准 XML 序列化，那么您将获得一个不错的 ACID 兼容键/值存储。此外，它还允许您相对轻松地对其进行查询，并且您可以将结果连接到以更相关的方式存储的数据。我们这样做，它有效。如果将内容存储在 XML 字段中，存储需求比使用 NTEXT 低很多，并且查询会更灵活、更快速。
SqlServer 无法为您提供的（与 mongo 相比）是副本集的无缝故障转移和 mongo 的自动分片。此外，诸如增加文档深处的特定属性之类的原子操作很困难（尽管使用 XQuery 更新功能并非不可能）。大多数 NoSql 数据库的更新往往更快，因为它们更宽松地遵守“数据仅在磁盘上安全”的原则。
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.
是的，这是可能的。至于这是否是一个好主意，这只是我的 2 美分...
在 XML 数据类型出现之前，我开发了一个将 XML 存储在 NTEXT 列中的系统 - 这并不令人愉快，并且要从 XML 数据类型中获得任何实际用途数据意味着将其中一些数据分解成关系形式。
好的，XML 数据类型现在可以更轻松地查询 XML blob 并提取某些值/对它们进行索引。但就我个人而言，一般来说，我不会。我并不是说永远不要使用 XML，因为存在这样的情况 - 相反，如果这就是您的全部计划，那么我会想“这是适合这项工作的工具吗”。使用 RDBMS 作为文档数据库让我感到有点不安。而像 MongoDB 这样的东西是从头开始构建的文档数据库。
老实说，我还没有对将数据存储为 XML 进行任何性能测试，因此我无法向您提供有关性能的指示。有兴趣知道它的大规模表现如何。
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.