Sql Server分区表

1、创建分区函数

点击(此处)折叠或打开

1. CREATE PARTITION FUNCTION FnP(DATE)    --分区列类型
   
2. AS RANGE RIGHT    --LEFT OR RIGHT，区分边界值属于左边还是右边
   
3. FOR VALUES('20100101','20150101','20200101')

2、创建分区架构

点击(此处)折叠或打开

1. CREATE PARTITION SCHEME SchemeForP
   
2. AS PARTITION FnP
   
3. TO([PRIMARY],[PRIMARY],FileGroup1,FileGroup2)    --指定每个分区所属的文件组
4. --或ALL TO ([PRIMARY]) --全部指定到一个文件上

3、创建分区表

点击(此处)折叠或打开

1. CREATE TABLE Cwfx2(
   
2.     [Sequence] [int] NULL,
   
3.     [TradeGUID] [uniqueidentifier] NULL,
   
4.     [YsDate] DATE NULL,
   
5.     [YsItemName] [varchar](30) NULL,
   
6.     [YsAmount] [money] NULL
   
7. ) ON SchemeForP(YsDate)

4、查询各个分区

点击(此处)折叠或打开

1. SELECT S.name schemename, f.name functionname, P.partition_number, DS.name filegroupname, V.value, P.rows
   
2. FROM SYS.indexes I
   
3. INNER JOIN SYS.partition_schemes S ON S.data_space_id=I.data_space_id
   
4. INNER JOIN SYS.destination_data_spaces DDS ON DDS.partition_scheme_id=S.data_space_id
   
5. INNER JOIN SYS.data_spaces DS ON DS.data_space_id=DDS.data_space_id
   
6. INNER JOIN SYS.partitions P ON DDS.destination_id=P.partition_number AND P.object_id=I.object_id AND P.index_id=I.index_id
   
7. INNER JOIN SYS.partition_functions F ON S.function_id=F.function_id
   
8. LEFT JOIN SYS.partition_range_values V ON F.function_id=V.function_id AND V.boundary_id=P.partition_number-F.boundary_value_on_right
   
9. WHERE I.object_id=object_id('CWFX2')
   
10. 
    
11. --各个分区的条数
    
12. SELECT $PARTITION.FnP(ysdate) p_num, COUNT(1) rows FROM CWFX2 GROUP BY $PARTITION.FnP(ysdate)

5、分区表的分割
分割从老分区中复制到新分区，在从老分区中删除

点击(此处)折叠或打开

1. --新分区存放位置
2. ALTER PARTITION SCHEME SCHEMEFORP NEXT USED 'FileGroup2'
3. --分割点
   
4. ALTER PARTITION FUNCTION FnP() SPLIT RANGE('20250101')

6、分区的合并
合并向没有边界值的一边合并

点击(此处)折叠或打开

1. ALTER PARTITION FUNCTION FnP() MERGE RANGE('20250101')

7、把一个表改成分区表脚本
图形化的右键要分区的表-->存储-->创建分区。

点击(此处)折叠或打开

1. BEGIN TRANSACTION
   
2. CREATE PARTITION FUNCTION [FnCwfx](datetime) AS RANGE LEFT FOR VALUES (N'2010-01-01T00:00:00', N'2015-01-01T00:00:00', N'2018-01-01T00:00:00')
   
3. 
   
4. 
   
5. CREATE PARTITION SCHEME [ScCwfx] AS PARTITION [FnCwfx] TO ([PRIMARY], [FileGroup1], [FileGroup2], [PRIMARY])
   
6. 
   
7. 
   
8. CREATE CLUSTERED INDEX [ClusteredIndex_on_ScCwfx_636070443000552980] ON [dbo].[Cwfx]
   
9. (
   
10.     [YsDate]
    
11. )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [ScCwfx]([YsDate])
    
12. 
    
13. 
    
14. DROP INDEX [ClusteredIndex_on_ScCwfx_636070443000552980] ON [dbo].[Cwfx]
    
15. 
    
16. COMMIT TRANSACTION

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26066458/viewspace-2123512/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26066458/viewspace-2123512/