問題描述
我打算使用 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 數據的表的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！