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