分區教程參閱：http://database.9sssd.com/mssql/art/951

切換分區（歸檔）：http://technet.microsoft.com/zh-cn/library/ms191160(v=sql.105).aspx

?

補充：

1. 數據更新時，會根據分區依據，數據在文件組間移動
2. 歸檔時，外鍵約束將阻止歸檔（同文件組的不同表間歸檔）

?

理想方案：正常分區，定期結轉

?

USE[master]

GO

CREATEDATABASESalesONPRIMARY

(

NAME=N'Sales',

FILENAME=N'd:/temp/data/Primary/Sales.mdf',

SIZE=3MB,

MAXSIZE=100MB,

FILEGROWTH=10%

),FILEGROUPFG1

????(

???? NAME=N'File1',

???? FILENAME=N'd:/temp/data/FG1/File1.ndf',

???? SIZE= 1MB,

???? MAXSIZE= 100MB,

???? FILEGROWTH= 10%

????),FILEGROUPFG2

????(

???? NAME=N'File2',

???? FILENAME=N'd:/temp/data/FG2/File2.ndf',

???? SIZE= 1MB,

???? MAXSIZE= 100MB,

???? FILEGROWTH= 10%

????),FILEGROUPFG3

????(

???? NAME=N'File3',

???? FILENAME=N'd:/temp/data/FG3/File3.ndf',

???? SIZE= 1MB,

???? MAXSIZE= 100MB,

???? FILEGROWTH= 10%

????)LOGON

????(

???? NAME=N'Sales_Log',

???? FILENAME=N'd:/temp/data/Primary/Sales_Log.ldf',

???? SIZE= 1MB,

???? MAXSIZE= 100MB,

???? FILEGROWTH= 10%

????)

????GO

?

USEsales

GO

?

CREATEPARTITIONFUNCTIONpf_OrderDate(DATETIME)

ASRANGERIGHT

FORVALUES ('2003/01/01', '2004/01/01')

????GO

????

CREATEPARTITIONSCHEMEps_OrderDate

ASPARTITIONpf_OrderDate

TO(FG1,FG2,FG3)

????GO

?????

????

CREATETABLEOrders

(

OrderIDINTIDENTITY(10000, 1),

OrderDateDATETIMENOTNULL,

CustomerIDINTNOTNULL,

CONSTRAINTPK_OrdersPRIMARYKEY (OrderID, OrderDate)

)

ONps_OrderDate(OrderDate)

????GO

CREATETABLEOrdersHistory

(

OrderIDINTIDENTITY(10000, 1),

OrderDateDATETIMENOTNULL,

CustomerIDINTNOTNULL,

CONSTRAINTPK_OrdersHistoryPRIMARYKEY (OrderID, OrderDate)

)

ONps_OrderDate(OrderDate)

????GO

????

????

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2002/6/25', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2002/8/13', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2002/8/25', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2002/9/23', 1000 )

????GO

?

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2003/6/25', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2003/8/13', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2003/8/25', 1000 )

INSERTINTOdbo.Orders

(OrderDate, CustomerID)

VALUES ('2003/9/23', 1000 )

????GO

????

SELECT*

FROMdbo.Orders

WHERE$partition.pf_orderdate(orderdate)= 1

SELECT*

FROMdbo.Orders

PRINTN'數據更新后，分區變化'

UPDATEdbo.Orders

SETOrderDate='2004-9-8'

WHEREOrderID= 10000

????

SELECT*

FROMdbo.Orders

WHERE$partition.pf_orderdate(orderdate)= 1

SELECT*

FROMdbo.Orders

?

PRINTN'數據歸檔,外鍵阻止歸檔'

CREATETABLECustomer(id INTPRIMARYKEY)

INSERTINTOcustomer

VALUES ( 1000 )

ALTERTABLEordersADDCONSTRAINTfk_orders_customerFOREIGNKEY (customerid)REFERENCES Customer(id)

?

CREATETABLEorder_detail

(

idINT,

ORDERidINT,

order_dateDATETIME,

CONSTRAINTPK_Orders_detailPRIMARYKEY (ORDERid, Order_Date),

CONSTRAINTfk_orderFOREIGNKEY (ORDERid, order_date) REFERENCESdbo.Orders(OrderID, OrderDate)

)

?

INSERTINTOorder_detail

VALUES ( 1, 10000,'2004/9/8')

?

?

ALTERTABLEorderssWITCHPARTITION 2 TOordersHistoryPARTITION 2

GO

/*

消息4967，級別16，狀態1，第1 行

ALTER TABLE SWITCH 語句失敗。由于源表'Sales.dbo.orders' 包含約束'fk_order' 的主鍵，因此不允許使用SWITCH。

?

*/

SELECT*

FROMdbo.Orders

WHERE$partition.pf_orderdate(orderdate)= 1

SELECT*

FROMdbo.Orders