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

dengwen3

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

注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:ServerSecurity/Database/'>SQLHOLSPartitioningSolutionPartition Processing 文件夹中的 PartitionProcessing.ssmssln 解决方案中。

1.新建ServerSecurity/Database/'>SQLServer脚本项目

(1)从开始->所有程序菜单中的 MicrosoftServerSecurity/Database/'>SQLServer2008程序组中启动ServerSecurity/Database/'>SQLServerManagementStudio。

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

· 服务器类型：数据库引擎

· 服务器名称：(local)

· 身份验证：Windows 身份验证

(3)在文件菜单上，指向新建，然后单击项目。

(4)确保选中ServerSecurity/Database/'>SQLServer脚本，然后输入下列设置：

· 名称：Partition Processing

· 位置：C:ServerSecurity/Database/'>SQLHOLsPartitioningStarter

· 解决方案名称：Partition Processing

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

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

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

· 服务器名称：(local)

· 身份验证：Windows 身份验证

2.创建文件组和文件

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

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

USE[master]　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2001]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2002]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2003]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2004]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME=N'AdventureWorksDW_Data2001',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2001.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2001]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2002',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2002.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2002]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2003',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2003.ndf',

SIZE =2048KB , FILEGROWTH =1024KB) TOFILEGROUP [fg2003]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2004',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2004.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2004]　　GO

(5)单击执行。

3.创建分区函数

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

CREATEPARTITION FUNCTIONpf_OrderDateKey(int)

ASRANGE RIGHT　　FORVALUES(185,550)

GO

(5)单击执行。

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

4.创建分区方案

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

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

USEAdventureWorksDW

CREATEPARTITION SCHEME ps_OrderDateKey

ASPARTITION pf_OrderDateKey

TO(fg2001,fg2002,fg2003,fg2004)

GO

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

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

CREATETABLE[dbo].[FactInternetSalesPartitioned]　　(

[InternetSalesID][int]IDENTITY(1,1) NOTNULL,

[ProductKey][int]NOTNULL,

[OrderDateKey][int]NOTNULL,

[DueDateKey][int]NOTNULL,

[ShipDateKey][int]NOTNULL,

[CustomerKey][int]NOTNULL,

[PromotionKey][int]NOTNULL,

[CurrencyKey][int]NOTNULL,

[SalesTerritoryKey][int]NOTNULL,

[SalesOrderNumber][nvarchar](20) NOTNULL,

[OrderQuantity][smallint]NULL,

[UnitPrice][money]NULL,

CONSTRAINT[PK_ FactInternetSalesPartitioned]PRIMARYKEYCLUSTERED　　(

[InternetSalesID],

[ProductKey],

[OrderDateKey],

[DueDateKey],

[ShipDateKey],

[CustomerKey],

[PromotionKey],

[CurrencyKey],

[SalesTerritoryKey]　　)

)

ONps_OrderDateKey(OrderDateKey)

GO

(5)单击执行。

6.将数据插入已分区表中

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

INSERTINTO[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)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

SELECTProductKey,

OrderDateKey,

$PARTITION.pf_OrderDateKey (OrderDateKey) ASPartitionNo

FROMFactInternetSalesPartitioned

GO　　SELECT$PARTITION.pf_OrderDateKey (OrderDateKey) ASPartitionNo,

COUNT(*) ASRows FROMFactInternetSalesPartitioned

GROUPBY$PARTITION.pf_OrderDateKey (OrderDateKey)

ORDERBYPartitionNo

GO

(5)单击执行。

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

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

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

(7)保持ServerSecurity/Database/'>SQLServerManagementStudio 打开，下一个练习还要使用此程序。

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

注意： 您可以复制此练习中所用的脚本，这些脚本位于 C:ServerSecurity/Database/'>SQLHOLSPartitioningSolutionPartition Processing 文件夹中的 PartitionProcessing.ssmssln 解决方案中。

1.新建ServerSecurity/Database/'>SQLServer脚本项目

