分区技巧

核心提示：sql server(WINDOWS平台上强大的数据库平台) 分区视图允许各机构调节各自系统以处理大量数据，这种功能使您能够在多个表之间对数据进行分区；在逻辑上通过视图把各个表联合起来，分区技巧，使分区尽可能对用户与开发人员透明，以前发表的几篇文章（包括我和 Kalen Delaney 发表的两篇，要不断寻找要达到目标

sql server(WINDOWS平台上强大的数据库平台) 分区视图允许各机构调节各自系统以处理大量数据。这种功能使您能够在多个表之间对数据进行分区；在逻辑上通过视图把各个表联合起来，使分区尽可能对用户与开发人员透明。以前发表的几篇文章（包括我和 Kalen Delaney 发表的两篇，其中一篇“分布式分区视图”2000 年 8 月发表，InstantDoc ID 9086，另一篇“查询分布式分区视图”2000 年 9 月发表，InstantDoc ID 9097）曾经介绍了分区视图。sql server(WINDOWS平台上强大的数据库平台) 联机丛书 (BOL) 也涉及到一些基础知识。在这篇文章中，我与您分享一些您可能不曾注意到的技巧，包括使用 Enterprise Manager 对分区进行架构更改的负作用，如何绕过对通过分区视图向表中插入数据的方式的限制，以及在分区标准中使用常量所引起的问题。

从 Enterprise Manager 中更改分区

我极为提倡使用 T-SQL，甚至是对于您通过 Enterprise Manager 能轻易执行的任务。除了可提高 T-SQL 技能，这种方法使我更好地明白我所做的操作并予以更多控制。另外，Enterprise Manager 并不总是以最有效的方式执行任务。例如，从 Enterprise Manager 对分区进行架构更改，会降低分区的性能与功能。为处理这个问题，首先运行sqlmag03/html/PartitioningTipsListing_01.txt" target="_blank">清单 1 中的脚本来创建分区 Orders2000、Orders2001 与 Orders2002，以及可更新的分区视图 Orders。

首先，需确保在检索期间 Orders 在可更新性与分区消除方面提供全面的分区功能。分区排除是指 sql server(WINDOWS平台上强大的数据库平台) 只访问符合查询筛选标准的相关分区。用以下三个定单填充 Orders。因为每个分区承载不同年份的行，所以各定单要放到不同的分区中：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(1, 'aaaa', '20000109')INSERT INTO Orders(orderid, customerid, orderdate)VALUES(2, 'aaaa', '20010118')INSERT INTO Orders(orderid, customerid, orderdate)VALUES(3, 'aaaa', '20020212')

正如在 BOL ???ù?è???????ù?Orders 分区视图满足了可更新性的所有要求，所以 INSERT 语句很成功。现在，为了验证在数据检索期间是否发生了分区排除，将 STATISTICS IO 设置为打开，并且将 Query Analyzer 中 Query 菜单下的 Show Execution Plan 选项设置为打开。注意，通过将这个选项设置为打开，当运行查询时，您请求优化器生成并使用的实际计划。此选项与 Display Estimated Execution Plan 选项（不运行查询，只显示估计计划）形成对比。

为了测试分区排除，对 Orders 发出下面的 SELECT 语句，来只检索 2000 年 1 月份的数据：

SET STATISTICS IO ONSELECT * FROM OrdersWHERE orderdate >= '20000101'AND orderdate < '20000201'

注意在 STATISTICS IO 结果中，sql server(WINDOWS平台上强大的数据库平台) 物理上只访问了 Orders2000；针对其他所有分区的 I/O 数量为 0：

Table 'Orders2002'. Scan count 0, logical reads 0, ...Table 'Orders2001'. Scan count 0, logical reads 0, ...Table 'Orders2000'. Scan count 1, logical reads 2, ...

在图形执行计划中，把鼠标指针悬停在三个群集索引搜索运算符处。注意，在黄色详细资料框中，实际执行的只有 Orders2000 分区计划中的分支（执行数目：1）；而其他的则没有被执行（执行数目：0）。

现在，假设必须把 customerid 列从 varchar(5) 增大到 varchar(10)。使用 T-SQL 可以编写一个简短、有效的脚本。这是因为 sql server(WINDOWS平台上强大的数据库平台) 不需要在物理上访问每行以增大 varchar 列，而只是在元数据中表达这个更改，所以这个脚本可以在瞬间运行。运行以下代码执行更改：

ALTER TABLE Orders2000ALTER COLUMN customerid VARCHAR(10) NOT NULLALTER TABLE Orders2001ALTER COLUMN customerid VARCHAR(10) NOT NULLALTER TABLE Orders2002ALTER COLUMN customerid VARCHAR(10) NOT NULL

