分区教程参阅：http://database.9sssd.com/mssql/art/951
切换分区（归档）：http://technet.microsoft.com/zh-cn/library/ms191160(v=sql.105).aspx
?
补充：
- 数据更新时，会根据分区依据，数据在文件组间移动
- 归档时，外键约束将阻止归档（同文件组的不同表间归档）
?
理想方案：正常分区，定期结转
?
USE [master]
GO
CREATE DATABASE Sales ON PRIMARY
(
NAME=N‘Sales‘,
FILENAME=N‘d:\temp\data\Primary\Sales.mdf‘,
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=10%
), FILEGROUP FG1
????(
???? NAME = N‘File1‘,
???? FILENAME = N‘d:\temp\data\FG1\File1.ndf‘,
???? SIZE = 1MB,
???? MAXSIZE = 100MB,
???? FILEGROWTH = 10%
????), FILEGROUP FG2
????(
???? NAME = N‘File2‘,
???? FILENAME = N‘d:\temp\data\FG2\File2.ndf‘,
???? SIZE = 1MB,
???? MAXSIZE = 100MB,
???? FILEGROWTH = 10%
????), FILEGROUP FG3
????(
???? NAME = N‘File3‘,
???? FILENAME = N‘d:\temp\data\FG3\File3.ndf‘,
???? SIZE = 1MB,
???? MAXSIZE = 100MB,
???? FILEGROWTH = 10%
????) LOG ON
????(
???? NAME = N‘Sales_Log‘,
???? FILENAME = N‘d:\temp\data\Primary\Sales_Log.ldf‘,
???? SIZE = 1MB,
???? MAXSIZE = 100MB,
???? FILEGROWTH = 10%
????)
????GO
?
USE sales
GO
?
CREATE PARTITION FUNCTION pf_OrderDate (DATETIME)
AS RANGE RIGHT
FOR VALUES (‘2003/01/01‘, ‘2004/01/01‘)
????GO
????
CREATE PARTITION SCHEME ps_OrderDate
AS PARTITION pf_OrderDate
TO(FG1,FG2,FG3)
????GO
???? ?
????
CREATE TABLE Orders
(
OrderID INT IDENTITY(10000, 1) ,
OrderDate DATETIME NOT NULL ,
CustomerID INT NOT NULL ,
CONSTRAINT PK_Orders PRIMARY KEY ( OrderID, OrderDate )
)
ON ps_OrderDate(OrderDate)
????GO
CREATE TABLE OrdersHistory
(
OrderID INT IDENTITY(10000, 1) ,
OrderDate DATETIME NOT NULL ,
CustomerID INT NOT NULL ,
CONSTRAINT PK_OrdersHistory PRIMARY KEY ( OrderID, OrderDate )
)
ON ps_OrderDate(OrderDate)
????GO
????
????
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2002/6/25‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2002/8/13‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2002/8/25‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2002/9/23‘, 1000 )
????GO
?
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2003/6/25‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2003/8/13‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2003/8/25‘, 1000 )
INSERT INTO dbo.Orders
( OrderDate, CustomerID )
VALUES ( ‘2003/9/23‘, 1000 )
????GO
????
SELECT *
FROM dbo.Orders
WHERE $partition.pf_orderdate(orderdate) = 1
SELECT *
FROM dbo.Orders
PRINT N‘数据更新后，分区变化‘
UPDATE dbo.Orders
SET OrderDate = ‘2004-9-8‘
WHERE OrderID = 10000
????
SELECT *
FROM dbo.Orders
WHERE $partition.pf_orderdate(orderdate) = 1
SELECT *
FROM dbo.Orders
?
PRINT N‘数据归档,外键阻止归档‘
CREATE TABLE Customer ( id INT PRIMARY KEY )
INSERT INTO customer
VALUES ( 1000 )
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer FOREIGN KEY (customerid) REFERENCES Customer (id)
?
CREATE TABLE order_detail
(
id INT ,
ORDERid INT ,
order_date DATETIME ,
CONSTRAINT PK_Orders_detail PRIMARY KEY ( ORDERid, Order_Date ) ,
CONSTRAINT fk_order FOREIGN KEY ( ORDERid, order_date ) REFERENCES dbo.Orders ( OrderID, OrderDate )
)
?
INSERT INTO order_detail
VALUES ( 1, 10000, ‘2004/9/8‘ )
?
?
ALTER TABLE orders SWITCH PARTITION 2 TO ordersHistory PARTITION 2
GO
/*
消息4967，级别16，状态1，第1 行
ALTER TABLE SWITCH 语句失败。由于源表‘Sales.dbo.orders‘ 包含约束‘fk_order‘ 的主键，因此不允许使用SWITCH。
?
*/
SELECT *
FROM dbo.Orders
WHERE $partition.pf_orderdate(orderdate) = 1
SELECT *
FROM dbo.Orders