【IT168 技术文档】目标
通过这个动手实验室，您可以了解到：
创建已分区表。
创建分区对齐索引视图。
实现可调窗口应用场景将数据切换到存档表。
先决条件
在开始此实验之前，您必须：
使用 Transact-SQL 在 Microsoft® SQL Server® 数据库中创建数据库对象的经验。
实验设置
实验场景
Adventure Works Cycles 的数据仓库中有几个非常大的表。为改善此数据的存储，您决定对几个跨文件组的表进行分区。这样您就可以控制基础磁盘存储。
对几个跨文件组的表进行分区时，您希望增添功能并改善您的解决方案的性能。您决定基于订单日期分配数据，而不是随意地跨文件组分割数据。因为您只修改包含最新数据的文件组，所以这样会提高备份和还原的速度。此外，您还可以定期对文件组进行存档，以将历史数据移动到另一个表中，使活动表的大小保持在可管理的范围内。
一. 创建已分区表
在本练习中，您将创建一个分区数据仓库事实数据表。非常大的表经常需要跨几个磁盘卷存储数据。SQL Server 表无法放置在特定文件中。但是，文件组可以放置在文件中，而表可以分配给文件组。这样您就可以控制 SQL Server 中非常大的表中的数据的存储。而且，如果表跨几个文件组，定义哪些数据放置在哪个文件组中会非常有用。分区函数通过基于特定列中的值沿水平方向拆分表提供了此功能。
注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:\SQLHOLS\Partitioning\Solution\Partition Processing 文件夹中的 Partition Processing.ssmssln 解决方案中。
新建 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 身份验证
创建文件组和文件
1. 在解决方案资源管理器中，右键单击在前面步骤中添加的连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Files and Filegroups.sql，然后按 Enter。
4. 键入下面的代码(每个 FILENAME 参数都应单占一行)。
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. 单击执行。
创建分区函数
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Partition Function.sql，然后按 Enter。
4. 键入下面的代码。
CREATE PARTITION FUNCTION pf_OrderDateKey(int)
AS RANGE RIGHT
FOR VALUES(185,550)
GO
5.单击执行。
注意：分区函数提供了两个文件组之间的边界。在本例中，值是与 1 月 1 日对应的键。
创建分区方案
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Partition Scheme.sql，然后按 Enter。
4. 键入下面的代码。单击执行。
CREATE PARTITION SCHEME ps_OrderDateKey
AS PARTITION pf_OrderDateKey
TO (fg2001,fg2002,fg2003,fg2004)
GO
注意：虽然分区函数中仅列出了两个边界，但却有四个文件组在分区函数中列出。第四个文件组是作为供将来的文件组拆分使用的下一个文件组提供的。
创建已分区表
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Table.sql，然后按 Enter。
4. 键入下面的代码。
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.单击执行。
将数据插入已分区表中
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Load Data.sql，然后按 Enter。
4. 键入下面的代码。
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.单击执行。
查看分区数据
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 View Partitioned Data.sql，然后按 Enter。
4. 键入下面的代码。
SELECTProductKey,
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 打开，下一个练习还要使用此程序。
二. 创建分区对齐索引视图
在此练习中，您将创建分区对齐索引视图。索引可以包含表中所有行的数据，不过，它消除了分区的某些优势。但是，可以创建分区对齐视图，在分区对齐视图上又可以创建索引。然后，无论查询是否显式使用分区对齐视图，都可以使用这些分区对齐索引。
注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:\SQLHOLS\Partitioning\Solution\ Partition Processing 文件夹中的 Partition Processing.ssmssln 解决方案中。
创建分区对齐视图
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Views.sql，然后按 Enter。
4. 键入下面的代码。
GO
CREATE VIEW vw_InternetSales2001
WITH SCHEMABINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey]<185
GO
CREATE VIEW vw_InternetSales2002
WITH SCHEMABINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey] BETWEEN 185 AND 549
GO
CREATE VIEW vw_InternetSales2003
WITH SCHEMABINDING
AS
SELECT
[InternetSalesID],
[ProductKey],
[FullDateAlternateKey] as OrderDate,
[fisp].[OrderDateKey],
[SalesOrderNumber],
[OrderQuantity],
[UnitPrice]
FROM [dbo].[FactInternetSalesPartitioned] AS fisp
INNER JOIN [dbo].[DimTime] AS dt ON [fisp].[OrderDateKey]=[dt].[TimeKey]
WHERE [DueDateKey]>549
GO
5.单击执行。
创建索引
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Indexes.sql，然后按 Enter。
4. 键入下面的代码。
GO
CREATE UNIQUE CLUSTERED INDEX idx_CL_vw_InternetSales2001
on [dbo].[vw_InternetSales2001]
(
[OrderDateKey] ASC,
[InternetSalesID] ASC,
[ProductKey] ASC,
[OrderDate] ASC,
[SalesOrderNumber] ASC,
[OrderQuantity],
[UnitPrice])
ON ps_OrderDateKey(OrderDateKey)
GO
CREATE UNIQUE CLUSTERED INDEX idx_CL_vw_InternetSales2002
on [dbo].[vw_InternetSales2002]
(
[OrderDateKey] ASC,
[InternetSalesID] ASC,
[ProductKey] ASC,
[OrderDate] ASC,
[SalesOrderNumber] ASC,
[OrderQuantity],
[UnitPrice])
ON ps_OrderDateKey(OrderDateKey)
GO
CREATE UNIQUE CLUSTERED INDEX idx_CL_vw_InternetSales2003
on [dbo].[vw_InternetSales2003]
(
[OrderDateKey] ASC,
[InternetSalesID] ASC,
[ProductKey] ASC,
[OrderDate] ASC,
[SalesOrderNumber] ASC,
[OrderQuantity],
[UnitPrice])
ON ps_OrderDateKey(OrderDateKey)
GO
5.单击执行。
查看查询执行计划
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 View Execution Plan.sql，然后按 Enter。
4. 键入下面的代码。
SELECT ProductKey, OrderQuantity
FROM vw_InternetSales2003
WHERE OrderDate BETWEEN '01/01/2003' AND '06/06/2003'
GO
5. 在工具栏中，单击显示估计的执行计划。
6. 在得到的执行计划中，确认查询优化器选择了 idx_CL_vw_InternetSales2003 分区索引。
注意：优化器会选择较小的视图索引，而不选择较大的表索引。可使用分区对齐索引视图改善具有分区表的系统的性能。
7.保持 SQL Server Management Studio 打开，下一个练习还要使用此程序。
三. 实现可调窗口应用场景
在此练习中，您将实现一个可调窗口应用场景，通过将分区切换入表中以及从表中将分区切换出去实现此应用场景。在大多数系统中，最常使用的数据都是最新的数据。在非常大的表中，定期对时间最早的数据进行存档很有用，这样可以改善性能，对给新数据创建空间也是很有必要的。使用 SQL Server 2008 中分区表的 SPLIT、SWITCH 和 MERGE 功能，您可以极快地执行存档任务。
注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:\SQLHOLS\Partitioning\Solution\Partition Processing 文件夹中的 Partition Processing.ssmssln 解决方案中。
为存档表创建分区函数
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Archive Partition Function.sql，然后按 Enter。
4. 键入下面的代码。
CREATE PARTITION FUNCTION pf_OrderDateKeyArchive(int)
AS RANGE RIGHT
FOR VALUES(185)
GO
5.单击执行。
为存档表创建分区方案
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Archive Partition Scheme.sql，然后按 Enter。
4. 键入下面的代码。
CREATE PARTITION SCHEME ps_OrderDateKeyArchive
AS PARTITION pf_OrderDateKeyArchive
TO (fg2001,fg2002,fg2003)
GO
5. 单击执行。
为存档数据创建分区表
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Create Archive Table.sql，然后按 Enter。
4. 键入下面的代码。
CREATE TABLE [dbo].[FactInternetSalesArchive]
(
[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_ FactInternetSalesArchive] PRIMARY KEY CLUSTERED
(
[InternetSalesID],
[ProductKey],
[OrderDateKey],
[DueDateKey],
[ShipDateKey],
[CustomerKey],
[PromotionKey],
[CurrencyKey],
[SalesTerritoryKey]
)
)
ON ps_OrderDateKeyArchive(OrderDateKey)
GO
5. 单击执行。
查看分区数据
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 View Archive Data.sql，然后按 Enter。
4. 键入下面的代码。
GO
SELECT * FROM [dbo].[vw_InternetSales2001]
GO
SELECT * FROM [dbo].[FactInternetSalesArchive]
GO
5.单击执行。
注意，FactInternetSalesPartitioned 表中包含 2001 年的数据，而 FactInternetSalesArchive 表中无数据。
拆分存档分区函数以创建空分区
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Alter Archive Partition Function.sql，然后按 Enter。
4. 键入下面的代码。
ALTER PARTITION FUNCTION pf_OrderDateKeyArchive()
SPLIT RANGE(550)
GO
5. 单击执行。
注意：使用 SPLIT，您可以在分区函数中创建额外的边界。
将数据切换到存档表中的空分区
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Switch Partition.sql，然后按 Enter。
4. 键入下面的代码。
ALTER TABLE FactInternetSalesPartitioned SWITCH PARTITION 1 TO FactInternetSalesArchive PARTITION 1
GO
5. 单击执行。
注意：使用 SWITCH，您可以将数据从一个表中的分区移动到另一个表中的分区。由于可能实际上并无数据移动，因而该移动过程可以非常迅速。
合并分区函数
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Merge Partition.sql，然后按 Enter。
4. 键入下面的代码。
ALTER PARTITION FUNCTION pf_OrderDateKey()MERGE RANGE (185)
GO
ALTER PARTITION FUNCTION pf_OrderDateKeyArchive ()MERGE RANGE (185)
GO
5. 单击执行。
注意：使用 MERGE，您可以从分区函数删除边界。
拆分分区函数以便为新数据创建分区
1. 在解决方案资源管理器中，右键单击该连接，然后单击新建查询。
2. 右键单击 SQLQuery1.sql，然后单击重命名。
3. 键入 Split Partition.sql，然后按 Enter。
4. 键入下面的代码。
ALTER PARTITION FUNCTION pf_OrderDateKey()SPLIT RANGE (915)
GO
5. 单击执行。
查看分区数据
1. 返回到 View Archive Data.sql 查询窗口。
2. 单击执行。
3. 注意，FactInternetSalesPartitioned 表中没有 2001 年的数据，而 FactInternetSalesArchive 表中有数据。
注意：使用可调窗口方法，您已将数据移动到存档表中并为下一年的数据提供了新的分区函数边界。
4. 关闭 SQL Server Management Studio。如果收到保存作业的提示，请勿保存。
5. 关闭 Virtual PC，放弃更改。