运行此脚本后，尝试检索数据，然后重新运行对 Orders 的查询，并像上面所做的操作一样检查 STATISTICS IO 的计划和结果：

SELECT * FROM OrdersWHERE orderdate >= '20000101'AND orderdate < '20000201'

注意在结果中发生了分区排除。发出的 INSERT 查询也很成功：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(4, 'aaaa', '20020828')

要查看 Enterprise Manager 如何处理这个更改，单击 tempdb 数据库（分区在此数据库中创建）下的 Tables 文件夹，然后右击 Orders2000 表，选择 Design Table。在 Design Table 对话框中，把 customerid 列的大小更改为 varchar(15)。对 Orders2001 和 Orders2002 分区重复此过程。在把更改保存到一个分区之前，单击 Save Change Script，并检查 sql server(WINDOWS平台上强大的数据库平台) 生成的脚本。您应获得 sqlmag03/html/PartitioningTipsListing_02.txt" target="_blank">清单 2 对 Orders2002 所示的（简短的）脚本。

Enterprise Manager 更改列大小的效率非常低。首先，它创建了一个新表 Tmp_Orders2002，为 customerid 指定了新的大小。然后，Enterprise Manager 把数据从 Orders2002 中复制到 Tmp_Orders2002，删除 Orders2002，并把 Tmp_Orders2002 重命名为 Orders2002。最后，它还要添加一个主键约束、一个唯一约束，以及一个 CHECK 约束。

处理分区时需要特别注意 CHECK 约束。添加 CHECK 约束的代码使用 WITH NOCHECK 选项。这个选项指示 sql server(WINDOWS平台上强大的数据库平台) 跳过检查现有数据是否符合约束，而只检查将要做的修改。sql server(WINDOWS平台上强大的数据库平台) 不能保证分区中都是 2002 年的数据。 sql server(WINDOWS平台上强大的数据库平台) 通过打开或关闭约束行的 sysobjects.status 中的第十二位来跟踪是否为约束添加了 WITH NOCHECK。当这个位处于打开状态时，sql server(WINDOWS平台上强大的数据库平台) 知道表中的所有行都满足约束标准的要求并不可信。

更改了 Enterprise Manager 中所有列的大小后，尝试只检索 2000 年的数据：

SELECT * FROM OrdersWHERE orderdate >= '20000101'AND orderdate < '20000201'

因为 sql server(WINDOWS平台上强大的数据库平台) 不能确定每个表中的数据，所以它访问了所有的分区：

Table 'Orders2002'. Scan count 1, logical reads 2, ...Table 'Orders2001'. Scan count 1, logical reads 2, ...Table 'Orders2000'. Scan count 1, logical reads 2, ...

尝试在视图中插入一行：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(5, 'aaaa', '20021224')

这一次插入失败，导致出现如下错误消息：

Server: Msg 4436, Level 16, State 12, Line 1UNION ALL view 'Orders' is not updatable becausea partitioning column was not found.

要解决这个问题，就要通过运行 sqlmag03/html/PartitioningTipsListing_03.txt" target="_blank">清单 3 所示代码删除并重新创建所有 CHECK ??????现在，重新尝试 SELECT 和 INSERT 查询时，分区视图就会按预期方式工作。如果使用 T-SQL 来执行以后的架构更改，则无需解决任何问题。

INSERT 技巧

可更新分区视图的一个已证明的限制就是：在所有分区视图中不管一个列是否允许为 NULL，sql server(WINDOWS平台上强大的数据库平台) 都要求您在 INSERT 语句中指定所有值。该限制可能导致有关客户端应用程序向后兼容性方面的问题。例如，假设开发三个名为 APP1，APP2 和 APP3 的应用程序，它们在 Orders 分区视图中检索并插入数据。所有应用程序都有像这样的 INSERT 和 SELECT 语句：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(...)SELECT orderid, customerid, orderdateFROM OrdersWHERE ...

现在，假设您需要在 Orders 中增加一列而对其中一个应用程序（如 APP1）进行了更改。通过使用以下代码，您在所有分区中都增加一个可为空的列，并且刷新视图架构：

ALTER TABLE Orders2000 ADD shipperid INT NULLALTER TABLE Orders2001 ADD shipperid INT NULLALTER TABLE Orders2002 ADD shipperid INT NULLEXEC sp_refreshview Orders

您可能认为这个更改会使 APP1 操作 Orders 中的发货人 ID 数据，但是因为列的值可为空，更改将不会影响 APP2 和 APP3。但是一旦 APP2 或 APP3 试图像平常一样插入数据：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(6, 'aaaa', '20020418')

