Partitioned Table
可伸缩性性是数据库管理系统的一个很重要的方面，在SQL Server 2005中可伸缩性方面提供了表分区功能。
其实对于有关系弄数据库产品来说，对表、数据库和服务器进行数据分区的从而提供大数据量的支持并不是什么新鲜事，但 SQL Server 2005 提供了一个新的体系结构功能，用于对数据库中的文件组进行表分区。水平分区可根据分区架构，将一个表划分为几个较小的分组。表分区功能是针对超大型数据库（从数百吉字节到数千吉字节或更大）而设计的。超大型数据库 (VLDB) 查询性能通过分区得到了改善。通过对广大分区列值进行分区，可以对数据的子集进行管理，并将其快速、高效地重新分配给其他表。
设想一个大致的电子交易网站，有一个表存储了此网站的历史交易数据，这此数据量可能有上亿条，在以前的SQL Server版本中存储在一个表中不管对于查询性能还是维护都是件麻烦事，下面我们来看一下在SQL Server2005怎么提高性能和可管理性：
C:\SQLHOLS\Partitioning\Solution\Partition Processing 文件夹中的 Partition Processing.ssmssln 解决方案中。
1.新建 SQL Server 脚本项目
(1)从开始->所有程序菜单中的 Microsoft SQL Server 2008 程序组中启动SQL Server Management Studio。
(2)在连接到服务器对话框中，验证下列设置无误后单击连接：
• 服务器类型：数据库引擎
• 服务器名称：(local)
• 身份验证：Windows 身份验证
(3)在文件菜单上，指向新建，然后单击项目。
(4)确保选中 SQL Server 脚本，然后输入下列设置：
• 名称：Partition Processing
• 位置：C:\SQLHOLs\Partitioning\Starter
• 解决方案名称：Partition Processing
(5)确保选中创建解决方案的目录，然后单击确定。
(6)在解决方案资源管理器中，右键单击连接，然后单击新建连接。
(7)在连接到服务器对话框中，验证下列设置无误后单击确定：
• 服务器名称：(local)
• 身份验证：Windows 身份验证
2.创建文件组和文件
(1)在解决方案资源管理器中，右键单击在前面步骤中添加的连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 Files and Filegroups.sql，然后按 Enter。
(4)键入下面的代码(每个 FILENAME 参数都应单占一行)。
USE [master]
ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2001]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2002]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2003]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2004]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILE
(NAME = N'AdventureWorksDW_Data2001',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data2001.ndf',
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2001]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILE
(NAME = N'AdventureWorksDW_Data2002',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data2002.ndf',
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2002]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILE
(NAME = N'AdventureWorksDW_Data2003',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data2003.ndf',
SIZE = 2048KB , FILEGROWTH = 1024KB) TO FILEGROUP [fg2003]
GO
ALTER DATABASE [AdventureWorksDW] ADD FILE
(NAME = N'AdventureWorksDW_Data2004',
FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW_Data2004.ndf',
SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2004]
GO
(5)单击执行。
wordend 相关阅读：
- 详解对SQL Server分析服务性能进行优化
- 通过SQL Server数据仓库查看数据收集组
- SQL Server 2005成主流 2008版潜力无穷
3.创建分区函数
(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 Create Partition Function.sql，然后按 Enter。
(4)键入下面的代码。
USE AdventureWorksDW
CREATE PARTITION FUNCTION pf_OrderDateKey(int)
AS RANGE RIGHT
FOR VALUES(185,550)
GO
(5)单击执行。
注意：分区函数提供了两个文件组之间的边界。在本例中，值是与 1 月 1 日对应的键。
4.创建分区方案
(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 Create Partition Scheme.sql，然后按 Enter。
(4)键入下面的代码。单击执行。
USE AdventureWorksDW
CREATE PARTITION SCHEME ps_OrderDateKey
AS PARTITION pf_OrderDateKey
TO (fg2001,fg2002,fg2003,fg2004)
GO
注意：虽然分区函数中仅列出了两个边界，但却有四个文件组在分区函数中列出。第四个文件组是作为供将来的文件组拆分使用的下一个文件组提供的。
5.创建已分区表
(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 Create Table.sql，然后按 Enter。
(4)键入下面的代码。
USE AdventureWorksDW
CREATE TABLE [dbo].[FactInternetSalesPartitioned]
(
[InternetSalesID] [int] IDENTITY(1,1) NOT NULL,
[ProductKey] [int] NOT NULL,
[OrderDateKey] [int] NOT NULL,
[DueDateKey] [int] NOT NULL,
[ShipDateKey] [int] NOT NULL,
[CustomerKey] [int] NOT NULL,
[PromotionKey] [int] NOT NULL,
[CurrencyKey] [int] NOT NULL,
[SalesTerritoryKey] [int] NOT NULL,
[SalesOrderNumber] [nvarchar](20) NOT NULL,
[OrderQuantity] [smallint] NULL,
[UnitPrice] [money] NULL,
CONSTRAINT [PK_ FactInternetSalesPartitioned] PRIMARY KEY CLUSTERED
(
[InternetSalesID],
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
[PromotionKey],
[CurrencyKey],
[SalesTerritoryKey]
)
)
ON ps_OrderDateKey(OrderDateKey)
GO
(5)单击执行。
wordend 相关阅读：
- 详解对SQL Server分析服务性能进行优化
- 通过SQL Server数据仓库查看数据收集组
- SQL Server 2005成主流 2008版潜力无穷
6.将数据插入已分区表中
(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 Load Data.sql，然后按 Enter。
(4)键入下面的代码。
USE AdventureWorksDW
INSERT INTO [dbo].[FactInternetSalesPartitioned]
(
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
[PromotionKey],
[CurrencyKey],
[SalesTerritoryKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
)
SELECT
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
[PromotionKey],
[CurrencyKey],
[SalesTerritoryKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSales]
GO
(5)单击执行。
7.查看分区数据
(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
(2)右键单击 SQLQuery1.sql，然后单击重命名。
(3)键入 View Partitioned Data.sql，然后按 Enter。
(4)键入下面的代码。
USE AdventureWorksDW
SELECT ProductKey,
OrderDateKey,
$PARTITION.pf_OrderDateKey (OrderDateKey) AS PartitionNo
FROM FactInternetSalesPartitioned
GO
SELECT $PARTITION.pf_OrderDateKey (OrderDateKey) AS PartitionNo,
COUNT(*) AS Rows FROM FactInternetSalesPartitioned
GROUP BY $PARTITION.pf_OrderDateKey (OrderDateKey)
ORDER BY PartitionNo
GO
(5)单击执行。
(6)待查询完成后，查看结果。
注意：第一个结果集显示表中每行的产品密钥和订单日期密钥以及存储各行的相应分区。
第二个结果集显示各分区中的行数。
(7)保持 SQL Server Management Studio 打开，下一个练习还要使用此程序。
-- 创建要使用的测试数据库，Demo
USE [master]
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')
DROP DATABASE [DEMO]
CREATE DATABASE [DEMO]
--由于表分区使用使用新的体系结构，使用文件组来进行表分区，所以我们创建将要用到的6个文件组，来存储6个时间段的交易数据[<2000],[ 2001], [2002], [2003], [2004], [>2005]
ALTER DATABASE Demo ADD FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILEGROUP YEARFG6;
-- 下面为这些文件组添加文件来进行物理的数据存储
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF1', FILENAME = 'C:\ADVWORKSF1.NDF') TO FILEGROUP YEARFG1;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF2', FILENAME = 'C:\ADVWORKSF2.NDF') TO FILEGROUP YEARFG2;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF3', FILENAME = 'C:\ADVWORKSF3.NDF') TO FILEGROUP YEARFG3;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF4', FILENAME = 'C:\ADVWORKSF4.NDF') TO FILEGROUP YEARFG4;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF5', FILENAME = 'C:\ADVWORKSF5.NDF') TO FILEGROUP YEARFG5;
ALTER DATABASE Demo ADD FILE (NAME = 'YEARF6', FILENAME = 'C:\ADVWORKSF6.NDF') TO FILEGROUP YEARFG6;
-- HERE WE ASSOCIATE THE PARTITION FUNCTION TO
-- THE CREATED FILEGROUP VIA A PARTITIONING SCHEME
USE DEMO;
GO
-------------------------------------------------------
-- 创建分区函数
-------------------------------------------------------
CREATE PARTITION FUNCTION YEARPF(datetime)
AS
RANGE LEFT FOR VALUES ('01/01/2000'
,'01/01/2001'
,'01/01/2002'
,'01/01/2003'
,'01/01/2004')
-------------------------------------------------------
-- 创建分区架构
-------------------------------------------------------
CREATE PARTITION SCHEME YEARPS
AS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)
-- 创建使用此Schema的表
CREATE TABLE PARTITIONEDORDERS
(
ID INT NOT NULL IDENTITY(1,1),
DUEDATE DATETIME NOT NULL,
) ON YEARPS(DUEDATE)
--为此表填充数据
declare @DT datetime
SELECT @DT = '1999-01-01'
--start looping, stop at ending date
WHILE (@DT <= '2005-12-21')
BEGIN
INSERT INTO PARTITIONEDORDERS VALUES(@DT)
SET @DT=dateadd(yy,1,@DT)
END
-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition
SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
--
--现在我们设想一下，如果我们随着时间的流逝，现在已经到了2005年，按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就看如果新加一个分区。
--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区YEARFG6分区中，这样此分区就存储了两段partition的数据。
ALTER PARTITION SCHEME YEARPS
NEXT USED YEARFG6;
--更改分区函数
ALTER PARTITION FUNCTION YEARPF()
SPLIT RANGE ('01/01/2005')
--现在我们可以看一下我们刚才插入的行都分布在哪个Partition？
SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS
--我们可以看一下我们现在PARTITIONEDORDERS表的数据存储在哪此partition中，以及在这些分区中数据量的分布
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')
