sqlserver 分区表

--1 建分区函数，用于自动划分物理表数据的流向（建好后可以在Databases/[dbName]/Storage中看到）
/* 下面分成四个区域<BigScreen, >=BigScreen且<Computer, >=Computer且<PoolTable, >=PoolTable
若是RIGHT，则X1 < BigScreen <= X2 < Computer <= X3 < PoolTable <= X4
若是LEFT， 则X1 <= BigScreen < X2 <= Computer < X3 <= PoolTable < X4
*/

CREATE  PARTITION  FUNCTION   [ pf_Product_Partition ] ( VARCHAR ( 10 ))  AS  RANGE 
RIGHT
FOR   VALUES  (N ' BigScreen ' , N ' Computer ' , N ' PoolTable ' )

 

--2 建分区方案，用于与上面的function关联（建好后可以在Storage中看到）
--概念上的schema可以与物理文件组关联，这样可以获得更加效率，具体可以参考http://www.agilelabs.cn/blogs/woody/archive/2006/08/24/1574.aspx

CREATE  PARTITION SCHEME  [ ps_Product_Scheme ]   AS  PARTITION
[ pf_Product_Partition ]
ALL   TO  ( [ PRIMARY ] )

 

--3 建表，把需要分区的字段关联到分区方案schema上

CREATE   TABLE   [ SalesHistoryArchive ]  
(           
        [ SaleID ]   [ int ]   IDENTITY ( 1 , 1 ),           
        [ Product ]   [ varchar ] ( 10 )  NULL ,                 
        [ SaleDate ]   [ datetime ]   NULL ,                 
        [ SalePrice ]   [ money ]   NULL
) ON   [ ps_Product_Scheme ] (Product) 
GO  

 

--创建分区索引
--CREATE INDEX PARTITION_INDEX ON SalesHistoryArchive(Product) ON [ps_Product_Scheme](Product)

--4 建立演示数据

DECLARE   @i   SMALLINT
SET   @i   =   1
WHILE  ( @i   <= 10000 )
BEGIN                        
     INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)                       
     VALUES ( ' Computer ' ,  DATEADD (mm,  @i ,  ' 3/11/1919 ' ),  DATEPART (ms,  GETDATE ())  +  ( @i   +   57 ))     
     INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)           
     VALUES ( ' BigScreen ' ,  DATEADD (mm,  @i ,  ' 3/11/1927 ' ),  DATEPART (ms,  GETDATE ())  +  ( @i   +   13 ))                 
     INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)               
     VALUES ( ' PoolTable ' ,  DATEADD (mm,  @i ,  ' 3/11/1908 ' ),  DATEPART (ms,  GETDATE ())  +  ( @i   +   29 ))                        
     SET   @i   =   @i   +   1
END

-- 其它测试数据
INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)                       
VALUES ( ' Aomputer ' ,  DATEADD (mm,  1 ,  ' 3/11/1919 ' ),  DATEPART (ms,  GETDATE ())  +  ( 1   +   57 )) 

-- 其它测试数据
INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)                       
VALUES ( ' Bia ' ,  DATEADD (mm,  1 ,  ' 3/11/1919 ' ),  DATEPART (ms,  GETDATE ())  +  ( 2   +   57 )) 

-- 其它测试数据
INSERT   INTO  SalesHistoryArchive(Product, SaleDate, SalePrice)                       
VALUES ( ' Zomputer ' ,  DATEADD (mm,  2 ,  ' 3/11/1919 ' ),  DATEPART (ms,  GETDATE ())  +  ( 3   +   57 )) 

 

--5 可以看到每条数据在第几个分区中（根据上述情况，共有四个分区）

SELECT  $partition. [ pf_Product_Partition ] (Product),  *
FROM  SalesHistoryArchive

 

--6 看看每个分区的记录数

SELECT   *   From  sys.partitions
WHERE   OBJECT_NAME ( object_id )  =   ' SalesHistoryArchive '

 

--7 转移 把表SalesHistoryArchive分区2中的数据转移到bak中，也可以转移到指定的分区2中
--好处，速度太快了，你可以对这组数据进行查询或删除

SELECT   *   INTO  SalesHistoryArchiveBak  FROM  SalesHistoryArchive  WHERE   1 = 2
ALTER   TABLE  SalesHistoryArchive SWITCH PARTITION  2   TO  SalesHistoryArchiveBak 
-- ALTER TABLE SalesHistoryArchive SWITCH PARTITION 4 TO SalesHistoryArchiveBak PARTITION 4

 

--8 性能对比，请参考
--http://www.cnblogs.com/13590/archive/2007/07/09/810770.html

--9 删除

DROP   TABLE  SalesHistoryArchive
GO
DROP   TABLE  SalesHistoryArchiveBak
GO
DROP  PARTITION SCHEME ps_Product_Scheme
GO
DROP  PARTITION  FUNCTION  pf_Product_Partition
GO