partitioned table

可伸縮性性是數(shù)據(jù)庫管理系統(tǒng)的一個很重要的方面，在sql server 2005中可伸縮性方面提供了表分區(qū)功能。

其實對于有關(guān)系弄數(shù)據(jù)庫產(chǎn)品來說，對表、數(shù)據(jù)庫和服務(wù)器進行數(shù)據(jù)分區(qū)的從而提供大數(shù)據(jù)量的支持并不是什么新鮮事，但 sql server 2005 提供了一個新的體系結(jié)構(gòu)功能，用于對數(shù)據(jù)庫中的文件組進行表分區(qū)。水平分區(qū)可根據(jù)分區(qū)架構(gòu)，將一個表劃分為幾個較小的分組。表分區(qū)功能是針對超大型數(shù)據(jù)庫（從數(shù)百吉字節(jié)到數(shù)千吉字節(jié)或更大）而設(shè)計的。超大型數(shù)據(jù)庫 (vldb) 查詢性能通過分區(qū)得到了改善。通過對廣大分區(qū)列值進行分區(qū)，可以對數(shù)據(jù)的子集進行管理，并將其快速、高效地重新分配給其他表。

設(shè)想一個大致的電子交易網(wǎng)站，有一個表存儲了此網(wǎng)站的歷史交易數(shù)據(jù)，這此數(shù)據(jù)量可能有上億條，在以前的sql server版本中存儲在一個表中不管對于查詢性能還是維護都是件麻煩事，下面我們來看一下在sql server2005怎么提高性能和可管理性：

-- 創(chuàng)建要使用的測試數(shù)據(jù)庫，demo

use [master]

if  exists (select name from master.dbo.sysdatabases where name = n'demo')

drop database [demo]

create database [demo]

--由于表分區(qū)使用使用新的體系結(jié)構(gòu)，使用文件組來進行表分區(qū)，所以我們創(chuàng)建將要用到的6個文件組，來存儲6個時間段的交易數(shù)據(jù)[<2000],[ 2001], [2002], [2003], [2004], [>2005]

alter database demo add filegroup yearfg1;

alter database demo add filegroup yearfg2;

alter database demo add filegroup yearfg3;

alter database demo add filegroup yearfg4;

alter database demo add filegroup yearfg5;

alter database demo add filegroup yearfg6;

-- 下面為這些文件組添加文件來進行物理的數(shù)據(jù)存儲

alter database demo add file (name = 'yearf1', filename = 'c:/advworksf1.ndf') to filegroup yearfg1;

alter database demo add file (name = 'yearf2', filename = 'c:/advworksf2.ndf') to filegroup yearfg2;

alter database demo add file (name = 'yearf3', filename = 'c:/advworksf3.ndf') to filegroup yearfg3;

alter database demo add file (name = 'yearf4', filename = 'c:/advworksf4.ndf') to filegroup yearfg4;

alter database demo add file (name = 'yearf5', filename = 'c:/advworksf5.ndf') to filegroup yearfg5;

alter database demo add file (name = 'yearf6', filename = 'c:/advworksf6.ndf') to filegroup yearfg6;

-- here we associate the partition function to

-- the created filegroup via a partitioning scheme

use demo;

go

-------------------------------------------------------

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

-------------------------------------------------------

create partition function yearpf(datetime)

as

range left for values ('01/01/2000'

                                          ,'01/01/2001'

                                          ,'01/01/2002'

                                          ,'01/01/2003'

                                          ,'01/01/2004')

-------------------------------------------------------

-- 創(chuàng)建分區(qū)架構(gòu)

-------------------------------------------------------

create partition scheme yearps

as partition yearpf to (yearfg1, yearfg2,yearfg3,yearfg4,yearfg5,yearfg6)

-- 創(chuàng)建使用此schema的表

create table partitionedorders

(

id int not null identity(1,1),

duedate datetime not null,

) on yearps(duedate)

--為此表填充數(shù)據(jù)

declare @dt datetime

select @dt = '1999-01-01'

--start looping, stop at ending date

while (@dt <= '2005-12-21')

begin

       insert into partitionedorders values(@dt)

       set @dt=dateadd(yy,1,@dt)

end

-- 現(xiàn)在我們可以看一下我們剛才插入的行都分布在哪個partition

select *, $partition.yearpf(duedate) from partitionedorders

--我們可以看一下我們現(xiàn)在partitionedorders表的數(shù)據(jù)存儲在哪此partition中，以及在這些分區(qū)中數(shù)據(jù)量的分布

select * from sys.partitions where object_id = object_id('partitionedorders')

--

--現(xiàn)在我們設(shè)想一下，如果我們隨著時間的流逝，現(xiàn)在已經(jīng)到了2005年，按照我們先前的設(shè)定，我們想再想入一個分區(qū)，這時是不是重新創(chuàng)建表分區(qū)架構(gòu)然后重新把數(shù)據(jù)導放到新的分區(qū)架構(gòu)呢，答案是完全不用。下面我們就看如果新加一個分區(qū)。

--更改分區(qū)架構(gòu)定義語言，讓下一個分區(qū)使用和現(xiàn)在已經(jīng)存在的分區(qū)yearfg6分區(qū)中，這樣此分區(qū)就存儲了兩段partition的數(shù)據(jù)。

alter partition scheme yearps

next used yearfg6;

--更改分區(qū)函數(shù)

alter partition function yearpf()

split range ('01/01/2005') 

--現(xiàn)在我們可以看一下我們剛才插入的行都分布在哪個partition？

select *, $partition.yearpf(duedate) from partitionedorders

--我們可以看一下我們現(xiàn)在partitionedorders表的數(shù)據(jù)存儲在哪此partition中，以及在這些分區(qū)中數(shù)據(jù)量的分布

select * from sys.partitions where object_id = object_id('partitionedorders')