详解SQL Server中创建数据仓库已分区表

Question

详解SQL Server中创建数据仓库已分区表

在本练习中，您将创建一个分区数据仓库事实数据表。非常大的表经常需要跨几个磁盘卷存储数据。SQL Server 表无法放置在特定文件中。但是，文件组可以放置在文件中，而表可以分配给文件组。这样您就可以控制 SQL Server 中非常大的表中的数据的存储。而且，如果表跨几个文件组，定义哪些数据放置在哪个文件组中会非常有用。分区函数通过基于特定列中的值沿水平方向拆分表提供了此功能。

　　注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:SQLHOLSPartitioningSolutionPartition 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:SQLHOLsPartitioningStarter

　　• 解决方案名称：Partition Processing

　　(5)确保选中创建解决方案的目录，然后单击确定。

　　(6)在解决方案资源管理器中，右键单击连接，然后单击新建连接。

　　(7)在连接到服务器对话框中，验证下列设置无误后单击确定：

　　• 服务器名称：(local)

　　• 身份验证：Windows 身份验证

　　2.创建文件组和文件

　　(1)在解决方案资源管理器中，右键单击在前面步骤中添加的连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 Files and Filegroups.sql，然后按 Enter。

　　(4)键入下面的代码(每个 FILENAME 参数都应单占一行)。

1. USE [master]
3. 　　ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2001]
5. 　　GO
7. 　　ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2002]
9. 　　GO
11. 　　ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2003]
13. 　　GO
15. 　　ALTER DATABASE [AdventureWorksDW] ADD FILEGROUP [fg2004]
17. 　　GO
19. 　　ALTER DATABASE [AdventureWorksDW] ADD FILE
21. 　　(NAME = N'AdventureWorksDW_Data2001',
23. 　　FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorksDW_Data2001.ndf',
25. 　　SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2001]
27. 　　GO
29. 　　ALTER DATABASE [AdventureWorksDW] ADD FILE
31. 　　(NAME = N'AdventureWorksDW_Data2002',
33. 　　FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorksDW_Data2002.ndf',
35. 　　SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2002]
37. 　　GO
39. 　　ALTER DATABASE [AdventureWorksDW] ADD FILE
41. 　　(NAME = N'AdventureWorksDW_Data2003',
43. 　　FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorksDW_Data2003.ndf',
45. 　　SIZE = 2048KB , FILEGROWTH = 1024KB) TO FILEGROUP [fg2003]
47. 　　GO
49. 　　ALTER DATABASE [AdventureWorksDW] ADD FILE
51. 　　(NAME = N'AdventureWorksDW_Data2004',
53. 　　FILENAME = N'C:Program FilesMicrosoft SQL ServerMSSQL10.MSSQLSERVERMSSQLDATAAdventureWorksDW_Data2004.ndf',
55. 　　SIZE = 2048KB , FILEGROWTH = 1024KB ) TO FILEGROUP [fg2004]
57. 　　GO

复制代码(5)单击执行。
　　3.创建分区函数

