使用SQL 2005中的分区表实现 --基于已存在的表创建分区 /*You can use this process to partition a table or index that already exists by performing the following steps: 1. Create a partition function. 2. Create a partition scheme. 3. Drop the existing clustered index. 4. Re-create the clustered index on the partition scheme. */ --以下示例基于inoutstore_raw表ID列创建分区 --1.创建分区函数 use ecustom_lz go create partition function partfunc_inoutstore_raw(int) as range left for values(10000,20000,30000,40000) --2.添加文件组 alter database ecustom_lz add filegroup [FG1]; alter database ecustom_lz add filegroup [FG2]; alter database ecustom_lz add filegroup [FG3]; alter database ecustom_lz add filegroup [FG4]; --3.添加数据库文件 alter database ecustom_lz add file (name = FG1_data,filename = 'D:\test\FG1_data.ndf',size = 3MB ) to filegroup [FG1]; alter database ecustom_lz add file (name = FG2_data,filename = 'D:\test\FG2_data.ndf',size = 3MB) to filegroup [FG2]; alter database ecustom_lz add file (name = FG3_data,filename = 'D:\test\FG3_data.ndf',size = 3MB) to filegroup [FG3]; alter database ecustom_lz add file (name = FG4_data,filename = 'D:\test\FG4_data.ndf',size = 3MB) to filegroup [FG4]; go/*alter database ecustom_lz remove file FG1_data */ --4.关联分区函数和架构 create partition scheme partfunc_inoutstore_raw_scheme as partition partfunc_inoutstore_raw to ([FG1],[FG2],[FG3],[FG4],[primary])--sp_help inoutstore_raw --5.重建索引(表被按分区字段将分配到各文件组) drop index PK_INOUTSTORE_RAW on inoutstore_raw go create clustered index PK_INOUTSTORE_RAW on inoutstore_raw(id) go --or do as follow alter index PK_INOUTSTORE_RAW on inoutstore_raw rebuild select * from sys.partitions where object_id = object_id('inoutstore_raw') --查看分区范围情况 select * from sys.partition_range_values --查看分区架构情况 select * from sys.partition_schemes --某一特定分区列值属于哪个分区 select $partition.partfunc_inoutstore_raw(32156) as partition_num --各分区所包含的记录数 select $partition.partfunc_inoutstore_raw(id) as partition_num, count(*) as recrod_num from inoutstore_raw group by $partition.partfunc_inoutstore_raw(id) order by $partition.partfunc_inoutstore_raw(id)select $partition.partfunc_inoutstore_raw(id) as partition_num, count(*) as recrod_num from inoutstore_raw group by $partition.partfunc_inoutstore_raw(id) order by $partition.partfunc_inoutstore_raw(id)select * from inoutstore_raw where $partition.partfunc_inoutstore_raw(id) =5 ------------------------------------------------------------------------------------------------------- --增加分区值,增加分区之前应先增加或设置新分区使用的文件组 alter partition scheme partfunc_inoutstore_raw_scheme next used [primary] go alter partition function partfunc_inoutstore_raw() split range(35000) go --合并分区 alter partition function partfunc_inoutstore_raw() merge range(10000)
全是高手讲解。。去吧
建立分区表 OrdersArchive (用来归档 Orders 表中不再活跃的数据)
查看分区表分区函数的分区范围
获取 dbo.sp_partition_range sp_partition_range代码 查看分区表每个分区的数据分布情况
运用滑动窗口机制,把分区表 Orders 分区数据迁移入 OrdersArchive
窗口滑动的步骤: 1. 在 OrdersArchive 分区表增加一个空闲分区。 2. 移动 Orders 一个分区到相应的 OrdersArchive 分区。 3. 删除 Orders 中的空闲分区。 移动订单日期为 1996 年的分区数据: 移动订单日期为 1997 年的分区数据: 移动订单日期为 1998 年的分区数据: 经过以上三次移动,我们已经把分区表 Orders 中的数据全部移动到 OrdersArchive 中了。同时我们注意到:在这个窗口滑动过程中,代码中只有三处是变化的(有规律): “split range('2000-01-01')”。 “merge range('1998-01-01')”。 “to dbo.OrdersArchive partition 4”。这就为程序化处理,提供了条件简单的就这意思,了其实自己也不懂,就拿来帖,呵呵
--基于已存在的表创建分区
/*You can use this process to partition a table or index that already exists by performing
the following steps:
1. Create a partition function.
2. Create a partition scheme.
3. Drop the existing clustered index.
4. Re-create the clustered index on the partition scheme.
*/
--以下示例基于inoutstore_raw表ID列创建分区
--1.创建分区函数
use ecustom_lz
go
create partition function
partfunc_inoutstore_raw(int) as
range left
for values(10000,20000,30000,40000)
--2.添加文件组
alter database ecustom_lz
add filegroup [FG1];
alter database ecustom_lz
add filegroup [FG2];
alter database ecustom_lz
add filegroup [FG3];
alter database ecustom_lz
add filegroup [FG4];
--3.添加数据库文件
alter database ecustom_lz
add file
(name = FG1_data,filename = 'D:\test\FG1_data.ndf',size = 3MB )
to filegroup [FG1];
alter database ecustom_lz
add file
(name = FG2_data,filename = 'D:\test\FG2_data.ndf',size = 3MB)
to filegroup [FG2];
alter database ecustom_lz
add file
(name = FG3_data,filename = 'D:\test\FG3_data.ndf',size = 3MB)
to filegroup [FG3];
alter database ecustom_lz
add file
(name = FG4_data,filename = 'D:\test\FG4_data.ndf',size = 3MB)
to filegroup [FG4];
go/*alter database ecustom_lz
remove file FG1_data
*/
--4.关联分区函数和架构
create partition scheme partfunc_inoutstore_raw_scheme
as partition partfunc_inoutstore_raw
to ([FG1],[FG2],[FG3],[FG4],[primary])--sp_help inoutstore_raw
--5.重建索引(表被按分区字段将分配到各文件组)
drop index PK_INOUTSTORE_RAW on inoutstore_raw
go
create clustered index PK_INOUTSTORE_RAW on inoutstore_raw(id)
go
--or do as follow
alter index PK_INOUTSTORE_RAW on inoutstore_raw rebuild
select * from sys.partitions where object_id = object_id('inoutstore_raw')
--查看分区范围情况
select * from sys.partition_range_values
--查看分区架构情况
select * from sys.partition_schemes
--某一特定分区列值属于哪个分区
select $partition.partfunc_inoutstore_raw(32156) as partition_num
--各分区所包含的记录数
select $partition.partfunc_inoutstore_raw(id) as partition_num, count(*) as recrod_num
from inoutstore_raw group by $partition.partfunc_inoutstore_raw(id) order by $partition.partfunc_inoutstore_raw(id)select $partition.partfunc_inoutstore_raw(id) as partition_num, count(*) as recrod_num
from inoutstore_raw group by $partition.partfunc_inoutstore_raw(id) order by $partition.partfunc_inoutstore_raw(id)select * from inoutstore_raw where $partition.partfunc_inoutstore_raw(id) =5
-------------------------------------------------------------------------------------------------------
--增加分区值,增加分区之前应先增加或设置新分区使用的文件组
alter partition scheme partfunc_inoutstore_raw_scheme
next used [primary]
go
alter partition function partfunc_inoutstore_raw()
split range(35000)
go
--合并分区
alter partition function partfunc_inoutstore_raw()
merge range(10000)