SQL 2005 分区表的创建方法

USE [master]
IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')
DROP DATABASE [DEMO]

CREATE DATABASE [DEMO]

ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_1;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_2;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_3;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_4;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_5;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_6;
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_7;

-- 下面为这些文件组添加文件来进行物理的数据存储
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_1', FILENAME = 'C:/file1.NDF') TO FILEGROUP fg_CLRQ_1;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_2', FILENAME = 'C:/file2.NDF') TO FILEGROUP fg_CLRQ_2;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_3', FILENAME = 'C:/file3.NDF') TO FILEGROUP fg_CLRQ_3;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_4', FILENAME = 'C:/file4.NDF') TO FILEGROUP fg_CLRQ_4;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_5', FILENAME = 'C:/file5.NDF') TO FILEGROUP fg_CLRQ_5;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_6', FILENAME = 'C:/file6.NDF') TO FILEGROUP fg_CLRQ_6;
ALTER DATABASE Demo ADD FILE (NAME = 'f_CLRQ_7', FILENAME = 'C:/file7.NDF') TO FILEGROUP fg_CLRQ_7;

===========================================================
use demo
go
--分区函数（成立日期CLRQ）
CREATE PARTITION FUNCTION fn_CLRQ(datetime)
AS
RANGE LEFT FOR VALUES (
'01/01/2007',
'02/01/2007',
'03/01/2007',
'04/01/2007',
'05/01/2007',
'06/01/2007'
)
-------------------------------------------------------
-- 创建分区架构
-------------------------------------------------------
CREATE PARTITION SCHEME sch_CLRQ
AS PARTITION fn_CLRQ TO (
fg_CLRQ_1,
fg_CLRQ_2,
fg_CLRQ_3,
fg_CLRQ_4,
fg_CLRQ_5,
fg_CLRQ_6,
fg_CLRQ_7
)

-- 创建表,并使用sch_CLRQ架构,按CLRQ字段分区
CREATE TABLE par_History
(
ID INT NOT NULL IDENTITY(1,1),
CLRQ DATETIME NOT NULL
) on sch_CLRQ(CLRQ)

--建立聚集索引,唯一索引必须要与分区健组成符合索引
create unique clustered index IX_ID on par_History(ID,CLRQ)

--插入数据
insert into par_history select clrq from stat.dbo.history

--======================================

-- 现在我们可以看一下我们刚才插入的行都分布在哪个Partition
-- fn_CLRQ 是分区函数
SELECT *, $PARTITION.fn_CLRQ(CLRQ) FROM par_History
--统计一下各分区数据分布情况
SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('par_History')


--======================================

--新添加一个文件组
ALTER DATABASE Demo ADD FILEGROUP fg_CLRQ_8
ALTER DATABASE Demo ADD FILE
(NAME = 'f_CLRQ_8', FILENAME = 'C:/File8.NDF') TO FILEGROUP fg_CLRQ_8


--将新组与分区连接上
ALTER PARTITION SCHEME sch_CLRQ NEXT USED fg_CLRQ_8
--新分一个7月份组
--后面的数据会自动的新分开组
ALTER PARTITION FUNCTION fn_CLRQ() SPLIT RANGE ('07/01/2007')

先利用2楼的脚本创建测试环境（注：只用到“建立聚集索引,唯一索引必须要与分区健组成符合索引”那句话以前的脚本），用下面的语句给表插入数据：

insert into par_History select '2006-5-1'
insert into par_History select '2007-1-10'
insert into par_History select '2007-2-10'
insert into par_History select '2007-3-10'
insert into par_History select '2007-4-10'
insert into par_History select '2007-5-10'
insert into par_History select '2007-6-10'

此时执行下面的语句来看看这些数据分别存在什么地方的：

dbcc extentinfo(demo,par_History)

执行结果如下：
/*
file_id    page_id    pg_alloc    ext_size    object_id  index_id    partition_number partition_id        iam_chain_type      pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
3          8          1          1          2073058421  0          1                72057594038321152    In-row data          0x6100000000000000
4          8          1          1          2073058421  0          2                72057594038386688    In-row data          0x6100000000000000
5          8          1          1          2073058421  0          3                72057594038452224    In-row data          0x6100000000000000
6          8          1          1          2073058421  0          4                72057594038517760    In-row data          0x6100000000000000
7          8          1          1          2073058421  0          5                72057594038583296    In-row data          0x6100000000000000
8          8          1          1          2073058421  0          6                72057594038648832    In-row data          0x6100000000000000
9          8          1          1          2073058421  0          7                72057594038714368    In-row data          0x6100000000000000

(7 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

*/

可以看到数据确实按照分区架构来存储了。

现在，在表的ID列上创建聚集索引，然后在看看数据存在什么地方的：

