微软认证考试70-461 Work with Data 数据处理 --27%比重--（5.5）

附注：微软认证考试70-461范围

1. Create Database Objects创建数据库对象 (24%)
2. Work with Data数据处理 (27%)
3. Modify Data数据修改 (24%)
4. Troubleshoot & Optimize故障排解及SQL优化 (25%)

本文是第二节Work with Data 数据处理。

第一部分直通车

第二部分直通车

第三部分直通车

第四部分直通车

第五部分：Query and manage XML data.May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server and when and when not to use it, including XML namespaces; import and export xml; xml indexing. XML数据的查询与管理。可能包含但不仅限于：理解XML类型，架构，交互，局限性和约束性；实现XML架构和XML数据的处理；了解什么时候使用XML比较恰当，包括XML命名空间；导入导出XML；XML索引。

考试归来，XQUERY基础知识和XML 数据类型方法并没有考到，而且这部分考试中也没明确指出包含这些知识，因此了解即可。

参考：http://msdn.microsoft.com/zh-cn/library/ms189075.aspx

http://msdn.microsoft.com/zh-cn/library/ms190798.aspx

XML索引

XML索引包括主XML索引和辅助XML索引。xml类型列的第一个索引必须是主 XML 索引。

主XML索引

主 XML 索引对 XML 列中 XML 实例内的所有标记、值和路径进行索引。若要创建主 XML 索引，相应 XML 列所在的表必须对该表的主键创建了聚集索引。SQL Server 使用此主键将主 XML 索引中的行与包含此 XML 列的表中的行关联起来。

主 XML 索引是xml数据类型列中的 XML BLOB 的已拆分和持久的表示形式。对于列中的每个 XML 二进制大型对象 (BLOB)，索引将创建数个数据行。该索引中的行数大约等于 XML 二进制大型对象中的节点数。当查询检索完整的 XML 实例时，SQL Server 会提供此 XML 列中的实例。XML 实例中的查询使用主 XML 索引，并可以通过使用索引本身返回标量值或 XML 子树。

辅助XML索引

为了增强搜索性能，可以创建辅助 XML 索引。必须有了主 XML 索引才能创建辅助索引。辅助索引的类型如下：

• PATH 辅助 XML 索引

• VALUE 辅助 XML 索引

• PROPERTY 辅助 XML 索引

PATH 辅助 XML 索引

如果查询通常对xml类型列指定路径表达式，则 PATH 辅助索引可以提高搜索的速度。如本主题前面所述，当查询在 WHERE 子句中指定exist()方法时主索引非常有用。如果添加 PATH 辅助索引，则您还可以改善此类查询的搜索性能。

虽然主 XML 索引避免了在运行时拆分 XML 二进制大型对象，但是它不会为基于路径表达式的查询提供最好的性能。由于是按顺序在与 XML 二进制大型对象相对应的主 XML 索引中的所有行中搜索大 XML 实例，所以按顺序搜索可能会很慢。这种情况下，对主索引中的路径值和节点值生成辅助索引可以有效地提高索引搜索的速度。在 PATH 辅助索引中，路径值和节点值是允许在搜索路径时使用更高效的查找功能的键列。查询优化器可以将 PATH 索引用于如下所示的表达式：

• /root/Location，仅指定一个路径

  或

• 
  

/root/Location/@LocationID[.="10"]，其中路径和节点值均指定。

VALUE 辅助 XML 索引

如果查询是基于值的查询，例如/Root/ProductDescription/@*[. = "Mountain Bike"]或//ProductDescription[@Name = "Mountain Bike"]，且没有完 全指定路径或路径包含有通配符，则生成基于主 XML 索引中的节点值所创建的辅助 XML 索引可以更快地获得结果。

VALUE 索引的键列是主 XML 索引的节点值和路径。如果您的工作负荷涉及到查询 XML 实例中的值，但不知道包含这些值的元素名称或属性名称，则 VALUE 索引可能会很有用。例如，以下表达式受益于 VALUE 索引：

• //author[LastName="someName"]，其中 <LastName> 元素的值已知，但是 <author> 父级可以出现在任何地方。

• /book[@* = "someValue"]，其中查询将查找包含值为"someValue"的属性的 <book> 元素。

PROPERTY 辅助索引

从单个 XML 实例检索一个或多个值的查询适用 PROPERTY 索引。当使用xml类型的value()方法检索对象属性并且知道对象的主键值时，会发生这种情况。

PROPERTY 索引是对主 XML 索引的列（PK、Path 和节点值）创建的，其中 PK 是基表的主键。

