SQL Server 2008数据库创建过程和可配置选项(四)

--创建四个文件组
USER MASTER
GO

ALTER DATABASE AdventureWorks2008 ADD FILEGROUP FGPre2002
GO
ALTER DATABASE AdventureWorks2008 ADD FILE
(
NAME='AworksPre2002',
FILENAME='E:\SQLData\AworksPre2002.ndf',
SIZE=20MB,
FILEGROWTH=20%
) TO FILEGROUP FGPre2002
GO

ALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2002
GO
ALTER DATABASE AdventureWorks2008 ADD FILE
(
NAME='Aworks2002',
FILENAME='E:\SQLData\Aworks2002.ndf',
SIZE=20MB,
FILEGROWTH=20%
) TO FILEGROUP FG2002
GO

ALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2003
GO
ALTER DATABASE AdventureWorks2008 ADD FILE
(
NAME='Aworks2003',
FILENAME='E:\SQLData\Aworks2003.ndf',
SIZE=20MB,
FILEGROWTH=20%
) TO FILEGROUP FG2003
GO

ALTER DATABASE AdventureWorks2008 ADD FILEGROUP FG2004AndAfter
GO
ALTER DATABASE AdventureWorks2008 ADD FILE
(
NAME='Aworks2004AndAfter',
FILENAME='E:\SQLData\Aworks2004AndAfter.ndf',
SIZE=20MB,
FILEGROWTH=20%
) TO FILEGROUP FG2004AndAfter
GO

--创建分区函数
CREATE PARTITION FUNCTION YearFunction(datetime) AS RANGE RIGHT FOR VALUES('1/1/2002','1/1/2003','1/1/2004')

--将YearFunction创建的分区映射到之前创建的文件组
CREATE PARTITION SCHEME YearScheme
AS PARTITION YearFunction
TO (FGPre2002,FG2002,FG2003,FG2004AndAfter)

--将数据从原Transactions表中移至分区表中
USER AdventureWorks2008
GO
CREATE TABLE dbo.PartitionedTransactions(
TransactionID int NOT NULL,
ProductID int NOT NULL,
ReferenceOrderID int NOT NULL,
ReferenceOrderLineID int NOT NULL,
TransactionDate datetime NOT NULL,
TransactionType nchar(1) NOT NULL,
Quantity int NOT NULL,
ActualCost money NOT NULL,
ModifiedDate datetime NOT NULL
) ON YearScheme(TransactionDate)
GO

INSERT INTO dbo.PartitionedTransactions SELECT * FROM dbo.Transactions

--查询性能测试
USER AdventureWorks2008
GO
DBCC DROPCLEANBUFFERS
SET STATISTICS IO ON
DECLARE @BeginDate AS datetime,@EndDate AS datetime
SET @BeginDate='2002-01-01'
SET @EndDate='2002-12-31'

SELECT SUM(Quantity) AS TotalQuantity,SUM(ActualCost) AS TotalCost FROM dbo.PartitionedTransactions WHERE TransactionDate BETWEEN @BeginDate AND @EndDate