create clustered index IX_ID on par_History(ID) on [primary]
go
dbcc extentinfo(demo,par_History)
go

执行结果如下：
/*
file_id    page_id    pg_alloc    ext_size    object_id  index_id    partition_number partition_id        iam_chain_type      pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1          110        1          1          2073058421  1          1                72057594038779904    In-row data          0x6000000000000000

(1 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

说明数据按照聚集索引来存储了，但分区架构的文件组里还会有数据吗？这个我也暂时不知道，大家来一起讨论。

现在删除刚才的聚集索引，并查看数据的存储情况：

drop index IX_ID on par_History
go
dbcc extentinfo(demo,par_History)
go

结果和刚才一样。

现在，让我们在分区列上创建聚集索引，并查看数据存储情况：

create clustered index Ix_CLRQ
on par_History(CLRQ)
go
dbcc extentinfo(demo,par_History)
go
执行结果如下：
/*
file_id    page_id    pg_alloc    ext_size    object_id  index_id    partition_number partition_id        iam_chain_type      pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
1          121        1          1          2073058421  1          1                72057594038910976    In-row data          0x6000000000000000

(1 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

*/

可以看到数据并没有象预料的那样进行分区存储，那么要实现分区存储怎么办呢？执行下面的语句即可：

drop index Ix_CLRQ on par_History
go
create clustered index Ix_CLRQ
on par_History(CLRQ)
on sch_CLRQ(CLRQ)--这里把索引创建分区架构上了
go
dbcc extentinfo(demo,par_History)
go

执行结果如下：
/*
file_id    page_id    pg_alloc    ext_size    object_id  index_id    partition_number partition_id        iam_chain_type      pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
3          17          1          1          2073058421  1          1                72057594039042048    In-row data          0x6000000000000000
4          17          1          1          2073058421  1          2                72057594039107584    In-row data          0x6000000000000000
5          17          1          1          2073058421  1          3                72057594039173120    In-row data          0x6000000000000000
6          17          1          1          2073058421  1          4                72057594039238656    In-row data          0x6000000000000000
7          17          1          1          2073058421  1          5                72057594039304192    In-row data          0x6000000000000000
8          17          1          1          2073058421  1          6                72057594039369728    In-row data          0x6000000000000000
9          17          1          1          2073058421  1          7                72057594039435264    In-row data          0x6000000000000000

(7 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/
可以看到数据又按照分区架构存储了。

现在让我们把这个聚集索引给删除，看看数据怎么存储的：
drop index Ix_CLRQ on par_History
go
dbcc extentinfo(demo,par_History)
go

可以发现存储没有变化。

现在创建另一个分区函数和分区架构，如下：
CREATE PARTITION FUNCTION fn_CLRQ1(int)
AS
RANGE LEFT FOR VALUES (
3,
5,
8
)
go
create PARTITION SCHEME sch_CLRQ1
AS PARTITION fn_CLRQ1 TO (
fg_CLRQ_4,
fg_CLRQ_5,
fg_CLRQ_6,
fg_CLRQ_7
)
接下来在这个新的分区架构上创建聚集索引，如下：
create unique clustered index Ix_ID
on par_History(id)
on sch_CLRQ1(ID);
go
dbcc extentinfo(demo,par_History)
go
执行结果如下：
/*
file_id    page_id    pg_alloc    ext_size    object_id  index_id    partition_number partition_id        iam_chain_type      pfs_bytes
----------- ----------- ----------- ----------- ----------- ----------- ---------------- -------------------- -------------------- ------------------
6          19          1          1          2073058421  1          1                72057594039959552    In-row data          0x6000000000000000
7          19          1          1          2073058421  1          2                72057594040025088    In-row data          0x6000000000000000
8          19          1          1          2073058421  1          3                72057594040090624    In-row data          0x6000000000000000

(3 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
*/

可以看到数据按照新的分区架构存储了，那么以前分区架构里有没有数据，如果有，怎么来查看？大家一起讨论一下。

但此时我们用下面的语句来查看，分别能看到数据分布在哪个文件组上：

SELECT *, $PARTITION.fn_CLRQ1(id) 'ID上的分区',$PARTITION.fn_CLRQ(CLRQ) 'CLRQ上的分区' FROM par_History
go

执行结果如下：

/*
ID          CLRQ                    ID上的分区      CLRQ上的分区
----------- ----------------------- ----------- -----------
1          2006-05-01 00:00:00.000 1          1
2          2007-01-10 00:00:00.000 1          2
3          2007-02-10 00:00:00.000 1          3
4          2007-03-10 00:00:00.000 2          4
5          2007-04-10 00:00:00.000 2          5
6          2007-05-10 00:00:00.000 3          6
7          2007-06-10 00:00:00.000 3          7

(7 行受影响)

*/