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

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