2.为将要进行归档的数据做好准备
存放归档数据的数据表必须与分区数据表具有完全一致的结构和聚集索引。一会儿我们将把文件组2003Q3中的数据进行归档
(1)创建用于归档的数据表：
CREATE TABLE AdventureWorks2008Test.[dbo].[Orders2003Q3]
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [2003Q3]
GO
---------------------------------------------
-- The table must have the same clustered
-- index definition!
---------------------------------------------
CREATE CLUSTERED INDEX Orders2003Q3CLInd
ON Orders2003Q3(OrderDate, OrderID)
ON [2003Q3]
GO
(
[OrderID] [int] NOT NULL,
[EmployeeID] [int] NULL,
[VendorID] [int] NULL,
[TaxAmt] [money] NULL,
[Freight] [money] NULL,
[SubTotal] [money] NULL,
[Status] [tinyint] NOT NULL ,
[RevisionNumber] [tinyint] NULL,
[ModifiedDate] [datetime] NULL,
[ShipMethodID] tinyint NULL,
[ShipDate] [datetime] NOT NULL,
[OrderDate] [datetime] NOT NULL,
[TotalDue] [money] NULL
) ON [2003Q3]
GO
---------------------------------------------
-- The table must have the same clustered
-- index definition!
---------------------------------------------
CREATE CLUSTERED INDEX Orders2003Q3CLInd
ON Orders2003Q3(OrderDate, OrderID)
ON [2003Q3]
GO
(2)执行如下代码，进行分区切换。注意，此时只是将分区从分区表中移除，数据实际并没有删除，但在OrdersRange 数据表中将无法再看到这些数据，因为这些数据已经被“切换”到了Orders2003Q3 表中
ALTER TABLE OrdersRange
SWITCH PARTITION 2
TO Orders2003Q3
GO
SWITCH PARTITION 2
TO Orders2003Q3
GO
3.验证分区数据表中已经不再包含切换出去的数据
(1)为了确保分区 2中的数据已经被删除，执行如下语句进行查看：
SELECT $partition.OrderDateRangePFN(OrderDate)
AS 'Parition Number'
, min(OrderDate) AS 'Min Order Date'
, max(OrderDate) AS 'Max Order Date'
, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1
GO
AS 'Parition Number'
, min(OrderDate) AS 'Min Order Date'
, max(OrderDate) AS 'Max Order Date'
, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1
GO
(2)此时可以看到只有分区3，4，5中有数据
