设计用于在 SQL Server 2008 R2 中存储 XML 数据的表 [英] Designing table to store XML data in SQL Server 2008 R2

问题描述

我打算使用 SQL Server 为我的应用程序存储 XML BLOB.我正在为设计决策而苦苦挣扎，并希望从在该主题方面有经验的人那里寻求任何指导方针或建议.

I am planing to use SQL Server to store XML BLOBs for my application. I am struggling with a design decision, and looking for any guidelines or advice from someone experienced in this topic.

需要存储为 XML 的数据大约有 100 个简单数据点.它们可以很容易地分为每组大约 20 个数据点的组.在应用程序的未来版本中，我们计划通过添加新的数据点来扩大数据的范围，其中一些将是分层的(列表、字典等).

The data that needs to be stored as XML has about 100 simple data points. They can easily be categorized into groups of maybe 20 data points each. In future versions of application, we plan to increase the scope of the data by adding new data points, some of which will be hierarchical (lists, dictionaries, etc).

我们预计不需要对 XML 数据执行查询.它们最多是非常简单的查询，如果需要，我们可以将任何数据点提升到关系列.

We are not anticipating needing to perform queries on the XML data. At most they will be very simple queries and we can promote any of the data points to a relational column if need be.

我不确定是应该创建一个巨大的 XML BLOB 来保存所有这些数据，还是应该将其分解为多个 XML 列.在 SQL Server 2008 R2 中是否有任何处理 XML 数据类型的最佳实践或指南可以帮助我做出最佳决策?重要吗?

I am not sure whether I should just create one giant XML BLOB to hold all of this data, or if should break it down into multiple XML columns. Are there any best practices or guidelines for dealing with XML data type in SQL Server 2008 R2 that can help me make the best decision? Does it even matter?

我已经开始使用 XML 作为数据类型，我正在尝试决定是使用一个大的 BLOB 还是将其分解为多个 XML 列.

I am already set on using XML as a datatype, I am trying to make a decision on whether I should use one large BLOB or break it up into multiple XML columns.

推荐答案

是的，这很重要！当您在 SQL Server 中将大型 XML blob 存储为 XML 数据类型时，它不会存储为文本 blob - 它被解析"和标记化"并以比您更有效的方式存储重新使用 varchar(max) 来存储文本表示.

Yes it matters! When you store a large XML blob as XML datatype inside SQL Server, then it's not stored as a textual blob - it's "parsed" and "tokenized" and stored in a significantly more efficient manner than if you're using just varchar(max) to store the textual representation.

如果它真的看起来像 XML，闻起来像 XML，而且听起来像 XML - 那么一定要使用XML 数据类型！

If it really looks like XML, smells like XML and quacks like XML - then definitely USE the XML datatype!

更新:如果您只想将 XML 作为一个整体来存储和检索 - 我认为将其分成多个块没有任何好处.SQL Server 中的 XML 数据类型最多可以容纳 2 GByte 的数据(就像 varchar(max))，您将看不到任何通过存储(和检索)多个较小的 XML 片段提高性能.

Update: if you only intend to store and retrieve the XML as a whole - I don't see any benefit in breaking it up into chunks. The XML datatype in SQL Server can hold up to 2 GByte of data (just like varchar(max)) and you won't see any performance gains from storing (and retrieving) multiple smaller XML fragments.

这篇关于设计用于在 SQL Server 2008 R2 中存储 XML 数据的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！