什么是表分區(qū)？
表分區(qū)其實(shí)就是將一個(gè)大表分成若干個(gè)小表。
表分區(qū)可以從物理上將一個(gè)大表分成幾個(gè)小表，但是邏輯上還是一個(gè)表。所以當(dāng)執(zhí)行插入、更新等操作的時(shí)候，不需要我們?nèi)ヅ袛鄳?yīng)該插入或更新到哪個(gè)表中。只需要插入大表中就可以了。SQL Server會(huì)自動(dòng)的將它放在對(duì)應(yīng)的表中。對(duì)于查詢(xún)也是一樣，直接查詢(xún)大表就可以了。 

如何創(chuàng)建分區(qū)表

一、創(chuàng)建文件組

其實(shí)可以使用默認(rèn)的primary組，但是為了更方便管理以及提高運(yùn)行速度，所以還是應(yīng)該創(chuàng)建幾個(gè)分組。

1、使用SSMS創(chuàng)建文件組

2、使用T-SQL創(chuàng)建文件組

--alter database <數(shù)據(jù)庫(kù)名> add filegroup <文件組名>
alter database webDB add filegroup group2013
alter database webDB add filegroup group2014
alter database webDB add filegroup group2015

二、為文件組添加數(shù)據(jù)庫(kù)文件

1、使用SSMS添加數(shù)據(jù)庫(kù)文件

2、使用T-SQL添加數(shù)據(jù)庫(kù)文件