参考：http://msdn.microsoft.com/zh-cn/library/ms191497(v=sql.110).aspx

创建XML索引

创建主XML索引

CREATE PRIMARY XML INDEX 索引名 on 【表名】 (列名)

创建辅助XML索引

CREATE XML INDEX 辅助索引名称 ON 表名(列名)
USING XML INDEX 辅助索引名称
FOR PATH;
GO
CREATE XML INDEX 辅助索引名称 ON 表名(列名)
USING XML INDEX 辅助索引名称
FOR VALUE;
GO
CREATE XML INDEX 辅助索引名称 ON 表名(列名)
USING XML INDEX 辅助索引名称
FOR PROPERTY;
GO

修改XML索引

通过将选项ALLOW_ROW_LOCKS设置为OFF来修改该索引。当ALLOW_ROW_LOCKS为OFF时，不会锁定行，并且可以使用页级锁和表级锁获得对指定索引的访问权限。

ALTER INDEX 索引名 on 表名 
SET (ALLOW_ROW_LOCKS = OFF)

禁用XML索引

ALTER INDEX 索引名 on 表名 DISABLE

启用XML索引

ALTER INDEX 索引名 on 表名 REBUILD

修改 XML 索引时以下选项不可用：

• 对于 XML 索引，重新生成和设置选项 IGNORE_DUP_KEY 无效。对于辅助 XML 索引，重新生成选项 ONLINE 必须设置为 OFF。在 ALTER INDEX 语句中，不允许 DROP_EXISTING 选项。

• 对用户表中的主键约束进行的修改不会自动传播到 XML 索引中。用户必须首先删除 XML 索引，然后再重新创建它们。

• 如果指定了 ALTER INDEX ALL，则它将应用于非 XML 索引和 XML 索引。指定的索引选项可能对两种索引无效。在这种情况下，整个语句将失败。

删除XML索引

如果删除主 XML 索引，则会删除任何现有的辅助索引。

DROP INDEX 索引名 ON 表名

使用 DROP_EXISTING 索引选项创建 XML 索引。

CREATE PRIMARY XML INDEX 索引名
ON 表名(列名1)

CREATE PRIMARY XML 索引名 
ON 表名(列名2) 
WITH (DROP_EXISTING = ON)

两个创建XML索引语句使用了相同的索引名，并且第二个指定了DROP_EXISTING选项，因此会删除第一个索引，即使它们创建在不同的列上。

选择性XML索引

选择性 XML 索引是除了普通 XML 索引之外可供您使用的另外一种 XML 索引类型。选择性 XML 索引功能的目标如下：

• 改进对在 SQL Server 中存储的 XML 数据的查询性能。

• 支持更快速地对较大的 XML 数据工作负荷建立索引。

• 通过减少 XML 索引的存储成本提高可伸缩性。

普通 XML 索引的主要限制是要对整个 XML 文档建立索引。这导致了主要与索引的存储成本相关的几大缺点，例如降低了查询性能和增加了索引维护成本。

通过选择性 XML 索引功能，您可以从要建立索引的 XML 文档仅提升某些路径。在创建索引时，将对这些路径进行评估，这些路径所指向的节点将被拆分并存储于 SQL Server 中的关系表内。在大多数情况下，通过创建选择性 XML 索引而不是普通 XML 索引，可以改善性能和提高存储效率。

但在以下条件之一成立时，不推荐使用选择性 XML 索引：

• 您要映射大量节点路径。

• 您要为未知元素或文档结构内未知位置中的元素支持查询。

请将下面的 XML 片段视为由大约 500,000 行构成的表中的一个 XML 文档：

Xml

<book>
    <created>2004-03-01</created> 
    <authors>Various</authors>
    <subjects>
        <subject>English wit and humor -- Periodicals</subject>
        <subject>AP</subject> 
    </subjects>
    <title>Punch, or the London Charivari, Volume 156, April 2, 1919</title>
    <id>etext11617</id>
</book>

对由这么多行构成的这个简单架构创建主 XML 索引会用较长的时间。对此数据进行查询还面临这样一个问题，即主 XML 索引不支持选择性索引。

如果您仅需要对/book/title路径和/book/subjects路径查询此数据，则可以创建以下选择性 XML 索引：

Transact-SQL

CREATE SELECTIVE XML INDEX 索引名
ON 表名(列名)
FOR 
(
    pathTitle = '/book/title/text()' AS XQUERY 'xs:string', 
    pathAuthors = '/book/authors' AS XQUERY 'node()',
    pathId = '/book/id' AS SQL NVARCHAR(100)
)

