SQL Server 表分區(partitioned table/Data Partitioning)

server

Partitioned Table

延展性性是資料庫管理系統的一個很重要的方面，在SQL Server 2005中延展性方面提供了表資料分割函數。

其實對於有關係弄資料庫產品來說，對錶、資料庫和伺服器進行資料分區的從而提供大資料量的支援並不是什麼新鮮事，但 SQL Server 2005 提供了一個新的體繫結構功能，用於對資料庫中的檔案組進行表分區。水平資料分割可根據分區架構，將一個表劃分為幾個較小的分組。表資料分割函數是針對超大型資料庫（從數百吉位元組到數千吉位元組或更大）而設計的。超大型資料庫 (VLDB) 查詢效能通過分區得到了改善。通過對廣大分區列值進行分區，可以對資料的子集進行管理，並將其快速、高效地重新分配給其他表。

設想一個大致的電子交易網站，有一個表格儲存體了此網站的曆史交易資料，這此資料量可能有上億條，在以前的SQL Server版本中儲存在一個表中不管對於查詢效能還是維護都是件麻煩事，下面我們來看一下在SQL Server2005怎麼提高效能和可管理性：

-- 建立要使用的測試資料庫，Demo

USE [master]

IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'DEMO')

DROP DATABASE [DEMO]

CREATE DATABASE [DEMO]

--由於表分區使用使用新的體繫結構，使用檔案組來進行表分區，所以我們建立將要用到的6個檔案組，來儲存6個時間段的交易資料[<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;

-- 下面為這些檔案組添加檔案來進行物理的資料存放區

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

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

-- 建立分區函數

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

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')

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

-- 建立分區架構

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

CREATE PARTITION SCHEME YEARPS

AS PARTITION YEARPF TO (YEARFG1, YEARFG2,YEARFG3,YEARFG4,YEARFG5,YEARFG6)

-- 建立使用此Schema的表

CREATE TABLE PARTITIONEDORDERS

(

ID INT NOT NULL IDENTITY(1,1),

DUEDATE DATETIME NOT NULL,

) ON YEARPS(DUEDATE)

--為此表填充資料

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

-- 現在我們可以看一下我們剛才插入的行都分布在哪個Partition

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

--我們可以看一下我們現在PARTITIONEDORDERS表的資料存放區在哪此partition中，以及在這些分區中資料量的分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')

--

--現在我們設想一下，如果我們隨著時間的流逝，現在已經到了2005年，按照我們先前的設定，我們想再想入一個分區，這時是不是重新建立表分區架構然後重新把資料導放到新的分區架構呢，答案是完全不用。下面我們就看如果新加一個分區。

--更改分區架構定義語言，讓下一個分區使用和現在已經存在的分區YEARFG6分區中，這樣此分區就儲存了兩段partition的資料。

ALTER PARTITION SCHEME YEARPS

NEXT USED YEARFG6;

--更改分區函數

ALTER PARTITION FUNCTION YEARPF()

SPLIT RANGE ('01/01/2005') 

--現在我們可以看一下我們剛才插入的行都分布在哪個Partition？

SELECT *, $PARTITION.YEARPF(DUEDATE) FROM PARTITIONEDORDERS

--我們可以看一下我們現在PARTITIONEDORDERS表的資料存放區在哪此partition中，以及在這些分區中資料量的分布

SELECT * FROM SYS.PARTITIONS WHERE OBJECT_ID = OBJECT_ID('PARTITIONEDORDERS')