SQL Server 2005分区表的实现以及修改

--注意：在表中中构建好分区后，或者是添加了新的分区范围，以前的数据信息则会自动进行移动操作，以适应新的分区范围规则

--性能：由于采用了分区存储，提高了I/O性能，对于数据的查询性能有极大的提高

USE master;

 

IF EXISTS(SELECT 1 FROM sys.databases WHERE name='db_test')

    DROP DATABASE db_test;

 

--创建数据库db_test

--开始不创建分区信息

CREATE DATABASE db_test ON PRIMARY (

    NAME = N'db_test',

    FILENAME = N'E:/db_test/db_test.mdf',

    SIZE = 12MB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 3MB

)

--日志

LOG ON (

    NAME = N'db_test_Log',

    FILENAME = N'E:/db_test/db_test_Log.ldf',

    SIZE = 3MB,

    MAXSIZE = UNLIMITED,

    FILEGROWTH = 10%

);

go

--创建一个表

USE db_test

CREATE TABLE tbTest(id UNIQUEIDENTIFIER DEFAULT NEWSEQUENTIALID() NOT NULL,

           operateTime DATETIME NOT NULL,

           txt VARCHAR(MAX)

       CONSTRAINT PK_tbTest PRIMARY KEY(id, operateTime))

      

DECLARE @dt DATETIME;

 

SELECT @dt = '2010-09-02'

WHILE @dt < '2011-03-02'

BEGIN

    INSERT INTO dbo.tbTest(operateTime) VALUES(@dt)

    SET @dt = DATEADD(MONTH, 1, @dt);

END

SELECT * FROM dbo.tbTest

--添加文件组，为分区做准备

USE master;

 

ALTER DATABASE db_test ADD FILEGROUP fg_2010_10_01;

ALTER DATABASE db_test ADD FILEGROUP fg_2010_11_01;

ALTER DATABASE db_test ADD FILEGROUP fg_2010_12_01;

ALTER DATABASE db_test ADD FILEGROUP fg_2011_01_01;

 

--添加文件信息

ALTER DATABASE db_test

    ADD FILE ( NAME = N'fg_2010_10_01',

       FILENAME = N'E:/db_test/fg_2010_10_01.ndf' ,

       SIZE = 3072KB ,

       FILEGROWTH = 1024KB )

       TO FILEGROUP [fg_2010_10_01];

 

ALTER DATABASE db_test

    ADD FILE ( NAME = N'fg_2010_11_01',

       FILENAME = N'E:/db_test/fg_2010_11_01.ndf' ,

       SIZE = 3072KB ,

       FILEGROWTH = 1024KB )

       TO FILEGROUP [fg_2010_11_01];

 

ALTER DATABASE db_test

    ADD FILE ( NAME = N'fg_2010_12_01',

       FILENAME = N'E:/db_test/fg_2010_12_01.ndf' ,

       SIZE = 3072KB ,

       FILEGROWTH = 1024KB )

       TO FILEGROUP fg_2010_12_01;

      

ALTER DATABASE db_test

    ADD FILE ( NAME = N'fg_2011_01_01',

       FILENAME = N'E:/db_test/fg_2011_01_01.ndf' ,

       SIZE = 3072KB ,

       FILEGROWTH = 1024KB )

       TO FILEGROUP fg_2011_01_01;

 

--添加分区函数信息

USE db_test;

create partition function  partitionFunction_DateTime (datetime)

as range right for values (

                           --'2010-10-01', --储存到-10-31的数据             

                           '2010-11-01',  

                           '2010-12-01',   --储存-12-01到-12-31的数据

                           '2011-01-01'   

                          

) ;

 

--添加分区方案，将概念上的分区和文件组（物理文件）联系起来

create partition scheme partitionScheme_DateTime

as partition partitionFunction_DateTime to(

       fg_2010_10_01,

       fg_2010_11_01,

       fg_2010_12_01,

       fg_2011_01_01               

) ;

 

ALTER table tbTest drop constraint PK_tbTest--删除现有表的主键(对有全文索引的只能在管理器中去除主键)

 

ALTER TABLE dbo.tbTest ADD

CONSTRAINT PK_tbTest PRIMARY KEY (id, operateTime)

ON partitionScheme_DateTime(operateTime)

 

SELECT $partition.partitionFunction_DateTime(operateTime),* FROM dbo.tbTest

 

/*

    现在我们设想一下，如果我们随着时间的流逝，现在已经到了年月，

    按照我们先前的设定，我们想再想入一个分区，这时是不是重新创建表分区

    架构然后重新把数据导放到新的分区架构呢，答案是完全不用。下面我们就

    看如何新加一个分区。

*/

--更改分区架构定义语言，让下一个分区使用和现在已经存在的分区fg_2011_02_01分区中，这样此分区就存储了两段partition的数据。

 

--添加一个文件组

ALTER DATABASE db_test ADD FILEGROUP fg_2011_02_01;

 

--添加文件信息

ALTER DATABASE db_test

    ADD FILE ( NAME = N'fg_2011_02_01',

       FILENAME = N'E:/db_test/fg_2011_02_01.ndf' ,

       SIZE = 3072KB ,

       FILEGROWTH = 1024KB)

       TO FILEGROUP [fg_2011_02_01];

   

ALTER PARTITION SCHEME partitionScheme_DateTime

NEXT USED fg_2011_02_01;

 

--更改分区函数

ALTER PARTITION FUNCTION partitionFunction_DateTime()

SPLIT RANGE ('2011-02-01');

go

USE db_test

DECLARE @dt DATETIME;

 

SELECT @dt = '2010-09-10'

WHILE @dt < '2011-03-10'

BEGIN

    INSERT INTO dbo.tbTest(operateTime) VALUES(@dt)

    SET @dt = DATEADD(MONTH, 1, @dt);

END

SELECT $partition.partitionFunction_DateTime(operateTime),* FROM dbo.tbTest;

 

USE master;

IF EXISTS(SELECT 1 FROM sys.databases WHERE name='db_test')

    DROP DATABASE db_test;