前面的语句是您在创建选择性 XML 索引时使用的 CREATE 语法的一个很好的例子。在 CREATE 语句中，您首先为索引提供名称，并且标识要建立索引的表和 XML 列。然后，您提供要建立索引的路径。一个路径由三个部分构成：

1. 唯一标识该路径的名称。

2. 描述该路径的 XQuery 表达式。

3. 可选的优化提示。

支持的 XML 功能

选择性 XML 索引在 exist()、value() 和 nodes() 方法内支持 SQL Server 所支持的 XQuery。

• 对于 exist()、value() 和 nodes() 方法，选择性 XML 索引包含用于转换整个表达式的足够信息。

• 对于 query() 和 modify() 方法，选择性 XML 索引只能用于节点筛选。

• 对于 query() 方法，选择性 XML 索引不用于检索结果。

• 对于 modify() 方法，选择性 XML 索引不用于更新 XML 文档。

不支持的 XML 功能

选择性 XML 索引不支持在 XML 的 SQL Server 实现中支持的以下功能：

• 对具有复杂 XS 类型的节点建立索引：联合类型、序列类型和列表类型。

• 对具有二进制 XS 类型的节点建立索引：例如 base64Binary 和 hexBinary。

• 使用在末尾包含通配符*的 XPath 表达式指定要建立索引的节点：例如/a/b/c/*、/a//b/*或/a/b/*:c。

• 对子级、属性或后代以外的任何轴建立索引。//<step>的情况允许作为特例。

• 对 XML 处理指令和注释建立索引。

• 通过使用 id() 函数为节点指定和检索标识符。

必备条件

必须首先满足以下必备条件，然后才能对用户表中的 XML 列创建选择性 XML 索引：

• 聚集索引必须存在于用户表的主键上。

• 在用于选择性 XML 索引时，用户表的主键限制为 128 字节大小。

• 在用于选择性 XML 索引时，用户表的聚集键限制为 15 列。

限制

一般要求和限制

• 只能对单个 XML 列创建各个选择性 XML 索引。

• 不能对非 XML 列创建选择性 XML 索引。

• 表中的每个 XML 列只能具有一个选择性 XML 索引。

• 每个表最多可具有 249 个选择性 XML 索引。

对支持的对象的限制

不能对以下对象创建选择性 XML 索引：

1. 视图中的 XML 列。

2. 具有 XML 列的表值变量。

3. XML 类型变量。

4. 计算出的 XML 列。

5. 深度超过 128 个嵌套节点的 XML 列。

与存储相关的限制

对于可添加到索引中的 XML 文档中的节点数有一定的限制。选择性 XML 索引将 XML 文档映射到单个关系表。因此，在表的任何给定行中，选择性 XML 索引不能具有超过 1024 个非 Null 列。此外，针对稀疏列的许多限制也适用于选择性 XML 索引，因为这些索引使用稀疏列来进行存储。

在任何给定行中支持的非 Null 列的最大数目依赖于列中的数据大小：

• 在最佳情形下，在所有列的类型均为bit时支持 1024 个非 Null 列。

• 在最差情形下，在所有列均为varchar类型的大型对象时仅支持 236 个非 Null 列。

选择性 XML 索引为建立索引的每个节点路径在内部使用一到四个列。可建立索引的节点总数从 60 个节点到几百个节点不等，具体数目取决于已建立索引的路径中数据的实际大小。

• 在最差情形下，在节点路径定义中使用//映射某些或所有节点时，已建立索引的节点的最大数目是 60。

• 在最佳情形下，在节点路径定义中未使用//映射节点时，已建立索引的节点的最大数目是 200。

在您使用 CREATE 或 ALTER 语句对索引执行操作时，将重新生成选择性 XML 索引。

在您使用 CREATE 或 ALTER 语句对选择性 XML 索引执行操作时，将在单线程的脱机模式下重新生成该索引。频繁使用 ALTER 语句会对针对已建立索引的 XML 文档执行的查询的性能造成负面影响。

其他限制

• 在查询提示中不支持选择性 XML 索引。

• 在数据库优化顾问中不支持选择性 XML 索引和辅助选择性 XML 索引。

修改选择性XML索引

ALTER INDEX 索引名
ON 表名
FOR 
(
    ADD pathSubjects = '/book/subjects' as XQUERY 'node()' ,
    REMOVE pathId
)

删除选择性索引

DROP INDEX 索引名 ON 表名

参考：http://msdn.microsoft.com/zh-cn/library/ms191497.aspx

第5.1部分

第5.2部分

第5.3部分

第5.4部分