--alter database <數(shù)據(jù)庫(kù)名稱(chēng)> add file <數(shù)據(jù)標(biāo)識(shí)> to filegroup <文件組名稱(chēng)>
alter database webDB add file
(
    name='web2013',
    filename='D:\web2013.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2013
alter database webDB add file
(
    name='web2014',
    filename='D:\web2014.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2014


alter database webDB add file
(
    name='web2015',
    filename='D:\web2015.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2015

注意：盡可能的將不同的文件放在不同的硬盤(pán)分區(qū)里，或者獨(dú)立硬盤(pán)中。這樣可以加快SQL Server運(yùn)行速度。

三、創(chuàng)建分區(qū)函數(shù)

分區(qū)函數(shù)用來(lái)告訴SQL Server用什么樣的規(guī)則進(jìn)行分區(qū)，這一步必須使用T-SQL腳本來(lái)執(zhí)行了。

create partition function fenqu(datetime) --分區(qū)函數(shù)名
as range right  --right分區(qū)方式 邊界值去左表還是右表
for values ('2014-01-01','2015-01-01') --按這些值來(lái)分區(qū) 
--group2013 : 2014-01-01 之前的
--group2014 : 2014-01-01 到 2014-12-31的
--group2015 : 2015-01-01 之后的

四、創(chuàng)建分區(qū)方案

create partition scheme SchemeFenqu --分區(qū)方案名
as partition fenqu    --之前創(chuàng)建的分區(qū)函數(shù)
to(group2013,group2014,group2015) --跟放的文件組

創(chuàng)建完分區(qū)函數(shù)和分區(qū)方案后可以在存儲(chǔ)中查看

五、創(chuàng)建分區(qū)表

create table fenquTable
(
    id int identity(1,1) not null,
    name varchar(20) not null,
    createTime datetime not null
) on SchemeFenqu(createTime) --調(diào)用分區(qū)方案

注意：不可以使用聚集索引，因?yàn)榫奂饕谴嬖谶B續(xù)的物理地址中的，而表分區(qū)是將數(shù)據(jù)分別存儲(chǔ)在不同表中的。

至此物理上分離的，邏輯上一體的分區(qū)表就創(chuàng)建完了。

操作分區(qū)表

一、插入數(shù)據(jù)

--插入測(cè)試數(shù)據(jù)
insert into fenquTable(name,createTime) values ('隔壁老王','2010-01-01')
insert into fenquTable(name,createTime) values ('隔壁老張','2011-01-01')
insert into fenquTable(name,createTime) values ('隔壁老趙','2012-01-01')
insert into fenquTable(name,createTime) values ('隔壁老李','2013-01-01')
insert into fenquTable(name,createTime) values ('老李兒子','2013-10-01')
insert into fenquTable(name,createTime) values ('隔壁老田','2014-01-01')
insert into fenquTable(name,createTime) values ('隔壁老梁','2015-01-01')
insert into fenquTable(name,createTime) values ('老梁姑娘楠楠','2015-10-10')

跟插入普通表沒(méi)有任何區(qū)別，不用管他放在哪個(gè)物理磁盤(pán)上。

二、查詢(xún)數(shù)據(jù)

--查詢(xún)數(shù)據(jù)
select * from fenquTable

查詢(xún)也是如此，不用考慮哪個(gè)磁盤(pán)，邏輯上都屬于同一個(gè)表，基本上看不出區(qū)別。如果需要查看哪條插入到哪個(gè)物理的分區(qū)表中，可以使用$partition函數(shù)查看。

--語(yǔ)法：$partition.分區(qū)函數(shù)名(表達(dá)式)
--查看該表達(dá)式下有多少數(shù)據(jù)
select $partition.fenqu('2015-01-01') --返回3


--查看分區(qū)表明細(xì)
select * from fenquTable where $partition.fenqu(createTime)=1
select * from fenquTable where $partition.fenqu(createTime)=2
select * from fenquTable where $partition.fenqu(createTime)=3


--查看分區(qū)表中的記錄數(shù)
select $partition.fenqu(createTime) as 分區(qū),count(id) from fenquTable 
group by $partition.fenqu(createTime)

三、修改數(shù)據(jù)

select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量 from fenquTable
group by $partition.fenqu(createTime)
--分區(qū)    數(shù)量
--1            5
--2            1
--3            2


update fenquTable set createTime ='2015-01-01' where id = 1


select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量 from fenquTable
group by $partition.fenqu(createTime)
--分區(qū)    數(shù)量
--1            4
--2            1
--3            3

可以明顯看到，跟普通修改沒(méi)有區(qū)別，SQL Server可以自動(dòng)幫我們重新劃分分區(qū)，將數(shù)據(jù)從第一個(gè)分區(qū)移動(dòng)到第五個(gè)分區(qū)中。

普通表轉(zhuǎn)分區(qū)表

上面介紹了如何在創(chuàng)建表的時(shí)候進(jìn)行分區(qū)，但往往我們需要的是將現(xiàn)有的普通表在數(shù)據(jù)保留的情況下進(jìn)行分區(qū)。

普通表一般都有主鍵，同時(shí)還是聚集索引。分區(qū)是以某個(gè)字段為條件進(jìn)行的，而除了這個(gè)字段其他字段是不可以創(chuàng)建聚集索引的。所以需要先刪除表中的聚集索引，再新建一個(gè)聚集索引。

--刪除主鍵，自動(dòng)同時(shí)刪除索引
alter table newTable drop constraint PK_newTable 


--創(chuàng)建主鍵，但不創(chuàng)建聚集索引
alter table newTable add constraint PK_newTable
primary key nonclustered --非聚集
(
    id asc
) on [primary]


--然后給我們親愛(ài)的時(shí)間創(chuàng)建一個(gè)聚集索引
create clustered index CT_newTable on newTable(createTime)
on schemeFenqu(createTime) --并調(diào)用分區(qū)方案


--然后再查詢(xún)分區(qū)，發(fā)現(xiàn)數(shù)據(jù)保留情況下，已經(jīng)將數(shù)據(jù)按規(guī)則進(jìn)行分區(qū)了
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)

添加分區(qū)

向上面只分了3個(gè)區(qū)，而15年以后的都存在第三個(gè)分區(qū)中，到16年還是會(huì)存在這個(gè)分區(qū)中，這時(shí)候需要再新加一個(gè)16年的分區(qū)。

添加新的分區(qū)意味著要新建一個(gè)文件組和文件來(lái)存放這個(gè)分區(qū)表，然后在分區(qū)方案中用到這個(gè)文件組，最后再修改一下分區(qū)函數(shù)的規(guī)則即可。文件組和分區(qū)數(shù)量要保持一致。

--創(chuàng)建文件組
alter database webDB add filegroup group2016


--添加數(shù)據(jù)庫(kù)文件
alter database webDB add file
(
    name='web2016',
    filename='D:\web2016.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2016


--修改分區(qū)方案
alter partition scheme SchemeFenqu
next used group2016


--修改分區(qū)函數(shù)
alter partition function fenqu()
split range('2016-01-01')


--添加2016年數(shù)據(jù)
insert into newTable (name,createTime) values ('16年小明','2016-03-05')


--查看分區(qū)及統(tǒng)計(jì)
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)

刪除分區(qū)

刪除分區(qū)就是將分區(qū)函數(shù)中多余的邊界值刪除。

如：2013，2014，2015，2016 現(xiàn)在需要將13年和14年進(jìn)行合并，刪除13年的分區(qū)。

--查看分區(qū)及統(tǒng)計(jì)
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)
--分區(qū)    數(shù)量
--1        4
--2        1
--3        3
--4        1


