如轉(zhuǎn)載,請注明出處: http://blog.csdn.net/robinson_0612/archive/2009/11/10/4794371.aspx
?
?
SQL server 2005 切換分區(qū)表
在日常工作中經(jīng)常需要用到分區(qū)表切換的問題,筆者在此列出幾種常見的分區(qū)表切換的問題,供參考。
一、分區(qū)表的切換無外乎以下三種形式:
1.將一個(gè)分區(qū)中的數(shù)據(jù)切換成單個(gè)表。
2.將表作為分區(qū)切換到已分區(qū)的表中。
3.將分區(qū)從一個(gè)已分區(qū)表切換到另一個(gè)已分區(qū)表。
二、切換分區(qū)表的主要手段:
ALTER TABLE Table_name SWITCH
[ PARTITION source_partition_number_expression ]
TO [ schema_name. ] target_table [ PARTITION target_ partition_number_expression ]
三、切換分區(qū)時(shí)的注意事項(xiàng):
1.源表為已分區(qū)的表,必須創(chuàng)建所需的目標(biāo)表,目標(biāo)表可以為單個(gè)表(用于分區(qū)切換到單個(gè)表),也可以為已分區(qū)的表(用于分區(qū)表之間的切換)。
2.源表和目標(biāo)表必須位于同一文件組,具有相同的表結(jié)構(gòu),且它們的大型值列也必須存儲(chǔ)于同一文件組,任何對(duì)應(yīng)的索引或索引分區(qū)也必須位于同一文件組。
3.無論目標(biāo)表是已分區(qū)表還是普通表,目標(biāo)表必須為空。
4.對(duì)目標(biāo)表定義了任何 CHECK 約束,則對(duì)源表也需要定義這些約束
傳輸分區(qū)時(shí),能夠?qū)崿F(xiàn)數(shù)據(jù)的快速切換,因?yàn)樗]有物理上移動(dòng)數(shù)據(jù),只是更改了有關(guān)數(shù)據(jù)存儲(chǔ)位置的元數(shù)據(jù)。故在執(zhí)行 SWITCH 操作之前,從中移出該分區(qū)的表(源表)以及接收該分區(qū)的表(目標(biāo)表)都必須存在于數(shù)據(jù)庫中。
四、以下演示切換分區(qū)使用的數(shù)據(jù)庫和表,使用SQL server 2005自帶的數(shù)據(jù)庫AdventureWorks中的Sales.SalesOrderHeader表來創(chuàng)建分區(qū),考慮到該表太多的參照和約束關(guān)系,采取應(yīng)用該表的數(shù)據(jù)來生成一張新表dbo.Orders,再將dbo.Orders轉(zhuǎn)換為分區(qū)表,關(guān)于普通表轉(zhuǎn)換為分區(qū)表請參照:實(shí)驗(yàn)三:
SQL server 2005基于已存在的表創(chuàng)建分區(qū)
。 */
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
?
-------------------------------------------------------------------
--查看各分區(qū)所包含的記錄數(shù)
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
--四、以下實(shí)現(xiàn)對(duì)分區(qū)的切換操作。
-------------------------------------------------------------------------------
--將一個(gè)分區(qū)中的數(shù)據(jù)切換成單個(gè)表
--------------------------------------------------------------------------------
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);--創(chuàng)建與源表相同的索引鍵,否則報(bào)錯(cuò)
GO
ALTER TABLE dbo.Orders SWITCH PARTITION 4 TO dbo.Orders_2004;? --切換分區(qū)到表
GO
SELECT * FROM dbo.Orders_2004??? --查看剛剛切換過來的數(shù)據(jù)
SELECT * FROM dbo.orders WHERE $PARTITION.Part_func_orders(orderdate) = 4 --4號(hào)分區(qū)中已經(jīng)沒有數(shù)據(jù)了
--------------------------------------------------------------------------------
--將表作為分區(qū)切換到已分區(qū)的表中
--------------------------------------------------------------------------------
ALTER TABLE dbo.Orders_2004?????????? --目標(biāo)表定義了邊界條件,需增加Check 約束
? ADD CONSTRAINT CK_Orderdate
??? CHECK (Orderdate >= '20040101' AND Orderdate < '20050101');
GO
ALTER TABLE dbo.Orders_2004 SWITCH TO dbo.Orders PARTITION 4;
GO
-------------------------------------------------------------------------------
--將分區(qū)從一個(gè)已分區(qū)表切換到另一個(gè)已分區(qū)表
-------------------------------------------------------------------------------
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
-----------------------------------------------------------------------------
--查看切換后的結(jié)果
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
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長非常感激您!手機(jī)微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
