SQL Server 分区表补充说明

分区教程参阅：http://database.9sssd.com/mssql/art/951

切换分区（归档）：http://technet.microsoft.com/zh-cn/library/ms191160(v=sql.105).aspx

?

补充：

1. 数据更新时，会根据分区依据，数据在文件组间移动
2. 归档时，外键约束将阻止归档（同文件组的不同表间归档）

?

理想方案：正常分区，定期结转

?

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