如转载,请注明出处:http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx
SQL server 2005 切换分区表 在日常工作中经常需要用到分区表切换的问题,笔者在此列出几种常见的分区表切换的问题,供参考。 一、分区表的切换无外乎以下三种形式: 1.将一个分区中的数据切换成单个表。 2.将表作为分区切换到已分区的表中。 3.将分区从一个已分区表切换到另一个已分区表。
二、切换分区表的主要手段: ALTER TABLE Table_name SWITCH [ PARTITION source_partition_number_expression ] TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ]
三、切换分区时的注意事项: 1.源表为已分区的表,必须创建所需的目标表,目标表可以为单个表(用于分区切换到单个表),也可以为已分区的表(用于分区表之间的切换)。 2.源表和目标表必须位于同一文件组,具有相同的表结构,且它们的大型值列也必须存储于同一文件组,任何对应的索引或索引分区也必须位于同一文件组。 3.无论目标表是已分区表还是普通表,目标表必须为空。 4.对目标表定义了任何 CHECK 约束,则对源表也需要定义这些约束
传输分区时,能够实现数据的快速切换,因为它并没有物理上移动数据,只是更改了有关数据存储位置的元数据。故在执行 SWITCH 操作之前,从中移出该分区的表(源表)以及接收该分区的表(目标表)都必须存在于数据库中。
四、以下演示切换分区使用的数据库和表,使用SQL server 2005自带的数据库AdventureWorks中的Sales.SalesOrderHeader表来创建分区,考虑到该表太多的参照和约束关系,采取应用该表的数据来生成一张新表dbo.Orders,再将dbo.Orders转换为分区表,关于普通表转换为分区表请参照:实验三:SQL server 2005基于已存在的表创建分区 。 */
USE AdventureWorks GO CREATE PARTITION FUNCTION Part_func_orders(DATETIME) AS RANGE RIGHT FOR VALUES('20020101 00:00:00.000', '20030101 00:00:00.000', '20040101 00:00:00.000'); GO ---------------------------------------------------------------- ALTER DATABASE AdventureWorks ADD FILEGROUP [FG1]; GO ALTER DATABASE AdventureWorks ADD FILEGROUP [FG2]; GO ALTER DATABASE AdventureWorks ADD FILEGROUP [FG3]; GO
ALTER DATABASE AdventureWorks ADD FILE (NAME = FG1_data,FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/FG1_data.ndf',SIZE = 3MB) TO FILEGROUP [FG1]; ALTER DATABASE AdventureWorks ADD FILE (NAME = FG2_data,FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/FG2_data.ndf',SIZE = 3MB) TO FILEGROUP [FG2]; ALTER DATABASE AdventureWorks ADD FILE (NAME = FG3_data,FILENAME = 'C:/Program Files/Microsoft SQL Server/MSSQL.1/MSSQL/Data/FG3_data.ndf',SIZE = 3MB) TO FILEGROUP [FG3]; GO -------------------------------------------------------------------
CREATE PARTITION SCHEME Part_func_orders_scheme AS PARTITION Part_func_orders TO ([FG1],[FG2],[FG3],[Primary]); GO
-------------------------------------------------------------------- IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO CREATE TABLE dbo.Orders ( SalesOrderID INT NOT NULL, SalesPersonID INT , CustomerID INT NOT NULL, SalesOrderNumber NVARCHAR(25) NOT NULL, Orderdate DATETIME NOT NULL, Shipdate DATETIME )ON Part_func_orders_scheme(Orderdate); GO ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED(SalesOrderID,Customerid,Orderdate), CONSTRAINT FK_Orders_Customer_CustomerID FOREIGN KEY(CustomerID) REFERENCES Sales.Customer(Customerid), CONSTRAINT FK_Orders_SalesPerson_SalesPersonID FOREIGN KEY(SalesPersonID) REFERENCES Sales.SalesPerson(SalesPersonID); GO CREATE CLUSTERED INDEX Idx_Orderdate ON dbo.Orders(Orderdate); GO ------------------------------------------------------------------- INSERT INTO dbo.Orders SELECT SalesOrderID,SalesPersonID,CustomerID,SalesOrderNumber,Orderdate,Shipdate FROM Sales.SalesOrderHeader ------------------------------------------------------------------- --查看各分区所包含的记录数 SELECT $PARTITION.Part_func_orders(orderdate) as partition_num, MIN(orderdate) AS start_time,Max(orderdate) AS end_time,count(*) as count_num FROM dbo.Orders GROUP BY $PARTITION.Part_func_orders(orderdate) ORDER BY $PARTITION.Part_func_orders(orderdate); GO
--四、以下实现对分区的切换操作。 ------------------------------------------------------------------------------- --将一个分区中的数据切换成单个表 -------------------------------------------------------------------------------- USE [AdventureWorks] GO /****** Object: Table [dbo].[Orders_2004] Script Date: 11/10/2009 13:55:07 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO IF OBJECT_ID('dbo.orders_2004') IS NOT NULL DROP TABLE dbo.orders_2004; GO CREATE TABLE [dbo].[Orders_2004]( [SalesOrderID] [int] NOT NULL, [SalesPersonID] [int] NULL, [CustomerID] [int] NOT NULL, [SalesOrderNumber] [nvarchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [Orderdate] [datetime] NOT NULL, [Shipdate] [datetime] NULL, CONSTRAINT [PK_Orders_2004] PRIMARY KEY NONCLUSTERED ( [SalesOrderID] ASC, [CustomerID] ASC, [Orderdate] ASC )WITH (IGNORE_DUP_KEY = OFF) ); GO
ALTER TABLE [dbo].[Orders_2004] WITH CHECK ADD CONSTRAINT [FK_Orders_Customer_CustomerID_2004] FOREIGN KEY([CustomerID]) REFERENCES [Sales].[Customer] ([CustomerID]); GO ALTER TABLE [dbo].[Orders_2004] WITH CHECK ADD CONSTRAINT [FK_Orders_SalesPerson_SalesPersonID_2004] FOREIGN KEY([SalesPersonID]) REFERENCES [Sales].[SalesPerson] ([SalesPersonID]);
-------------------------------------------------------------------------------------------------- CREATE CLUSTERED INDEX Idx_Orderdate_2004 ON dbo.Orders_2004(Orderdate ASC);--创建与源表相同的索引键,否则报错 GO
ALTER TABLE dbo.Orders SWITCH PARTITION 4 TO dbo.Orders_2004; --切换分区到表 GO
SELECT * FROM dbo.Orders_2004 --查看刚刚切换过来的数据 SELECT * FROM dbo.orders WHERE $PARTITION.Part_func_orders(orderdate) = 4 --4号分区中已经没有数据了
-------------------------------------------------------------------------------- --将表作为分区切换到已分区的表中 -------------------------------------------------------------------------------- ALTER TABLE dbo.Orders_2004 --目标表定义了边界条件,需增加Check 约束 ADD CONSTRAINT CK_Orderdate CHECK (Orderdate >= '20040101' AND Orderdate < '20050101'); GO
ALTER TABLE dbo.Orders_2004 SWITCH TO dbo.Orders PARTITION 4; GO
------------------------------------------------------------------------------- --将分区从一个已分区表切换到另一个已分区表 ------------------------------------------------------------------------------- USE AdventureWorks GO CREATE PARTITION FUNCTION Part_func_orders_Archive(DATETIME) AS RANGE RIGHT FOR VALUES('20020101 00:00:00.000', '20030101 00:00:00.000', '20040101 00:00:00.000'); GO
CREATE PARTITION SCHEME Part_func_orders_Scehme_Archive AS PARTITION Part_func_orders_Archive TO ([FG1],[FG2],[FG3],[Primary]); GO
----------------------------------------------------------------------------- IF OBJECT_ID('dbo.Orders_Archive') IS NOT NULL DROP TABLE dbo.Orders_Archive; GO CREATE TABLE dbo.Orders_Archive ( SalesOrderID INT NOT NULL, SalesPersonID INT , CustomerID INT NOT NULL, SalesOrderNumber NVARCHAR(25) NOT NULL, Orderdate DATETIME NOT NULL, Shipdate DATETIME )ON Part_func_orders_Scehme_Archive(Orderdate); GO ALTER TABLE dbo.Orders_Archive ADD CONSTRAINT PK_Orders_Archive PRIMARY KEY NONCLUSTERED(SalesOrderID,Customerid,Orderdate), CONSTRAINT FK_Orders_Customer_CustomerID_Archive FOREIGN KEY(CustomerID) REFERENCES Sales.Customer(Customerid), CONSTRAINT FK_Orders_SalesPerson_SalesPersonID_Archive FOREIGN KEY(SalesPersonID) REFERENCES Sales.SalesPerson(SalesPersonID); GO CREATE CLUSTERED INDEX Idx_Orderdate_Archive ON dbo.Orders_Archive(Orderdate); GO ------------------------------------------------------------------------------ ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.Orders_Archive PARTITION 1; GO ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.Orders_Archive PARTITION 2; GO ALTER TABLE dbo.Orders SWITCH PARTITION 4 TO dbo.Orders_Archive PARTITION 4; GO
----------------------------------------------------------------------------- --查看切换后的结果 SELECT $PARTITION.Part_func_orders_Archive(Orderdate) AS Partition_num, MIN(Orderdate),MAX(Orderdate),COUNT(*) FROM dbo.Orders_Archive GROUP BY $PARTITION.Part_func_orders_Archive(Orderdate) ORDER BY $PARTITION.Part_func_orders_Archive(Orderdate);
--更多,请参阅:http://msdn.microsoft.com/zh-cn/library/ms345146(SQL.90).aspx