SQL Server表分区

----------------------------------------创建分区表Start

--分区函数
CREATE PARTITION FUNCTION partfunBfq (datetime)   
AS RANGE RIGHT FOR VALUES ('20110101','20120101','20130101','20140101','20150101') 


--分区方案
CREATE PARTITION SCHEME partschBfq 
AS PARTITION partfunBfq   
TO (   
    BFQ2011,   
    BFQ2012,   
    BFQ2013,   
    BFQ2014,   
    BFQ2015,
BFQ2016
)  




--创建表
CREATE TABLE BFQ_TEST(   
[Id] [int] IDENTITY(1,1) NOT NULL,   
[Name] [varchar](16) NOT NULL,   
[BfqTime][datetime] NOT NULL   
) ON partschBfq([BfqTime]) 


CREATE TABLE BFQ_TEST_A(   
[Id] [int] IDENTITY(1,1) NOT NULL,   
[Name] [varchar](16) NOT NULL,   
[BfqTime][datetime] NOT NULL   
) ON partschBfq([BfqTime]) 


--向表中插入数据
insert into BFQ_TEST_A values('测试一','2010-01-01 00:00:00');
insert into BFQ_TEST_A values('测试二','2011-01-01 00:00:00');
insert into BFQ_TEST_A values('测试三','2012-01-01 00:00:00');
insert into BFQ_TEST_A values('测试四','2013-01-01 00:00:00');
insert into BFQ_TEST_A values('测试五','2014-01-01 00:00:00');
insert into BFQ_TEST_A values('测试六','2015-01-01 00:00:00');
insert into BFQ_TEST_A values('测试七','2016-01-01 00:00:00');


--查询所有分区表数据
select * from BFQ_TEST;


--查询数据在那个分区
select $PARTITION.partfunBfq ('2010-10-1');
select $PARTITION.partfunBfq ('2011-10-1');
select $PARTITION.partfunBfq ('2012-10-1');
select $PARTITION.partfunBfq ('2013-10-1');
select $PARTITION.partfunBfq ('2014-10-1');
select $PARTITION.partfunBfq ('2015-10-1');
select $PARTITION.partfunBfq ('2016-10-1');


--查询各个分区内的内容
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=1;
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=2;
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=3;
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=4;
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=5;
select * from BFQ_TEST where $PARTITION.partfunBfq(BfqTime)=6;


--统计每个物理分区表中的记录数
select $PARTITION.partfunBfq(BfqTime) as 分区编号, * from BFQ_TEST;
select $PARTITION.partfunBfq(BfqTime) as 分区编号,count(Id) as 记录数 from BFQ_TEST group by $PARTITION.partfunBfq(BfqTime);


--统计所有分区中的记录总数
select $PARTITION.partfunBfq(BfqTime) as 分区编号,count(Id) as 记录数 from BFQ_TEST group by $PARTITION.partfunBfq(BfqTime);


update BFQ_TEST set BfqTime = '2010-01-01 00:00:00' where name = '测试七';


----------------------------------------创建分区表END


----------------------------------------将普通表转换成分区表Start


--删除表
drop table BFQ_TEST_A;


--新建一个普通的数据表  
CREATE TABLE BFQ_TEST_A(  
    [Id] [int] IDENTITY(1,1) NOT NULL,          --自动增长  
    [Name] [varchar](16) NOT NULL,  
    [Time] [datetime] NOT NULL,  
    CONSTRAINT [PK_BFQ_TEST_A] PRIMARY KEY CLUSTERED  --创建主键  
    (  
        [Id] ASC  
    )  
)  


--向表中插入数据
insert into BFQ_TEST_A values('测试1',  '2010-01-01 00:00:00');
insert into BFQ_TEST_A values('测试2',  '2011-01-01 00:00:00');
insert into BFQ_TEST_A values('测试3',  '2011-01-01 00:00:00');
insert into BFQ_TEST_A values('测试4',  '2012-01-01 00:00:00');
insert into BFQ_TEST_A values('测试5',  '2013-01-01 00:00:00');
insert into BFQ_TEST_A values('测试6',  '2013-01-01 00:00:00');
insert into BFQ_TEST_A values('测试7',  '2014-01-01 00:00:00');
insert into BFQ_TEST_A values('测试8',  '2014-01-01 00:00:00');
insert into BFQ_TEST_A values('测试9',  '2015-01-01 00:00:00');
insert into BFQ_TEST_A values('测试10', '2015-01-01 00:00:00');
insert into BFQ_TEST_A values('测试11', '2016-01-01 00:00:00');
insert into BFQ_TEST_A values('测试12', '2016-01-01 00:00:00');
insert into BFQ_TEST_A values('测试13', '2017-01-01 00:00:00');


--删掉主键  
ALTER TABLE BFQ_TEST_A DROP constraint PK_BFQ_TEST_A


--创建主键，但不设为聚集索引  
ALTER TABLE BFQ_TEST_A ADD CONSTRAINT PK_BFQ_TEST_A PRIMARY KEY NONCLUSTERED  
(  
    [ID] ASC  
) ON [PRIMARY] 


--创建一个新的聚集索引，在该聚集索引中使用分区方案  
CREATE CLUSTERED INDEX CT_BFQ_TEST_A ON BFQ_TEST_A([Time])  
ON partschBFQ([Time])


--统计每个物理分区表中的记录数
select $PARTITION.partfunBfq(Time) as 分区编号, * from BFQ_TEST_A;


----------------------------------------将普通表转换成分区表END