创建分区表的步骤
现在，您对分区表的价值有了一定的了解，下一节将详细介绍实现分区表的过程以及有助于完成此过程的功能。逻辑流程如下：
虽然分区可以带来众多的好处，但也增加了实现对象的管理费用和复杂性，这可能是得不偿失的。尤其是，您可能不需要为较小的表或目前满足性能和维护要求的表分区。前面提到的销售方案使用分区减轻了移动行和数据的负担，但在决定是否实现分区时，您应考虑您的方案是否存在这种负担。
如果您正在尝试改善大型数据子集的性能和可管理性，并且已经定义了访问模式，则可以使用范围分区减少数据争用的情况，同时减少只读数据不需要分区时的维护工作。要确定分区数，应先评估您的数据中是否存在逻辑分组和模式。如果您通常一次只处理这些已定义子集中的少数几个，则应定义范围以隔离查询，使其只处理相应的数据（即，只处理特定的分区）。
有关详细信息，请参见sql/techinfo/productdoc/2000/books.asp" target="_blank">SQL Server Books Online中的“Designing Partitioned Tables and Indexes”。
为了有助于优化性能和维护，应使用文件组分离数据。文件组的数目一定程度上由硬件资源决定：一般情况下，文件组数最好与分区数相同，并且这些文件组通常位于不同的磁盘上。但是，这主要适用于打算对整个数据集进行分析的系统。如果您有多个 CPU，SQL Server 则可以并行处理多个分区，从而大大缩短处理大量复杂报表和分析的总体时间。这种情况下，可以获得并行处理以及在分区表中移入和移出分区的好处。
如果需要为多个文件放置一个分区表以获得更好的 I/O 平衡，则至少需要创建一个文件组。文件组可以由一个或多个文件构成，而每个分区必须映射到一个文件组。一个文件组可以由多个分区使用，但是为了更好地管理数据（例如，为了获得更精确的备份控制），应该对分区表进行设计，以便只有相关数据或逻辑分组的数据位于同一个文件组中。使用 ALTER DATABASE，可以添加逻辑文件组名，然后添加文件。要为 AdventureWorks 数据库创建名为 2003Q3 的文件组，请按以下方式使用 ALTER DATABASE：
ALTER DATABASE AdventureWorks ADD FILEGROUP [2003Q3]
创建文件组后，使用 ALTER DATABASE 将文件添加到该文件组中。
ALTER DATABASE AdventureWorks
ADD FILE
(NAME = N'2003Q3',
FILENAME = N'C:\AdventureWorks\2003Q3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [2003Q3]
通过在 CREATE TABLE 的 ON 子句中指定一个文件组，可以为文件创建一个表。但是，如果表未分区，则不能为多个文件组创建一个表。要为一个文件组创建表，请使用 CREATE TABLE 的 ON 子句。要创建分区表，必须先确定分区的功能机制。进行分区的标准以分区函数的形式从逻辑上与表相分离。此分区函数作为独立于表的定义存在，而这种物理分离将起到帮助作用，因为多个对象都可以使用该分区函数。因此，为表分区的第一步是创建分区函数。
范围分区必须使用边界条件进行定义。而且，即使通过 CHECK 约束对表进行了限制，也不能消除该范围任一边界的值。为了允许定期将数据移入该表，需要创建最后一个空分区。
在范围分区中，首先定义边界点：如果存在五个分区，则定义四个边界点值，并指定每个值是第一个分区的上边界 (LEFT) 还是第二个分区的下边界 (RIGHT)。根据 LEFT 或 RIGHT 指定，始终有一个空分区，因为该分区没有明确定义的边界点。
具体来讲，如果分区函数的第一个值（或边界条件）是 '20001001'，则边界分区中的值将是：
第一个分区是所有小于或等于 '20001001' 的数据
第二个分区是所有大于 '20001001' 的数据
第一个分区是所有小于 '20001001' 的数据
第二个分区是所有大于或等于 '20001001' 数据
由于范围分区可能在 datetime 数据中进行定义，因此必须了解其含义。使用 datetime 具有某种含义：即总是同时指定日期和时间。未定义时间值的日期表示时间部分为“0”的 12:00 A.M。如果将 LEFT 与此类数据结合使用，则日期为 10 月 1 日 12:00 A.M. 的数据将位于第一个分区，而 10 月份的其他数据将位于第二个分区。从逻辑上讲，最好将开始值与 RIGHT 结合使用，而将结束值与 LEFT 结合使用。下面的三个子句将创建逻辑上相同的分区结构：
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
RANGE RIGHT FOR VALUES ('20001001 00:00:00.000', '20010101 00:00:00.000', '20010401 00:00:00.000', '20010701 00:00:00.000')
RANGE RIGHT FOR VALUES ('20001001', '20010101', '20010401', '20010701')
注意：此处使用 datetime 数据类型确实增加了一定的复杂性，但您需要确保设置正确的边界情况。请注意使用 RIGHT 的简单性，因为默认时间为 12:00:00.000 A.M。对于 LEFT，复杂性增加是因为 datetime 数据类型具有精度。必须选择 23:59:59.997 的原因在于，datetime 数据无法保证毫秒级别的精度。相反，datetime 数据的精度在 3.33 毫秒内。使用 23:59:59.999 这个确切的时间值是不行的，因为该值将被舍入到最接近的时间值，即第二天的 12:00:00.000 A.M。由于进行了这种舍入，将无法正确定义边界。对于 datetime 数据，必须对明确提供的毫秒值加倍小心。
注意：分区函数还允许将函数作为分区函数定义的一部分。您可以使用 DATEADD(ms,-3,'20010101')，而不是使用 '20001231 23:59:59.997' 明确定义时间。
有关详细信息，请参见sql/techinfo/productdoc/2000/books.asp" target="_blank">SQL Server Books Online的“Transact-SQL Reference”中的“Date and Time”部分。
要在四个活动分区（每个分区代表一个日历季度）中存储四分之一的 Orders 数据，并创建第五个分区以备将来使用（还是作为占位符，用于在分区表中移入和移出数据），请将 LEFT 分区函数与以下四个边界条件结合使用：
CREATE PARTITION FUNCTION OrderDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20000930 23:59:59.997',
'20001231 23:59:59.997',
'20010331 23:59:59.997',
'20010630 23:59:59.997')
记住，定义四个边界点将创建五个分区。通过查看以下数据集检查此分区创建的数据集：
边界点 '20000930 23:59:59.997' 作为 LEFT（设置模式）：
最左侧的分区将包含所有小于或等于 '20000930 23:59:59.997' 的值
边界点 '20001231 23:59:59.997'：
第二个分区将包含所有大于 '20000930 23:59:59.997' 但小于或等于 '20001231 23:59:59.997' 的值
边界点 '20010331 23:59:59.997'：
第三个分区将包含所有大于 '20001231 23:59:59.997' 但小于或等于 '20010331 23:59:59.997' 的值
边界点 '20010630 23:59:59.997'：
第四个分区将包含所有大于 '20010331 23:59:59.997' 但小于或等于 '20010630 23:59:59.997' 的值
最后，第五个分区将包含所有大于 '20010630 23:59:59.997' 的值。
创建分区函数后，必须将其与分区架构相关联，以便将分区定向至特定的文件组。定义分区架构时，即使多个分区位于同一个文件组中，也必须为每个分区指定一个文件组。对于前面创建的范围分区 (OrderDateRangePFN)，存在五个分区；最后一个空分区将在 PRIMARY 文件组中创建。因为此分区永远不包含数据，所以不需要指定特殊的位置。
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
TO ([2000Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])
注意：如果所有分区都位于同一个文件组中，则可以使用以下更简单的语法：
CREATE PARTITION SCHEME OrderDatePScheme
AS
PARTITION OrderDateRangePFN
ALL TO ([PRIMARY])
定义分区函数（逻辑结构）和分区架构（物理结构）后，即可创建表来利用它们。表定义应使用的架构，而架构又定义函数。要将这三者结合起来，必须指定应该应用分区函数的列。范围分区始终只映射到表中的一列，此列应与分区函数中定义的边界条件的数据类型相匹配。另外，如果表应明确限制数据集（而不是从负无穷大到正无穷大），则还应添加 CHECK 约束。
CREATE TABLE [dbo].[OrdersRange]
(
[PurchaseOrderID] [int] NOT NULL,
[int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL ,
[ModifiedDate] [datetime] NULL ,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20030701'
AND [OrderDate] <= '20040630 11:59:59.997'),
[TotalDue] [money] NULL
)
ON OrderDatePScheme (OrderDate)
GO
默认情况下，分区表中创建的索引也使用相同的分区架构和分区列。如果属于这种情况，索引将与表对齐。尽管未作要求，但将表与其索引对齐可以使管理工作更容易进行，对于滑动窗口方案尤其如此。
例如，要创建唯一的索引，分区列必须是一个关键列；这将确保对相应的分区进行验证，以保证索引的唯一性。因此，如果需要在一列上对表进行分区，而必须在另一个列上创建唯一的索引，这些表和索引将无法对齐。在这种情况下，可以在唯一的列（如果是多列的唯一键，则可以是任一关键列）中对索引进行分区，或者根本就不进行分区。请注意，在分区表中移入和移出数据时，必须删除和创建此索引。
注意：如果您打算使用现有数据加载表并立即在其中添加索引，则通常可以通过以下方式获得更好的性能：先加载到未分区、未建立索引的表中，然后在加载数据后创建分区索引。通过为分区架构定义群集索引，可以在加载数据后更有效地为表分区。这也是为现有表分区的不错方法。要创建与未分区表相同的表并创建与已分区群集索引相同的群集索引，请用一个文件组目标位置替换创建表中的 ON 子句。然后，在加载数据之后为分区架构创建群集索引。
融会贯通：案例研究
如果您阅读了与分区有关的概念、优点和代码示例，则可能已对此过程有了一个很好的理解；但是，对于每个步骤，都可以使用特定的设置和选项，而且在某些情况下，还必须满足各种条件。本节将帮助您将这些内容融会贯通起来进行理解。
销售数据的使用方式经常发生变化。当前月份的数据是事务数据，而上一个月份的数据主要用于进行分析。分析通常针对月份、季度和/或年度范围的数据进行。因为不同的分析人员可能希望同时查看大量不断变化的数据，所以通过分区可以更好地隔离此活动。在此方案中，活动数据来自 283 个分支位置，而且是通过两个标准格式的 ASCII 文件传输的。在每个月第一天的上午 3 点之前，所有文件均被放置到一台中央文件服务器上。所有文件按大小进行排列，但每月平均约有 86,000 份销售（订单）。每个订单平均包含 2.63 个明细项，因此，OrderDetails 文件平均包含 226,180 行。每月增加约 2,500 万个新的 Orders 和 6,400 万个 OrderDetails 行，而历史分析服务器要使两年的数据都处于活动状态以便进行分析。两年的数据刚好低于 6 亿个 Orders 和超过 15 亿个 OrderDetails 行。因为分析通常是在同一季度的不同月份之间进行比较，或与上一年度的相同月份进行比较，所以可以使用范围分区。每个范围的边界都是按月份确定的。
按照图 11 描述的步骤，使用基于 OrderDate 的范围分区对表进行分区。了解这台新服务器的要求后，分析人员打算收集和分析连续六个月的数据，或当前年度与上一年度三个月份（例如 2003 年 1 月到 3 月与 2004 年 1 月到 3 月）的数据。要使磁盘分区最大化并隔离大多数数据组，多个文件组将使用相同的物理磁盘，但是这些文件组将相差六个月以减少磁盘争用。当前数据是 2004 年 10 月，而所有 283 个存储位置都在本地管理其当前销售。服务器上存储了从 2002 年 10 月到 2004 年 9 月的数据。为了利用新的 16 向多处理器计算机和存储区域网络，每个月的文件存储在一个文件组中，同时位于一个分区镜像 (RAID 1+0) 磁盘集上。对于数据通过文件组在逻辑驱动器上的物理布局，下图（图 12）描述了每月数据的位置。
12 个逻辑驱动器都位于 RAID 1+0 配置中，因此 Orders 和 OrderDetails 数据所需的总磁盘数为 48 个。存储区域网络支持 78 个磁盘，而另外 30 个用于事务日志、TempDB、系统数据库和其他更小的表，例如 Customers（900 万）和 Products（386,750 行）。Orders 和 OrderDetails 表都使用相同的边界条件、磁盘位置和分区架构。结果是（只看图 13 中的两个逻辑驱动器 [驱动器 E:\ 和 F:\]），相同月份的 Orders 和 OrderDetails 的数据都存储在相同的磁盘上：
虽然看起来很复杂，但创建过程非常简单。设计分区表最难的部分在于从大量数据源传输数据，即 283 个存储位置都必须使用一种标准的传输机制。但是，中央服务器上只定义了一个 Orders 表和一个 OrderDetails 表。要将两个表都创建为分区表，请先创建分区函数和分区架构。分区架构定义分区在磁盘上的物理位置，因此必须存在文件组。在此表中，文件组是必需的，因此下一步是创建文件组。每个文件组的语法都与下面的语法相同，但必须创建所有 24 个文件组。有关创建所有 24 个文件组的完整脚本，请参见 RangeCaseStudyFilegroups.sql 脚本。
注意：如果没有指定相应的驱动器号，将无法运行此脚本；但是此脚本包含一个“setup”表，可以修改此表以简化测试。您可以将驱动器号/位置更改为一个驱动器，以测试和学习语法。同时，确保将文件大小调整为 MB 而不是 GB，并根据可用的磁盘空间考虑指定一个较小的初始大小。
将为 SalesDB 数据库创建 24 个文件和文件组。每个文件和文件组都具有相同的语法，只是位置、文件名和文件组名不相同：
ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBFG1File1',
FILENAME = N'E:\SalesDB\SalesDBFG1File1.ndf',
SIZE = 20GB,
MAXSIZE = 35GB,
FILEGROWTH = 5GB)
TO FILEGROUP [FG1]
GO
创建所有 24 个文件和文件组后，即可定义分区函数和分区架构。要验证文件和文件组，请分别使用 sp_helpfile 和 sp_helpfilegroup。
分区函数将在 OrderDate 列中进行定义。使用的数据类型为 datetime，而且两个表都需要存储 OrderDate 才能根据此值对两个表进行分区。实际上，如果根据相同的键值对两个表进行分区，则分区键值属于重复信息，但它对于获得对齐优点又是必需的。而且，在大多数情况下，应该是一个相当窄的列（datetime 数据类型为 8 个字节）。如本文前面的“为范围分区创建分区函数”部分所述，此函数将是一个范围分区函数，其中的第一个边界条件位于 LEFT（第一个）分区中。
CREATE PARTITION FUNCTION TwoYearDateRangePFN(datetime)
AS
RANGE LEFT FOR VALUES ('20021031 23:59:59.997', -- 2002 年 10 月
'20021130 23:59:59.997', -- 2002 年 11 月
'20021231 23:59:59.997', -- 2002 年 12 月
'20030131 23:59:59.997', -- 2003 年 1 月
'20030228 23:59:59.997', -- 2003 年 2 月
'20030331 23:59:59.997', -- 2003 年 3 月
'20030430 23:59:59.997', -- 2003 年 4 月
'20030531 23:59:59.997', -- 2003 年 5 月
'20030630 23:59:59.997', -- 2003 年 6 月
'20030731 23:59:59.997', -- 2003 年 7 月
'20030831 23:59:59.997', -- 2003 年 8 月
'20030930 23:59:59.997', -- 2003 年 9 月
'20031031 23:59:59.997', -- 2003 年 10 月
'20031130 23:59:59.997', -- 2003 年 11 月
'20031231 23:59:59.997', -- 2003 年 12 月
'20040131 23:59:59.997', -- 2004 年 1 月
'20040229 23:59:59.997', -- 2004 年 2 月
'20040331 23:59:59.997', -- 2004 年 3 月
'20040430 23:59:59.997', -- 2004 年 4 月
'20040531 23:59:59.997', -- 2004 年 5 月
'20040630 23:59:59.997', -- 2004 年 6 月
'20040731 23:59:59.997', -- 2004 年 7 月
'20040831 23:59:59.997', -- 2004 年 8 月
'20040930 23:59:59.997') -- 2004 年 9 月
GO
因为包含了最左侧和最右侧的边界情况，所以此分区函数将创建 25 个分区。该表将保留第 25 个分区为空白。不需要为这个空分区指定特殊的文件组（因为其中永远不会包含数据）作为限制表数据的约束。要将数据定向至相应的磁盘，可以使用分区架构将分区映射到文件组。分区架构将为 24 个将要包含数据的文件组使用明确的文件组名，而为第 25 个空分区使用 PRIMARY 文件组。
CREATE PARTITION SCHEME [TwoYearDateRangePScheme]
AS
PARTITION TwoYearDateRangePFN TO
( [FG1], [FG2], [FG3], [FG4], [FG5], [FG6],
[FG7], [FG8], [FG9], [FG10],[FG11],[FG12],
[FG13],[FG14],[FG15],[FG16],[FG17],[FG18],
[FG19],[FG20],[FG21],[FG22],[FG23],[FG24],
[PRIMARY] )
GO
通过使用默认的文件组或用户定义的文件组作为未分区的表，或者使用架构创建分区表，可以使用与以前的版本支持的相同语法创建表。哪种方法更好取决于表的填充方式和创建的分区数。从性能角度看，先填充堆再建立群集索引可能要胜过在已经建立索引的表中加载数据。另外，如果有多个 CPU，您可以通过并行 BULK INSERT 语句将数据加载到表中，然后也以并行方式建立索引。对于 Orders 表，按照正常的方式创建表，然后通过 INSERT SELECT 语句（从 AdventureWorks 示例数据库中提取数据）加载现有的数据。要将 Orders 表建为分区表，请在该表的 ON 子句中指定分区架构。Orders 表是使用以下语法创建的：
CREATE TABLE SalesDB.[dbo].[Orders]
(
[PurchaseOrderID] [int] NOT NULL,
[int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NULL
CONSTRAINT OrdersRangeYear
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[TotalDue] [money] NULL
) ON TwoYearDateRangePScheme(OrderDate)
GO
因为 OrderDetails 表也将使用此架构，而且必须包含 OrderDate，所以使用以下语法创建 OrderDetails 表：
CREATE TABLE [dbo].[OrderDetails](
[OrderID] [int] NOT NULL,
[LineNumber] [smallint] NOT NULL,
[ProductID] [int] NULL,
[UnitPrice] [money] NULL,
[OrderQty] [smallint] NULL,
[ReceivedQty] [float] NULL,
[RejectedQty] [float] NULL,
[OrderDate] [datetime] NOT NULL
CONSTRAINT OrderDetailsRangeYearCK
CHECK ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001'),
[DueDate] [datetime] NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [OrderDetailsModifiedDateDFLT]
DEFAULT (getdate()),
[LineTotal] AS (([UnitPrice]*[OrderQty])),
[StockedQty] AS (([ReceivedQty]-[RejectedQty]))
) ON TwoYearDateRangePScheme(OrderDate)
GO
加载数据的下一步是通过两个 INSERT 语句处理的。这两个语句使用新的 AdventureWorks 数据库（从中复制数据）。请安装 AdventureWorks 示例数据库以复制此数据：
INSERT dbo.[Orders]
SELECT o.[PurchaseOrderID]
, o.
, o.[VendorID]
, o.[TaxAmt]
, o.[Freight]
, o.[SubTotal]
, o.[Status]
, o.[RevisionNumber]
, o.[ModifiedDate]
, o.[ShipMethodID]
, o.[ShipDate]
, o.[OrderDate]
, o.[TotalDue]
FROM AdventureWorks.Purchasing.PurchaseOrderHeader AS o
WHERE ([OrderDate] >= '20021001'
AND [OrderDate] < '20041001')
GO
INSERT dbo.[OrderDetails]
SELECT od.PurchaseOrderID
, od.LineNumber
, od.ProductID
, od.UnitPrice
, od.OrderQty
, od.ReceivedQty
, od.RejectedQty
, o.OrderDate
, od.DueDate
, od.ModifiedDate
FROM AdventureWorks.Purchasing.PurchaseOrderDetail AS od
JOIN AdventureWorks.Purchasing.PurchaseOrderHeader AS o
ON o.PurchaseOrderID = od.PurchaseOrderID
WHERE (o.[OrderDate] >= '20021001'
AND o.[OrderDate] < '20041001')
GO
现在，数据已加载到分区表中，您可以使用新的内置系统函数来确定数据所在的分区。下面的查询很有用，因为它将返回包含数据的每个分区的以下信息：每个分区内存在的行数以及最小和最大 OrderDate。此查询不会返回不包含行的分区。
SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
SELECT $partition.TwoYearDateRangePFN(od.OrderDate)
AS [Partition Number]
, min(od.OrderDate) AS [Min Order Date]
, max(od.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.OrderDetails AS od
GROUP BY $partition.TwoYearDateRangePFN(od.OrderDate)
ORDER BY [Partition Number]
GO
最后，在填充表后，可以建立群集索引。在本例中，群集索引将根据主键进行定义，因为分区键标识两个表（对于 OrderDetails，在索引中添加 LineNumber 以确保唯一性）。为分区表建立索引的默认行为是将索引与同一架构中的分区表对齐，而该架构是不需要指定的。
ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
GO
ALTER TABLE Orders
ADD CONSTRAINT OrdersPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON TwoYearDateRangePScheme(OrderDate)
GO
ALTER TABLE dbo.OrderDetails
ADD CONSTRAINT OrderDetailsPK
PRIMARY KEY CLUSTERED (OrderDate, OrderID, LineNumber)
ON TwoYearDateRangePScheme(OrderDate)
GO
连接对齐的表时，SQL Server 2005 提供了通过一个或多个步骤连接表的选项，通过此选项，可以先连接各个分区，然后将子集加起来。不管如何连接分区，SQL Server 都会评估是否可以实现某种程度的分区消除。
在下面的查询中，数据是从上一个方案中创建的 Orders 和 OrderDetails 表中查询的。该查询将只返回第三个季度的信息。通常，第三个季度包含订单处理较慢的月份，但在 2004 年，这些月份是订单最多的一些月份。在本例中，我们关心的是第三季度的 Products 趋势（订购的数量及其订单日期）。为了确保连接对齐的分区表时能够受益于分区消除，必须指定每个表的分区范围。在本例中，因为 Orders 表的主键是 OrderDate 和 OrderID 的组合键，这些表之间的连接显示表之间的 OrderDate 必须相等。SARG（搜索参数）将应用于两个分区表。检索此数据的查询如下：
SELECT o.OrderID, o.OrderDate, o.VendorID, od.ProductID, od.OrderQty
FROM dbo.Orders AS o
INNER JOIN dbo.OrderDetails AS od
ON o.OrderID = od.OrderID
AND o.OrderDate = od.OrderDate
WHERE o.OrderDate >= '20040701'
AND o.OrderDate <= '20040930 11:59:59.997'
GO
如图 14 所示，查看实际或预测的示例输出时，要查看一些关键元素：首先（使用 SQL Server Management Studio），将光标悬停在所访问的表上时，您会看到“Estimated Number of Executions”或“Number of Executions”。在本例中，可以看到一个季度或三个月的数据。每个月都有自己的分区，而且查看此数据时可以看到执行了三次：每个表一次。
如图 15 所示，SQL Server 正在消除所有不需要的分区，并且只选择包含正确数据的分区。请查看“Argument”部分中的 PARTITION ID:([PtnIds1017])，了解正在评估的内容。您可能想知道“PtnIds1017”表达式是从哪里来的。这是此查询中访问的分区的逻辑表示。如果您将光标悬停在示例顶部的“Constant Scan”上，您会发现它显示了参数 VALUES(((21)), ((22)), ((23)))。这代表分区号。
要验证每个分区并且只验证这些分区中存在的数据，请使用前面使用的查询（经过稍微修改）访问分区的新的内置系统函数：
SELECT $partition.TwoYearDateRangePFN(o.OrderDate)
AS [Partition Number]
, min(o.OrderDate) AS [Min Order Date]
, max(o.OrderDate) AS [Max Order Date]
, count(*) AS [Rows In Partition]
FROM dbo.Orders AS o
WHERE $partition.TwoYearDateRangePFN(o.OrderDate) IN (21, 22, 23)
GROUP BY $partition.TwoYearDateRangePFN(o.OrderDate)
ORDER BY [Partition Number]
GO
此时，您可以从图形上识别分区消除。可以对分区表和索引使用其他的优化方法，尤其是在它们与您要连接的表对齐的情况下。SQL Server 可以通过先连接每个分区来执行多个连接。
在同一个查询中，SQL Server 不仅消除分区，还分别在其余分区之间执行连接。除了查看每个表访问的执行次数之外，请注意与合并连接相关的信息。如果将光标悬停在合并连接的上方，您会发现合并连接执行了三次。
在图 16 中，请注意执行了额外的嵌套环连接。看上去这是在合并连接后发生的，但实际上，分区 ID 已经传递给每个表搜索或扫描操作；最后这个连接只是将两个分区数据集连接起来，确保每个数据集都符合一开始（在“Constant Scan”表达式中）定义的分区 ID。
当下一个月的数据（在本例中是 2004 年 10 月）可用时，将按特定的操作顺序使用现有的文件组、移入和移出数据。而在本销售方案中，目前 FG1 中的数据是 2002 年 10 月的数据。现在 2004 年 10 月的数据是可用的，因此，根据可用空间和存档要求，您有两个选择。记住，要将分区从表中快速移入或移出，移动操作必须只更改元数据。特别是，必须在要移入或移出的同一个文件组中创建新表（源或目标，即伪造的分区）。如果您打算继续使用相同的文件组（本例中为 FG1），则需要确定如何满足空间和存档要求。当表中没有完整的两个年度的数据时，为了最大程度地缩短时间，并且如果拥有足够的空间，您可以将当前数据（2004 年 10 月）加载到 FG1 中，而无需删除要存档的数据（2002 年 10 月）。但是，如果没有足够的空间同时保留当前月份和要存档的月份，则需要先移出旧的分区（然后再删除它）。
不管怎样，存档应该很容易，并且可能已经完成。好的存档做法是，加载和移入新分区之后立即备份文件组，而不要等到打算移出分区时再进行备份。例如，如果 RAID 阵列出现故障，则可以恢复文件组，而无需重新生成或重新加载数据。具体到本例中，因为数据库是最近才分区的，所以您可能已经在分区结构稳定后执行了完整的备份。当然，完整的数据库备份并非唯一的选择。在 SQL Server 2005 中可以实现各种各样的备份策略，而且许多备份策略都可以为备份和恢复提供更好的准确性。因为这么多的数据都是不变的，所以您可以在加载后备份各个文件组。实际上，这应该是滚动分区策略的一部分。有关详细信息，请参见 sql/techinfo/productdoc/2000/books.asp" target="_blank">SQL Server Books Online的“Administering SQL Server”中的“File and Filegroup Backups”部分。
现在，策略已经就位，您需要了解确切的处理过程和语法。语法和步骤数可能看起来很复杂，但每个月的处理过程都是相同的。通过使用动态 SQL 执行，您可以按照以下步骤轻松地使此过程自动化：
后面各节详细介绍了每个步骤的语法和最佳做法，还提供了注释，以帮助您通过动态 SQL 执行使此过程自动化。
|
1. |
创建分段表（将来的伪造分区）。这个分段表必须有一个约束，将其数据限制为只对要创建的分区有效的数据。为了获取更好的性能，将数据加载到未建立索引且未应用约束的堆中，然后在将表移入分区表之前添加约束（参见步骤 3）WITH CHECK。 CREATE TABLE SalesDB.[dbo].[OrdersOctober2004]
(
[OrderID] [int] NOT NULL,
[int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO
在自动化过程中：此表很容易创建，因为它总是代表当前月份。根据进程运行的时间，使用 DATENAME(m, getdate()) 等内置函数检测月份是很容易的。因为表的结构必须与现有表相匹配，所以每个月的主要变动是表名称。但是，您可以为每个月使用相同的名称，因为将表添加到分区中之后，表即不需要再存在。虽然将数据移入分区表后该表仍然存在，但您可以在移动操作完成后删除分段表。另外，必须更改日期范围。因为您要处理的是 datetime 数据，而在时间的存储方式方面又存在舍入问题，所以必须能够通过编程方式确定正确的毫秒值。要确定月末最后的 datetime 值，最容易的方法是将正在处理的月份加上 1 个月，然后再减去 2 或 3 毫秒。不能只减去 1 毫秒，因为 59.999 会上舍入为 .000，即下个月的第一天。可以减去 2 或 3 毫秒，因为 2 毫秒将向下舍入为 .997，而 3 毫秒等于 .997；.997 是可以存储的有效值。这样即可确定 datetime 范围的正确结束值： DECLARE @Month nchar(2),
@Year nchar(4),
@StagingDateRange nchar(10)
SELECT @Month = N'11', @Year = N'2004'
SELECT @StagingDateRange = @Year + @Month + N'01'
SELECT dateadd(ms, -2, @StagingDateRange)
每个月会重新创建表，因为它需要保留在要移入和移出数据的文件组中。要确定要处理的相应文件组，请将以下系统表查询与前面介绍的 $partition 函数结合使用。指定要移出的范围内的任何日期。这是要在其中执行所有操作的分区和文件组。带有下划线的部分需要针对特定的表、分区函数和特定的日期进行更改。 SELECT ps.name AS PSName,
dds.destination_id AS PartitionNumber,
fg.name AS FileGroupName
FROM (((sys.tables AS t
INNER JOIN sys.indexes AS i
ON (t.object_id = i.object_id))
INNER JOIN sys.partition_schemes AS ps
ON (i.data_space_id = ps.data_space_id))
INNER JOIN sys.destination_data_spaces AS dds
ON (ps.data_space_id = dds.partition_scheme_id))
INNER JOIN sys.filegroups AS fg
ON dds.data_space_id = fg.data_space_id
WHERE (t.name = 'Orders') AND (i.index_id IN (0,1)) AND
dds.destination_id = $partition.TwoYearDateRangePFN('20021001')
|
|
2. |
加载包含数据的分段表。如果文件是一致的，此过程应该通过 BULK INSERT 语句执行。
在自动化过程中：这是自动化过程最复杂的部分。您需要确保所有文件都已经加载，还应考虑并行加载这些文件。跟踪加载了哪些文件以及文件位置的表可以帮助您控制此过程。您可以创建一个 SQL Agent 作业，每隔几分钟检查一次文件，拾取新文件并执行多个 BULK INSERT 语句。 |
|
3. |
加载数据后，即可添加约束。为了使数据可信，必须添加约束 WITH CHECK。WITH CHECK 设置是默认的，因此不需要指定，但一定不能设置为 WITH NOCHECK。 |
|
4. |
为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区）具有相同的群集索引。 ALTER TABLE [OrdersOctober2004]
ADD CONSTRAINT OrdersOctober2004PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO
在自动化过程中：这是一个非常容易的步骤。使用步骤 1 中的月份和文件组信息，可以创建此群集索引。 ALTER TABLE SalesDB.[dbo].[OrdersOctober2004]
WITH CHECK
ADD CONSTRAINT OrdersRangeYearCK
CHECK ([OrderDate] >= '20041001'
AND [OrderDate] <= '20041031 23:59:59.997')
GO
|
|
1. |
创建第二个分段表。这是一个空表，用于存储移出的分区中的数据。 CREATE TABLE SalesDB.[dbo].[OrdersOctober2002]
(
[OrderID] [int] NOT NULL,
[int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] [tinyint] NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [FG1]
GO
|
|
2. |
为分段表建立索引。分段表必须与其要移入的表（成为该表的一个分区，而该分区将成为此表）具有相同的群集索引。 ALTER TABLE [OrdersOctober2002]
ADD CONSTRAINT OrdersOctober2002PK
PRIMARY KEY CLUSTERED (OrderDate, OrderID)
ON [FG1]
GO
|
|
1. |
ALTER TABLE Orders
SWITCH PARTITION 1
TO OrdersOctober2002
GO
|
|
2. |
更改分区函数以删除 2002 年 10 月的边界点。 ALTER PARTITION FUNCTION TwoYearDateRangePFN()
MERGE RANGE ('20021031 23:59:59.997')
GO
|
|
3. |
此操作还会删除文件组与分区架构之间的关联。具体来说，FG1 将不再是分区架构的一部分。因为您将滚动相同的现有 24 个分区的新数据，所以需要使 FG1 成为“下一个使用的”分区，此分区将是下一个用于拆分的分区。 ALTER PARTITION SCHEME TwoYearDateRangePScheme
NEXT USED [FG1]
GO
|
|
4. |
更改分区函数，为 2004 年 10 月添加新的边界点。 ALTER PARTITION FUNCTION TwoYearDateRangePFN()
SPLIT RANGE ('20041031 23:59:59.997')
GO
|
|
5. |
更改基础表的约束定义（如果存在），以允许新范围的数据。因为添加约束的代价可能很昂贵（需要验证数据），所以最好的做法是继续扩大日期范围，而不是删除并重新创建约束。现在，只存在一个约束 (OrdersRangeYearCK)，但以后将存在两个约束。 ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMaxOctober2004
CHECK ([OrderDate] < '20041101')
GO
ALTER TABLE Orders
ADD CONSTRAINT OrdersRangeMinNovember2002
CHECK ([OrderDate] >= '20021101')
GO
ALTER TABLE Orders
DROP CONSTRAINT OrdersRangeYearCK
GO
|
|
6. |
ALTER TABLE OrdersOctober2004
SWITCH TO Orders PARTITION 24
GO
|
因为下一个步骤（也是最后一个步骤）将存档所有数据，所以不再需要分段数据。删除表是最快的方式。
DROP TABLE dbo.OrdersOctober2002
GO
DROP TABLE dbo.OrdersOctober2004
GO
最后一步备份的对象是根据您的备份策略选择的。如果选择了基于文件或文件组的备份策略，则应执行文件或文件组备份。如果选择了基于整个数据库的备份策略，则可以执行完整数据库备份或差异备份。
BACKUP DATABASE SalesDB
FILEGROUP = 'FG1'
TO DISK = 'C:\SalesDB\SalesDB.bak'
GO
如果您的表包含的数据来自多个地区，而通常只对一个地区的数据进行分析，或者定期从每个地区接收数据，请考虑以列表的形式使用已定义的范围分区。换句话说，就是使用函数将每个分区定义为一个地区值。例如，有一家西班牙的公司，它的客户分布在西班牙、法国、德国、意大利和英国。该公司的销售数据总是按国家进行分析。该公司的表可以拥有 5 个分区，每个国家一个分区。
这样一个列表分区的创建过程与日期的范围分区几乎完全相同，不同之处在于，除了实际的分区键外，该范围的边界没有任何其他值。实际上，它是一个列表，而不是范围。尽管它是一个列表，但边界条件必须包含最左侧和最右侧。要创建 5 个分区，只需在分区函数中指定 4 个值。不需要为这些值排序（SQL Server 将在内部为它们排序），但是为了获得正确的分区数，最符合逻辑的方法是对分区值进行排序，将最大值指定给最后一个分区（定义为 LEFT 分区函数时），或者对分区值进行排序，并从倒数第二个值开始（对于 RIGHT）。
因为有 5 个分区，所以必须有 5 个文件组。在本例中，这些文件组根据存储的数据命名。脚本文件 RegionalRangeCaseStudyFilegroups.sql 充分显示了此语法。每个文件组都可以使用相同的设置进行创建，但如果数据不平衡，则不必这么做。只显示了西班牙的文件组和文件；其他四个文件组和文件中都具有相同的参数，但位于不同的驱动器上，而且具有特定于国家分区的名称。
ALTER DATABASE SalesDB
ADD FILEGROUP [Spain]
GO
ALTER DATABASE SalesDB
ADD FILE
(NAME = N'SalesDBSpain',
FILENAME = N'C:\SalesDB\SalesDBSpain.ndf',
SIZE = 1MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB)
TO FILEGROUP [Spain]
GO
下一步是创建函数，该函数将指定只有四个分区使用 LEFT 作为边界条件。在本例中，列表将包括除英国以外的所有国家，因为英国在按字母顺序排列的列表中位于最后。
CREATE PARTITION FUNCTION CustomersCountryPFN(char(7))
AS
RANGE LEFT FOR VALUES ('France', 'Germany', 'Italy', 'Spain')
GO
要将数据放入根据数据命名的文件组中，分区架构将按字母顺序列出。所有五个文件组都必须在分区架构的语法中指定。
CREATE PARTITION SCHEME [CustomersCountryPScheme]
AS
PARTITION CustomersCountryPFN
TO ([France], [Germany], [Italy], [Spain], [UK])
GO
最后，可以在新的 CustomersCountryPScheme 中创建 Customers 表。
CREATE TABLE [dbo].[Customers](
[CustomerID] [nchar](5) NOT NULL,
[CompanyName] [nvarchar](40) NOT NULL,
[ContactName] [nvarchar](30) NULL,
[ContactTitle] [nvarchar](30) NULL,
[Address] [nvarchar](60) NULL,
[City] [nvarchar](15) NULL,
[Region] [nvarchar](15) NULL,
[PostalCode] [nvarchar](10) NULL,
[Country] [char](7) NOT NULL,
[Phone] [nvarchar](24) NULL,
[Fax] [nvarchar](24) NULL
) ON CustomersCountryPScheme (Country)
GO
尽管范围分区被定义为只支持范围，但它们还为执行其他类型的分区（例如列表分区）提供了一种方法。
下载中心
进程信息库
先飞电脑技术网 →
技术文章
返回页首