--刪掉該邊界值
alter partition function fenqu() merge range('2014-01-01')


--再次查詢(xún)
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)
--分區(qū)    數(shù)量
--1        5
--2        3
--3        1

拆分分區(qū)

有的時(shí)候某一分區(qū)中數(shù)據(jù)量過(guò)大，需要將這個(gè)分區(qū)再次拆分為多個(gè)分區(qū)，以加快訪(fǎng)問(wèn)速度。

拆分分區(qū)的操作其實(shí)與添加分區(qū)類(lèi)似，首先要添加文件組、文件、修改分區(qū)方案、修改分區(qū)函數(shù)(新增一個(gè)邊界值)。

如：2014，2015，2015年6月份以上一個(gè) 6月份一下一個(gè)。

--查看分區(qū)及統(tǒng)計(jì)
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)
--分區(qū)        數(shù)量
--1            5
--2            3
--3            1


--創(chuàng)建文件組
alter database webDB add filegroup group2014_2015


--添加數(shù)據(jù)庫(kù)文件
alter database webDB add file
(
    name='web2014_2015',
    filename='D:\web2014_2015.ndf',
    size=5mb,
    filegrowth=5mb
)
to filegroup group2014_2015


--修改分區(qū)方案
alter partition scheme SchemeFenqu
next used group2014_2015


--修改分區(qū)函數(shù)
alter partition function fenqu()
split range('2015-06-01')


--查看分區(qū)及統(tǒng)計(jì)
select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)
--分區(qū)        數(shù)量
--1            5
--2            2
--3            1
--4            1

分區(qū)表轉(zhuǎn)普通表

--修改分區(qū)函數(shù) 將邊界值都刪除
alter partition function fenqu()
merge range('2013-01-01')
alter partition function fenqu()
merge range('2014-01-01')
alter partition function fenqu()
merge range('2015-01-01')
alter partition function fenqu()
merge range('2015-06-01')


select $partition.fenqu(createTime) as 分區(qū),count(id) as 數(shù)量
from newTable group by $partition.fenqu(createTime)
--這時(shí)只有一個(gè)分區(qū)了
--分區(qū)    數(shù)量
--1        9

這樣雖然只有一個(gè)分區(qū)了，但是查看數(shù)據(jù)表存儲(chǔ)位置，是否進(jìn)行分區(qū)：True，分區(qū)數(shù)1.

--重新建立聚集索引
create clustered index CT_newTable on newTable(createTime)
    with(drop_existing=on) --如果存在則刪除
on [primary]

刪除分區(qū)索引后，重新建立聚集索引，這時(shí)再此查看數(shù)據(jù)表的存儲(chǔ)位置，是否分區(qū)：Flase。