主页 / dba / 问题 / 11341
Accepted
JoeGeeky
Asked: 2012-01-23 08:09:02 +0800 CST

存储 XML 数据的数据类型：VARCHAR(MAX) 或 XML

  • 772

我正在使用 SQL Server 2008 为一组新资源定义一个架构...在这种情况下，每条记录（例如 row）都需要存储 XML 片段。时; 虽然不经常；我需要查询 XML 以查找元素和属性值。如果留给我自己的设备，我会倾向于使用XML数据类型，尽管我被引导相信这是有问题的。所以这导致我的问题。

鉴于这种情况，在尝试决定将 XML 存储在XML列还是varchar(MAX)列之间时，我应该考虑哪些因素

如果有帮助……这里有一些额外的细节：

  • 尚未就这些片段（例如 XSD）使用模式做出决定
  • 碎片的大小从小到大不等
  • 所有 XML 都将是格式良好的
  • 在一天的过程中，将收集多达约 10,000 个片段，需要约 3 个月的在线查询支持
  • 针对 XML 的查询将全天发生，但应保持少量此类并发查询
sql-server-2008 database-design

3 个回答

  1. xml在尝试决定将 XML 存储在列中还是varchar(MAX)列中时，我应该考虑哪些因素

    这些因素是：

    1. 该XML类型可通过 XQuery 表达式进行查询/解析，包括能够使用FLWOR 语句和迭代

    2. 变量和列中的数据XML可以通过XML DML使用 XQuery 表达式进行内联修改。

    3. XML数据存储为 UTF-16 LE (Little Endian)，因此VARCHAR(MAX)将是一个糟糕的选择，因为它可能导致数据丢失。因此，真正的决定应该在XML和之间NVARCHAR(MAX)，因为NCHAR/NVARCHAR也是 UTF-16 LE。

    4. XML可以针对 XSD / 验证数据XML SCHEMA COLLECTION。如果未指定 XML Schema Collection，则不会进行任何验证（除了确保格式正确），但在使用NVARCHAR(MAX).

    5. XML 类型的一个主要好处是它以高度优化的格式存储（不像VARBINARY(MAX)@Oleg的答案中所述），它不存储您看到的确切字符串表示，而是具有元素和属性名称的字典并引用通过他们的 ID 给他们。它还删除了空格。尝试以下操作：

      DECLARE @Test1 XML = N'<Test><TagName>1</TagName><TagName>2</TagName></Test>';

DECLARE @String1 NVARCHAR(MAX) = CONVERT(NVARCHAR(MAX), @Test1);

SELECT DATALENGTH(@Test1) AS [XmlBytes],
       LEN(@String1) AS [StringCharacters],
       DATALENGTH(@String1) AS [StringBytes];

SET @Test1 = N'<Test><TagName>1</TagName><TagName>2</TagName><TagName>3</TagName>
<TagName>4</TagName><TagName>5</TagName><TagName>6</TagName></Test>';

SET @String1 = CONVERT(NVARCHAR(MAX), @Test1);

SELECT DATALENGTH(@Test1) AS [XmlBytes],
       LEN(@String1) AS [StringCharacters],
       DATALENGTH(@String1) AS [StringBytes];

      回报：

      XmlBytes   StringCharacters   StringBytes
56         53                 106

XmlBytes   StringCharacters   StringBytes
84         133                266

      正如您在上面的示例输出中所看到的，添加四个元素（#s 3、4、5 和 6）会为变量添加 80 个字符（因此如果使用 80 个字节VARCHAR）和 160 个字节。NVARCHAR然而，它只向 XML 变量添加了 28 个字节，这比它添加的要少VARCHAR（以防万一有人反对，VARCHAR因为XMLUTF XML-16 [大部分] 是双字节的）。这种优化可以节省大量空间，并且本身就有足够的理由使用XML数据类型。

    6. XML 数据可以通过专门的XML 索引进行索引

    • 17
  2. Best Answer

    如果对 XML 的查询将通过 sql server xml 功能发生，则使用 XML 类型存储 xml 以避免强制转换

    和

    请记住，由于 xml 验证，XML 类型的存储速度可能会慢一些，但 XML 的底层类型是普通的 varbinary(max)

    • 7

  3. 原始答案中未讨论的 NVARCHAR(MAX) 之间存储差异的主要原因是 NVARCHAR(MAX) 不使用SCSU；除了下面提到的页面压缩之外，使用 NVARCHAR(N) + NVARCHAR(MAX) 列对可以显着降低存储需求。有关 NVARCHAR(MAX)，请参阅SQL Server 2017 中的手动 SCSU“压缩”

    —

    其他答案中不存在的一个考虑因素与页面压缩有关。使用页面压缩，有一些非常具体的用例，其中将 XML 存储为 NVARCHAR(MAX)可能是有益的，具体取决于某些特定因素。

    这是一种特殊情况，只能在数据和使用情况分析后考虑。在所呈现的场景中，这似乎不太可能。

    何时考虑NVARCHAR ( MAX ) over XML：

    • 使用页面压缩。第三级是字典压缩。
    • XML 片段很小（例如，< 1/4 页字符）并且包含足够的重复值和/或具有页面压缩可以消除的公共前缀。
    • 磁盘成本为 $$$，而主要的业务驱动目标是以其他功能为代价来减少磁盘使用量。（如果磁盘空间足够，请不要打扰！）
    • 这些字段保存在行内数据中，因为 LOB 数据不受益于页面压缩。
    • 页面压缩实际上允许在单个页面上容纳更多行。（如果无法将其他行合并到单个页面上，请不要打扰！）
    • 针对字段的 XML 查询很少发生，以避免解析成本。这种方法对于“冷藏”更为有效。

    一些缺点和反驳：

    • 没有对字段中的 XML 进行本机验证，无论是键入的还是其他的。
    • 不能使用 XML 索引。
    • 每次将文本转换回 XML 类型时都要支付 CPU 成本。
    • XML 编码的页面压缩效率取决于单个页面中记录的值（包括文本节点）的重复量。
    • 页面压缩不会有利于移动到 LOB 的大型 XML 片段。XML 编码预计会在此处减少磁盘使用量。人们可能会拆分字段并将 COMPRESS 用于大型 XML 数据，尽管这样会增加另一个级别的复杂性和额外的关注点。

    在我一直在研究的一个非常具体的场景中，当应用于许多具有高重复性的小片段时，使用 NVARCHAR(MAX) 可以减少 20% 以上的磁盘使用量。YMMV。使用实际数据和用法进行测试。

    • 1

相关问题