SQL Server 2008 Partition 测试

--创建FileGroup

ALTER DATABASE AdventureWorksADDFILEGROUP [2003Q3]

ALTER DATABASE AdventureWorksADDFILEGROUP [2000Q4]

ALTER DATABASE AdventureWorksADDFILEGROUP [2001Q1]

ALTER DATABASE AdventureWorksADDFILEGROUP [2001Q2]

ALTER DATABASE AdventureWorksADDFILEGROUP [2002Q1]

 

--创建数据文件

alter database  AdventureWorks

ADD FILE

       (NAME = '2003Q3',

        FILENAME=

         'D:\MSSQL\2003Q3.ndf',

        SIZE =5MB,

        MAXSIZE=500,

        FILEGROWTH=1)

TO FILEGROUP[2003Q3]

Go

 

alter database  AdventureWorks

ADD FILE

       (NAME = '2000Q4',

        FILENAME=

         'D:\MSSQL\2000Q4.ndf',

        SIZE =5MB,

        MAXSIZE=500,

        FILEGROWTH=1)

TO FILEGROUP[2000Q4]

Go

 

alter database  AdventureWorks

ADD FILE

       (NAME = '2001Q1',

        FILENAME=

         'D:\MSSQL\2001Q1.ndf',

        SIZE =5MB,

        MAXSIZE=500,

        FILEGROWTH=1)

TO FILEGROUP[2001Q1]

Go

 

alter database  AdventureWorks

ADD FILE

       (NAME = '2001Q2',

        FILENAME=

         'D:\MSSQL\2001Q2.ndf',

        SIZE =5MB,

        MAXSIZE=500,

        FILEGROWTH=1)

TO FILEGROUP[2001Q2]

Go

 

alter database  AdventureWorks

ADD FILE

       (NAME = '2002Q1',

        FILENAME=

         'D:\MSSQL\2002Q1.ndf',

        SIZE =5MB,

        MAXSIZE=500,

        FILEGROWTH=1)

TO FILEGROUP[2002Q1]

Go

 

--创建分区函数（确定分区界限）

CREATE PARTITIONFUNCTIONOrderDateRangePFN(datetime)

AS

RANGE LEFT FOR VALUES ('20000930 23:59:59.997',

            '2000123123:59:59.997',

            '2001033123:59:59.997',

            '2001063023:59:59.997')

           

---将分区界限绑定到文件组

CREATE PARTITION SCHEME OrderDatePScheme

AS

PARTITIONOrderDateRangePFN

TO ([2003Q3], [2000Q4], [2001Q1], [2001Q2], [PRIMARY])

 

--创建分区表

CREATE TABLE [dbo].[OrdersRange] 

(

   [PurchaseOrderID] [int] NOT NULL,

   [EmployeeID] [int] NULL,

   [VendorID] [int] NULL,

   [TaxAmt] [money] NULL,

   [Freight] [money] NULL,

   [SubTotal] [money] NULL,

   [Status] [tinyint] NOTNULL,

   [RevisionNumber] [tinyint] NULL ,

   [ModifiedDate] [datetime] NULL ,

   [ShipMethodID] [tinyint] NULL,

   [ShipDate] [datetime] NOT NULL,

   [OrderDate] [datetime] NOT NULL

      CONSTRAINTOrdersRangeYear

         CHECK ([OrderDate]>='20030701'

                         AND [OrderDate] <='20040630 11:59:59.997'),

   [TotalDue] [money] NULL

)

ONOrderDatePScheme(OrderDate)

GO

  

--查询当前时间应该分布到哪个分区

select $PARTITION.OrderDateRangePFN('20010530 23:59:59.997')

 

---Split分区

ALTER PARTITION SCHEME OrderDatePScheme

NEXT USED[2002Q1]

 

Alter partitionfunctionOrderDateRangePFN() splitrange('20010430 23:59:59.997')

 

--查看当前分区数量（6）

select * from sys.partitionswhereOBJECT_ID=object_id('OrdersRange')

 

---Merge 分区

Alter partitionfunctionOrderDateRangePFN()mergerange('20010430 23:59:59.997')

 

--查看当前分区数量（5）

select * from sys.partitionswhereOBJECT_ID=object_id('OrdersRange')

 

--将Partition分区转出到dbo.Sales表

 ALTER TABLE[OrdersRange] SWITCHPARTITION 1TO dbo.Sales

 

使用Partition的好处：

1.提高查询性能（对分区字段做筛选）/并行计划。

2.管理优化比如FileGroup 备份/单个Partition维护索引/Filegroup还原