就会得到以下错误消息：

Server: Msg 4448, Level 16,State 17, Line 1Cannot INSERT into partitioned view'Orders' because values were notsupplied for all columns.

查看一下 BOL 确认这种行为已有文件记录。在这种情况下，sql server(WINDOWS平台上强大的数据库平台) 的开发人员仍采用保守的方法，要求您为所有列指定数据。基本原理是：对于相同的列各个分区可能会有不同的为空性属性。但是，仍然可以强迫 sql server(WINDOWS平台上强大的数据库平台) 接受 INSERT 语句。这些语句并不对可为空的列提供值。只是创建一个 INSTEAD OF INSERT 触发器，它把 inserted 表中的数据重新插入到视图中。这听起来奇怪，但是工作起来效果非常好。运行以下代码以创建触发器：

CREATE TRIGGER TRG_Orders_IOI_I_Insist ON Orders INSTEAD OF INSERTASINSERT INTO OrdersSELECT * FROM insertedGO

现在发出上面失败的插入，这一次成功了：

INSERT INTO Orders(orderid, customerid, orderdate)VALUES(6, 'aaaa', '20020418')

常量问题

最后一个分区技巧涉及写入 CHECK 约束的分区标准中的常量。为了说明这个问题，运行 sqlmag03/html/PartitioningTipsListing_04.txt" target="_blank">清单 4 所示的脚本以创建分区 T1、T2 和 T3 以及分区视图 V1。注意，分区列 col1 为 bigint 数据类型（8 字节），但是我在 CHECK 约束中所指定的有些常量，超出了所允许的最大常规整数值 2,147,483,647（4 字节）。

分区视图乍看似乎满足了分区排除与可更新性的所有要求，但是还有一个问题要讲。首先，尝试在视图中插入一行：

INSERT INTO V1 VALUES(11)

会出现以下错误：

Server: Msg 4436, Level 16,State 12, Line 1UNION ALL view 'V1' is not updatablebecause a partitioning column was not found.

但是您知道已经存在一个极为有效的分区列 － 或者并不存在？

为了检查检索是否有效工作，可以把值直接插入到分区中：

INSERT INTO T1 VALUES(10)INSERT INTO T2 VALUES(3000000000)INSERT INTO T3 VALUES(5000000000)

然后尝试检索应该只存在于一个分区中的数据：

SET STATISTICS IO ONSELECT * FROM V1 WHERE col1 = 10

图形执行计划结果和 STATISTICS IO 结果显示 sql server(WINDOWS平台上强大的数据库平台) 访问了所有分区：

Table 'T3'. Scan count 1, logical reads 2, ...Table 'T2'. Scan count 1, logical reads 2, ...Table 'T1'. Scan count 1, logical reads 2, ...

sql server(WINDOWS平台上强大的数据库平台) 用确定的方式解释所指定常量的数据类型。例如，sql server(WINDOWS平台上强大的数据库平台) 把 10 当作整数。但是，它把超出常规整数范围的整个数字（例如，4,000,000,000）当作带有多个零的数值，而不是 bigint。现在，请考虑以下一个 CHECK 约束中的表达式：

col1 > 4000000000

Col1 是一个 bigint 列，但是 4000000000 是数值，所以 sql server(WINDOWS平台上强大的数据库平台) 把具有低优先级数据类型的值隐式地转换为具有高优先级的值。查看一下 "Precedence, Data Type Precedence" 下的 BOL ??????????????¨???????????????±? bigint ????所以实际上分区表达式变成：

CONVERT(numeric, col1) > 4000000000

关于分区列的其中一个规则是：在 CHECK 约束表达式中不能把它指定为函数的一个参数。在此示例中，这个列隐式地变成 CONVERT() 函数的参数，所以不能把它用作分区。为了解决这个问题，把常量显式地转换为 bigint。通过运行 sqlmag03/html/PartitioningTipsListing_05.txt" target="_blank">清单 5 所示代码，删除原有 CHECK 约束并重新创建正确约束。确保 STATISTICS IO 已打开，然后尝试检索并修改视图 － 它会按预期方式工作：

INSERT INTO V1 VALUES(11)SELECT * FROM V1 WHERE col1 = 10

Imparted Wisdom

希望我已经说服您在执行架构更改时使用 T-SQL，或者至少要谨慎使用 Enterprise Manager。希望您能够了解在屏幕后的运行情况，并确保在 Enterprise Manager 不能有效运行时使用 T-SQL。

另外，在遇到限制问题，并且这个限制问题可能危及应用程序的重要组成部分时（如向后兼容性），一定不要放弃。要不断寻找要达到目标的途径。只要足够努力，总可以找到解决方案的。