SQL Server 分区表补充说明分区教程参阅：http://database.9sssd.com/mssql/art/951
切换分区（归档）：http://technet.microsoft.com/zh-cn/library/ms191160(v=sql.105).aspx
补充：
数据更新时，会根据分区依据，数据在文件组间移动归档时，外键约束将阻止归档（同文件组的不同表间归档）
理想方案：正常分区，定期结转
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