　　(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 Create Partition Function.sql，然后按 Enter。

　　(4)键入下面的代码。

1. USE AdventureWorksDW
3. 　　CREATE PARTITION FUNCTION pf_OrderDateKey(int)
5. 　　AS RANGE RIGHT
7. 　　FOR VALUES(185,550)
9. 　　GO

复制代码(5)单击执行。

　　注意：分区函数提供了两个文件组之间的边界。在本例中，值是与 1 月 1 日对应的键。

　　4.创建分区方案

　　(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 Create Partition Scheme.sql，然后按 Enter。

　　(4)键入下面的代码。单击执行。

1. USE AdventureWorksDW
3. 　　CREATE PARTITION SCHEME ps_OrderDateKey
5. 　　AS PARTITION pf_OrderDateKey
7. 　　TO (fg2001,fg2002,fg2003,fg2004)
9. 　　GO

复制代码注意：虽然分区函数中仅列出了两个边界，但却有四个文件组在分区函数中列出。第四个文件组是作为供将来的文件组拆分使用的下一个文件组提供的。

　　5.创建已分区表

　　(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 Create Table.sql，然后按 Enter。

　　(4)键入下面的代码。

1. USE AdventureWorksDW
3. 　　CREATE TABLE [dbo].[FactInternetSalesPartitioned]
5. 　　(
7. 　　[InternetSalesID] [int] IDENTITY(1,1) NOT NULL,
9. 　　[ProductKey] [int] NOT NULL,
11. 　　[OrderDateKey] [int] NOT NULL,
13. 　　[DueDateKey] [int] NOT NULL,
15. 　　[ShipDateKey] [int] NOT NULL,
17. 　　[CustomerKey] [int] NOT NULL,
19. 　　[PromotionKey] [int] NOT NULL,
21. 　　[CurrencyKey] [int] NOT NULL,
23. 　　[SalesTerritoryKey] [int] NOT NULL,
25. 　　[SalesOrderNumber] [nvarchar](20) NOT NULL,
27. 　　[OrderQuantity] [smallint] NULL,
29. 　　[UnitPrice] [money] NULL,
31. 　　CONSTRAINT [PK_ FactInternetSalesPartitioned] PRIMARY KEY CLUSTERED
33. 　　(
35. 　　[InternetSalesID],
37. 　　[ProductKey],
39. 　　[OrderDateKey],
41. 　　[DueDateKey],
43. 　　[ShipDateKey],
45. 　　[CustomerKey],
47. 　　[PromotionKey],
49. 　　[CurrencyKey],
51. 　　[SalesTerritoryKey]
53. 　　)
55. 　　)
57. 　　ON ps_OrderDateKey(OrderDateKey)
59. 　　GO

复制代码(5)单击执行。
　　6.将数据插入已分区表中

　　(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 Load Data.sql，然后按 Enter。

　　(4)键入下面的代码。

1. 　USE AdventureWorksDW
3. 　　INSERT INTO [dbo].[FactInternetSalesPartitioned]
5. 　　(
7. 　　[ProductKey],
9. 　　[OrderDateKey],
11. 　　[DueDateKey],
13. 　　[ShipDateKey],
15. 　　[CustomerKey],
17. 　　[PromotionKey],
19. 　　[CurrencyKey],
21. 　　[SalesTerritoryKey],
23. 　　[SalesOrderNumber],
25. 　　[OrderQuantity],
27. 　　[UnitPrice]
29. 　　)
31. 　　SELECT
33. 　　[ProductKey],
35. 　　[OrderDateKey],
37. 　　[DueDateKey],
39. 　　[ShipDateKey],
41. 　　[CustomerKey],
43. 　　[PromotionKey],
45. 　　[CurrencyKey],
47. 　　[SalesTerritoryKey],
49. 　　[SalesOrderNumber],
51. 　　[OrderQuantity],
53. 　　[UnitPrice]
55. 　　FROM [dbo].[FactInternetSales]
57. 　　GO

复制代码(5)单击执行。

　　7.查看分区数据

　　(1)在解决方案资源管理器中，右键单击该连接，然后单击新建查询。

　　(2)右键单击 SQLQuery1.sql，然后单击重命名。

　　(3)键入 View Partitioned Data.sql，然后按 Enter。

　　(4)键入下面的代码。

1. 　USE AdventureWorksDW
3. 　　SELECTProductKey,
5. 　　OrderDateKey,
7. 　　$PARTITION.pf_OrderDateKey (OrderDateKey) AS PartitionNo
9. 　　FROM FactInternetSalesPartitioned
11. 　　GO
13. 　　SELECT $PARTITION.pf_OrderDateKey (OrderDateKey) AS PartitionNo,
15. 　　COUNT(*) AS Rows FROM FactInternetSalesPartitioned
17. 　　GROUP BY $PARTITION.pf_OrderDateKey (OrderDateKey)
19. 　　ORDER BY PartitionNo
21. 　　GO

复制代码(5)单击执行。

　　(6)待查询完成后，查看结果。

　　注意：第一个结果集显示表中每行的产品密钥和订单日期密钥以及存储各行的相应分区。

　　第二个结果集显示各分区中的行数。

　　(7)保持 SQL Server Management Studio 打开，下一个练习还要使用此程序。