(1)从开始->所有程序菜单中的 MicrosoftServerSecurity/Database/'>SQLServer2008程序组中启动ServerSecurity/Database/'>SQLServerManagementStudio。

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

· 服务器类型：数据库引擎

· 服务器名称：(local)

· 身份验证：Windows 身份验证

(3)在文件菜单上，指向新建，然后单击项目。

(4)确保选中ServerSecurity/Database/'>SQLServer脚本，然后输入下列设置：

· 名称：Partition Processing

· 位置：C:ServerSecurity/Database/'>SQLHOLsPartitioningStarter

· 解决方案名称：Partition Processing

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

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

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

· 服务器名称：(local)

· 身份验证：Windows 身份验证

2.创建文件组和文件

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

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

USE[master]　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2001]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2002]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2003]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILEGROUP [fg2004]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME=N'AdventureWorksDW_Data2001',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2001.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2001]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2002',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2002.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2002]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2003',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2003.ndf',

SIZE =2048KB , FILEGROWTH =1024KB) TOFILEGROUP [fg2003]　　GO　　ALTERDATABASE[AdventureWorksDW]ADDFILE　　(NAME =N'AdventureWorksDW_Data2004',

FILENAME =N'C:Program FilesMicrosoftServerSecurity/Database/'>SQLServerMSServerSecurity/Database/'>SQL10.MSServerSecurity/Database/'>SQLSERVERMSServerSecurity/Database/'>SQLDATAAdventureWorksDW_Data2004.ndf',

SIZE =2048KB , FILEGROWTH =1024KB ) TOFILEGROUP [fg2004]　　GO

(5)单击执行。

3.创建分区函数

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

CREATEPARTITION FUNCTIONpf_OrderDateKey(int)

ASRANGE RIGHT　　FORVALUES(185,550)

GO

(5)单击执行。

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

4.创建分区方案

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

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

USEAdventureWorksDW

CREATEPARTITION SCHEME ps_OrderDateKey

ASPARTITION pf_OrderDateKey

TO(fg2001,fg2002,fg2003,fg2004)

GO

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

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

CREATETABLE[dbo].[FactInternetSalesPartitioned]　　(

[InternetSalesID][int]IDENTITY(1,1) NOTNULL,

[ProductKey][int]NOTNULL,

[OrderDateKey][int]NOTNULL,

[DueDateKey][int]NOTNULL,

[ShipDateKey][int]NOTNULL,

[CustomerKey][int]NOTNULL,

[PromotionKey][int]NOTNULL,

[CurrencyKey][int]NOTNULL,

[SalesTerritoryKey][int]NOTNULL,

[SalesOrderNumber][nvarchar](20) NOTNULL,

[OrderQuantity][smallint]NULL,

[UnitPrice][money]NULL,

CONSTRAINT[PK_ FactInternetSalesPartitioned]PRIMARYKEYCLUSTERED　　(

[InternetSalesID],

[ProductKey],

[OrderDateKey],

[DueDateKey],

[ShipDateKey],

[CustomerKey],

[PromotionKey],

[CurrencyKey],

[SalesTerritoryKey]　　)

)

ONps_OrderDateKey(OrderDateKey)

GO

(5)单击执行。

6.将数据插入已分区表中

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

(2)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

INSERTINTO[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)右键单击ServerSecurity/Database/'>SQLQuery1.sql，然后单击重命名。

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

(4)键入下面的代码。

USEAdventureWorksDW

SELECTProductKey,

OrderDateKey,

$PARTITION.pf_OrderDateKey (OrderDateKey) ASPartitionNo

FROMFactInternetSalesPartitioned

GO　　SELECT$PARTITION.pf_OrderDateKey (OrderDateKey) ASPartitionNo,

COUNT(*) ASRows FROMFactInternetSalesPartitioned

GROUPBY$PARTITION.pf_OrderDateKey (OrderDateKey)

ORDERBYPartitionNo

GO

(5)单击执行。

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

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

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

(7)保持ServerSecurity/Database/'>SQLServerManagementStudio 打开，下一个练习还要使用此程序。