sql server 使用分区切换高效传输数据

Use testdb

go

 

if  exists (select * from sys.objects where object_id = object_id('dbo.p_logic_testtable_job') and type in ('p', 'pc'))

    drop proc dbo.p_logic_testtable_job

go

 

/**************************************************

功    能：清除testtable历史数据

创建日期：2011-05-27

创建人员：tongye

调用示例：exec p_logic_testtable_job

修订记录：

**************************************************/

create proc p_logic_testtable_job

    @report_date datetime = null

as

begin

 

    if @report_date is null or @report_date=’’

       set @report_date = convert(varchar(40),getdate()-30,102)

      

    declare

    @partition_min int,

    @partition_max int

   

    -- 找出符合要求分区的最小分区

    select top 1 @partition_min = partition_number

    from sys.partitions

    where object_id=object_id('dbo.testtable') and rows>0

    and $partition.pf_testtable_date(@report_date) > partition_number

    order by partition_number asc

 

    -- 找出符合要求分区的最大分区

    select top 1 @partition_max = partition_number

    from sys.partitions

    where object_id=object_id('dbo.testtable') and rows>0

    and $partition.pf_testtable_date(@report_date) > partition_number

    order by partition_number desc

   

    -- 转移分区数据

    if @partition_min is not null and @partition_max is not null

    begin

       if @partition_min=@partition_max

       begin

          alter table dbo.testtable switch partition @partition_min to dbo.testtable_switch

           truncate table dbo.testtable_switch

       end

       else

       begin

           while @partition_min <= @partition_max

           begin

              alter table dbo.testtable switch partition @partition_min to dbo.testtable_switch  

              truncate table dbo.testtable_switch

              set @partition_min+=1

           end

       end

    end

end

go

 

 

 

切换分区的常规要求  注：摘自 sql server 联机丛书

 

在转移分区时，数据在物理上并未移动；只不过是有关数据位置的元数据有了改动。必须满足下面几项一般性的要求才能切换分区：

·         在 SWITCH 操作之前两个表必须都存在。在执行切换操作之前，从中移出该分区的表（源表）以及接收该分区的表（目标表）都必须存在于数据库中。

·         接收分区必须存在并且必须是空的。无论是将表作为分区添加到现有的已分区表，还是将分区从一个已分区表移动到另一个已分区表，接收新分区的分区都必须存在并且必须为空分区。

·         不分区的接收表必须存在且必须是空的。如果要重新分配一个分区以形成一个不分区的表，则接收新分区的表必须存在并且必须为空的不分区表。

·         各分区必须依据同一列。如果要将分区从一个已分区表切换到另一个已分区表，则这两个表必须依据同一列进行分区。

·         源表和目标表必须共享同一个文件组。ALTER TABLE...SWITCH 语句的源表和目标表必须位于同一文件组，并且其大值列也必须存储在同一文件组中。所有对应的索引、索引分区或索引视图分区也必须位于同一文件组中。不过，该文件组可以与对应表或其他对应索引的文件组不同。