SQL Server中的关系型数据仓库分区策略（2）

核心提示：附录 F: 脚本以下的脚本是在本白皮书前面被称为“关系型数据仓库分区策略”的部份中所描述的策略II实现过程中用来创建数据库，分区函数，SQL Server中的关系型数据仓库分区策略（2）(3)，以及分区架构的，CREATE DATABASE [SALES]ON PRIMARY(NAME = N&

附录 F: 脚本

以下的脚本是在本白皮书前面被称为“关系型数据仓库分区策略”的部份中所描述的策略II实现过程中用来创建数据库，分区函数，以及分区架构的。

CREATE DATABASE [SALES]
ON PRIMARY
(
NAME = N'SALES_PRIMARY', FILENAME = N'D:SALESPrimarySALES_PRIMARY.MDF',
SIZE = 100MB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition1
(NAME = N'SALES_ACTIVE_PARTITION_1', FILENAME =
N'F:RAID5DATA1SALES_ACTIVE_PARTITION_1.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition2
(NAME = N'SALES_ACTIVE_PARTITION_2', FILENAME =
N'F:RAID5DATA2SALES_ACTIVE_PARTITION_2.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition3
(NAME = N'SALES_ACTIVE_PARTITION_3', FILENAME =
N'F:RAID5DATA3SALES_ACTIVE_PARTITION_3.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition4
(NAME = N'SALES_ACTIVE_PARTITION_4', FILENAME =
N'F:RAID5DATA4SALES_ACTIVE_PARTITION_4.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition5
(NAME = N'SALES_ACTIVE_PARTITION_5', FILENAME =
N'F:RAID5DATA5SALES_ACTIVE_PARTITION_5.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition6
(NAME = N'SALES_ACTIVE_PARTITION_6', FILENAME =
N'F:RAID5DATA6SALES_ACTIVE_PARTITION_6.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition7
(NAME = N'SALES_ACTIVE_PARTITION_7', FILENAME =
N'F:RAID5DATA7SALES_ACTIVE_PARTITION_7.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition8
(NAME = N'SALES_ACTIVE_PARTITION_8', FILENAME =
N'F:RAID5DATA8SALES_ACTIVE_PARTITION_8.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition9
(NAME = N'SALES_ACTIVE_PARTITION_9', FILENAME =
N'F:RAID5DATA9SALES_ACTIVE_PARTITION_9.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition10
(NAME = N'SALES_ACTIVE_PARTITION_10', FILENAME =
N'F:RAID5DATA10SALES_ACTIVE_PARTITION_10.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_DIMENSIONS
(NAME = N'SALES_DIMENSIONS_1', FILENAME =
N'F:RAID5DATA11SALES_DIMENSIONS_1.NDF', SIZE = 450GB, FILEGROWTH = 0),
(NAME = N'SALES_DIMENSIONS_2', FILENAME =
N'F:RAID5DATA12SALES_DIMENSIONS_2.NDF', SIZE = 450GB, FILEGROWTH = 0)
LOG ON　
(NAME = N'SALES_LOG', FILENAME = N'F:SQLPath8SALES_LOG.LDF',
SIZE = 120GB, FILEGROWTH = 0)
GO
CREATE PARTITION FUNCTION SALES_WEEK_PARTITION_FUNCTION (INT)
AS RANGE RIGHT FOR VALUES ( 129, 130, 131, 132, 133, 134, 135, 136 )
GO
CREATE PARTITION SCHEME SALES_WEEK_PARTITION_SCHEME AS
PARTITION SALES_WEEK_PARTITION_FUNCTION
TO
(
SALES_ActivePartition1, SALES_ActivePartition2,
SALES_ActivePartition3, SALES_ActivePartition4, SALES_ActivePartition5,
SALES_ActivePartition6, SALES_ActivePartition7,
SALES_ActivePartition8, SALES_ActivePartition9, SALES_ActivePartition10
)
GO

以下的脚本是在本白皮书前面部份描述的策略II实现过程中用来创建数据库，分区函数，以及分区架构的。

CREATE DATABASE [SALES]
ON PRIMARY
(NAME = N'SALES_PRIMARY', FILENAME = N'D:SALESPrimarySALES_PRIMARY.MDF',
SIZE = 100MB, FILEGROWTH = 0),
FILEGROUP SALES_ActivePartition
(NAME = N'SALES_ACTIVE_PARTITION_1', FILENAME =
N'F:SQLPath21SALES_ACTIVE_PARTITION_1.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_2', FILENAME =
N'F:SQLPath22SALES_ACTIVE_PARTITION_2.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_3', FILENAME =
N'F:SQLPath31SALES_ACTIVE_PARTITION_3.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_4', FILENAME =
N'F:SQLPath32SALES_ACTIVE_PARTITION_4.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_5', FILENAME =
N'F:SQLPath41SALES_ACTIVE_PARTITION_5.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_6', FILENAME =
N'F:SQLPath42SALES_ACTIVE_PARTITION_6.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_7', FILENAME =
N'F:SQLPath51SALES_ACTIVE_PARTITION_7.NDF', SIZE = 100GB, FILEGROWTH = 0),
(NAME = N'SALES_ACTIVE_PARTITION_8', FILENAME =
N'F:SQLPath61SALES_ACTIVE_PARTITION_8.NDF', SIZE = 100GB, FILEGROWTH = 0),
FILEGROUP SALES_DIMENSIONS
(NAME = N'SALES_DIMENSIONS_1', FILENAME =
N'F:RAID5DATA11SALES_DIMENSIONS_1.NDF', SIZE = 450GB, FILEGROWTH = 0),
(NAME = N'SALES_DIMENSIONS_2', FILENAME =
N'F:RAID5DATA12SALES_DIMENSIONS_2.NDF', SIZE = 450GB, FILEGROWTH = 0),
LOG ON　
(NAME = N'SALES_LOG', FILENAME =
N'F:SQLPath8SALES_LOG.LDF', SIZE = 120GB, FILEGROWTH = 0)
GO
CREATE PARTITION FUNCTION SALES_WEEK_PARTITION_FUNCTION (INT)
AS RANGE RIGHT FOR VALUES ( 129, 130, 131, 132, 133, 134, 135, 136 )
GO
CREATE PARTITION SCHEME SALES_WEEK_PARTITION_SCHEME AS
PARTITION SALES_WEEK_PARTITION_FUNCTION
ALL TO (SALES_ActivePartition)
GO