数据表分区

set statistics time on
--增加文件组
alter database MyTest add filegroup ByIdGroup1
alter database MyTest add filegroup ByIdGroup2
alter database MyTest add filegroup ByIdGroup3
alter database MyTest add filegroup ByIdGroup4
alter database MyTest add filegroup ByIdGroup5
alter database MyTest add filegroup ByIdGroup6
alter database MyTest add filegroup ByIdGroup7
alter database MyTest add filegroup ByIdGroup8
alter database MyTest add filegroup ByIdGroup9
alter database MyTest add filegroup ByIdGroup10

---给文件组增加文件
alter database MyTest add file
(name=N'ById1',filename=N'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\ById1.ndf',size=5Mb,filegrowth=5mb) to filegroup ByIdGroup1

-----创建分区函数和分区方案
Use [MyTest]
Go
begin transaction
create partition function bgPartitionFun(datetime) as range left for values('2012-01-01','2013-01-01','2014-01-01','2015-01-01')
create partition scheme bgPartitionSchema as partition bgPartitionFun to([PRIMARY],ByIdGroup1,ByIdGroup3,ByIdGroup4,ByIdGroup5)
COMMIT TRANSACTION

---数据在那个分区
select $partition.bgPartitionFun('2014-11-21')
--分区中的数据
select * from userInfo where $partition.bgPartitionFun(DateSet)=1
--各个分区中的数据数
select COUNT(*) a,$partition.bgPartitionFun(DateSet) b from userInfo
group by $partition.bgPartitionFun(DateSet)
---查看各分区和分区函数
 select * from sys.filegroups
 select * from sys.partition_schemes  
 select * from sys.partition_functions
 select * from sys.partition_range_values
  select * from sys.data_spaces ---包含文件组和分区方案
--分区操作
--一个分区变两个
alter partition function bgPartitionFun() split range('2014-01-01')
--合并分区
alter partition function bgPartitionFun() merge range('2014-01-01')
--把分区表中第一分区的数据复制到普通表
alter table userInfo switch partition 1 to tableName
--把普通表中的数据复制到分区表第一分区
alter table tableName switch to userInfo partition 1

--删除分区函数bgPartitionFun
drop partition function bgPartitionFun
--创建分区索引
create clustered index indexName on tableName(columnName)
on partitionSchemeName(partitionColumnName)
---查看分区表的相关信息
SELECT
    SchemaName = S.name,
    TableName = TB.name,
    PartitionScheme = PS.name,
    PartitionFunction = PF.name,
    PartitionFunctionRangeType = CASE
            WHEN boundary_value_on_right = 0 THEN 'LEFT'
            ELSE 'RIGHT' END,
    PartitionFunctionFanout = PF.fanout,
    SchemaID = S.schema_id,
    ObjectID = TB.object_id,
    PartitionSchemeID = PS.data_space_id,
    PartitionFunctionID = PS.function_id
FROM sys.schemas S
    INNER JOIN sys.tables TB
        ON S.schema_id = TB.schema_id
    INNER JOIN sys.indexes IDX
        on TB.object_id = IDX.object_id
            AND IDX.index_id < 2
    INNER JOIN sys.partition_schemes PS
        ON PS.data_space_id = IDX.data_space_id
    INNER JOIN sys.partition_functions PF
        ON PS.function_id = PF.function_id