如何创建SQL Server 2005表分区-jimmyray-ChinaUnix博客

在数据库领域，分区是指把表中的行或表定义的目录水平划分成更小的独立逻辑单元。

实际上，SQL Server 2005中的每个表都有至少一个分区。你基本上可以把一个表或目录分解成更小的表或目录。这样做可以显著提高性能，因为你可以查询几个更小，而不是一个较大的表。

如果服务器拥有几个处理器，允许在同一个查询中并行查询分区，并且分区分别位于几个文件组中，那么这种优势就更加明显。

在SQL Server 2005之前，表分区过程更多地需要数据库管理员参与其中。被分区的表实际上是数据库中的用户表，为模拟一个大型表，它们往往被“统一”起来。

由此，数据库管理员能够定义INSTEAD OF触发器来操作基本表中的数据。在SQL Server 2005中，由于数据库引擎在后台维护分区，允许你专注于其它任务，因而管理起来更加轻松。

对数据库中的每一个表都进行分区并不现实。大型表和性能随时间推移逐渐减退的表是需要应用分区的典型情况。下面我将为你说明对一个大型表进行分区的完整步骤。

创建分区实例

我提供的例子说明如何对一个保存档案数据的表进行分区，从而提高查询性能。在深入研究实例代码前，我们先来概括说明一个分区表的构成。

分区函数

分区一个表时，这个表被水平划分成更小的表分区，这些小分区被共同用来模拟整个表。要完成这个任务，SQL Server必须知道如何把表分解成更小的部分。这时就要用到分区函数(Partition Function)。分区函数是一种逻辑关系，它根据一个数据字段中的值把一个表或目录的行映射到预先定义的分区中。这是我在例子中建立的第一个分区对象。

分区方案

你告诉SQL Server如何用分区函数水平划分一个表。你还需要指出如何将分区保存在数据库中。在SQL Server 2005中，你把一个表分区映射到一个文件组中，这个文件组基本上是一个数据库用来保存数据文件和处理日志文件的逻辑分组。每个数据库至少有一个叫做主要文件组(Primary filegroup)的文件组，以及其它用于管理和性能目的的文件组。

在分区方案(Partition Scheme)中，你可以定义所有分区被映射到同一个文件组中，或者利用这个方案把分区分解到所有文件组中。

这样做的好处在于，如果文件组分别位于不同的磁盘时，SQL Server能够更好地利用资源。如果你在一台拥有几个处理器的服务器上对分区表进行查询，这种好处会表现得更加明显。

创建一个分区表

首先要定义你用来映射表内分区的分区函数。在下面定义的分区函数中，我将使用三个分区，每个分区对应于SalesHistoryArchive表中的每一种产品类型，这个表将在稍后定义。基本上，这些分区会把SalesHistoryArchive档案表划分成三个不同的表，它们由SQL Server自动维护。

CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS RANGE

LEFT

FOR VALUES (N＇BigScreen＇, N＇Computer＇, N＇PoolTable＇)

定义函数的范围决定分区值属于哪一个边界。

RNAGE LEFT：指定分区值将小于或等于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于或等于‘BigScreen’的产品将映射到第一个分区。任何名称大于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。

RANGE RIGHT：指定分区值将小于在分区函数中定义的值。在上面使用的分区函数中一共建立了四个分区。所有名称小于‘BigScreen’的产品将映射到第一个分区。任何名称大于或等于‘BigScreen’但小于或等于‘Computer’的产品将映射到第二个分区；诸如此类。

建好分区函数后，现在我需要建立分区方案。我将把例子中的所有分区都映射到主文件组中。如果我希望将分区映射到不同的文件组，我会按文件组列表的顺序加入文件组名称。下面是创建分区方案的代码：

CREATE PARTITION SCHEME [ps_Product_Scheme] AS PARTITION

[pf_Product_Partition]

ALL TO ([PRIMARY])

现在我建立需要分区的表，我可以创建SalesHistoryArchive表并在其中加载数据。在CREATE TABLE语句末尾，分区方案使用表中的一个字段名告诉SQL Server如何映射需要分区的表中的数据。查看列表A中的代码样本。

我需要对数据进行一些查询，保证分区正常运行。下面的查询返回SalesHistoryArchive表的所有行，并使用$partition函数指出返回的行属于哪个分区：

ｓｅｌｅｃｔ $partition.[pf_Product_Partition](Product), *

FROM SalesHistoryArchive

这个查询返回所有映射到SalesHistoryArchive表中的分区：

ｓｅｌｅｃｔ * From sys.partitions

WHERE OBJECT_NAME(object_id) = ＇SalesHistoryArchive＇

接下来…

在后面的文章中，我将解释如何修改表中的这些分区，从而在其中增加新数据，并非常高效地删除旧数据。

Tim Chapman是肯塔基州路易维尔市一家银行的SQL Server数据库管理员，他有超过7年的IT行业经验。他还通过了微软SQL Server 2000和SQL Server 2005的认证。

Listing A

IF OBJECT_ID(＇SalesHistoryArchive＇)>0
DROP TABLE [SalesHistoryArchive];
GO
CREATE TABLE [dbo].[SalesHistoryArchive]
(
       [SaleID] [int] IDENTITY(1,1),
       [Product] [varchar](10) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
)ON [ps_Product_Scheme](Product)
GO

ｄｅｃｌａｒｅ @i SMALLINT
SET @i = 1
WHILE (@i <=10000)
BEGIN
    ｉｎｓｅｒｔ INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES(＇Computer＇, DATEADD(mm, @i, ＇3/11/1919＇), DATEPART(ms, GETDATE()) + (@i + 57))
    ｉｎｓｅｒｔ INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES(＇BigScreen＇, DATEADD(mm, @i, ＇3/11/1927＇), DATEPART(ms, GETDATE()) + (@i + 13))
    ｉｎｓｅｒｔ INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES(＇PoolTable＇, DATEADD(mm, @i, ＇3/11/1908＇), DATEPART(ms, GETDATE()) + (@i + 29))
    SET @i = @i + 1
END