SQL-Server-2000和SQL-Server-2005的分区.doc

SQL Server 2000里的分区 -（SQL Server 2005里面的分区技术，为大部分朋友所熟知，但对于SQL Server 2000里面的表分区，很多朋友可能有些迷糊，本方将为大家描述一下SQL Server 2000及SQL Server 20005的分区技术。其实SQL Server 2000里面本没有真正的分区，但为了弥补这一缺陷，人们利用视图和触发器的组合，创造出一种分区方案，对于这样种分区方案，姑且称之为“伪分区”。） - -准备1 SELECT * INTO CustomersGer FROM Customers WHERE Customers.Country=Germany SELECT * INTO CustomersMex FROM Customers WHERE Customers.Country=Mexico GO -准备2 CREATE VIEW v_Customers_Ger_Mex AS SELECT * FROM CustomersGer UNION SELECT * FROM CustomersMex GO -创建INSTEAD OF触发器 CREATE TRIGGER tr_Customers_Update ON v_Customers_Ger_Mex INSTEAD OF UPDATE AS DECLARE Country nvarchar(15) SET Country=(SELECT Country FROM inserted) IF Country=Germany BEGIN UPDATE CustomersGer SET CustomersGer.Phone=Inserted.Phone FROM CustomersGer JOIN Inserted ON CustomersGer.CustomerID=Inserted.CustomerID END ELSE IF Country=Mexico BEGIN UPDATE CustomersMex SET CustomersMex.Phone=Inserted.Phone FROM CustomersMex JOIN Inserted ON CustomersMex.CustomerID=Inserted.CustomerID END GO -测试 UPDATE v_Customers_Ger_Mex SET Phone=030-007xfxx WHERE CustomerID=ALFKI SELECT CustomerID,Phone FROM v_Customers_Ger_Mex WHERE CustomerID=ALFKI SELECT CustomerID,Phone FROM CustomersGer WHERE CustomerID=ALFKI SELECT CustomerID,Phone FROM CustomersMex WHERE CustomerID=ALFKI GO - - SQL Server 2005里的分区 - -创建实验用数据库 USE master CREATE DATABASE Sales ON PRIMARY ( NAME = Sales_Data, FILENAME=C:DatabasesSales_dat.mdf, SIZE=3MB, MAXSIZE=10MB, FILEGROWTH=10% ), FILEGROUP FG1 ( NAME = File1, FILENAME = C:DatabasesFile1_dat.ndf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10% ), FILEGROUP FG2 ( NAME = File2, FILENAME = C:DatabasesFile2_dat.ndf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10% ), FILEGROUP FG3 ( NAME = File3, FILENAME = C:DatabasesFile3_dat.ndf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10% ) LOG ON ( NAME = Sales_Log, FILENAME = C:DatabasesSales_Log.ldf, SIZE = 1MB, MAXSIZE = 10MB, FILEGROWTH = 10% ) -创建分区函数，假定当前为2002年 USE Sales CREATE PARTITION FUNCTION pf_OrderDate (datetime) AS RANGE RIGHT FOR VALUES (2003/01/01, 2004/01/01) GO -USE Sales -ALTER PARTITION FUNCTION pf_OrderDate () -SPLIT RANGE (2005/01/01) -GO -创建分区方案 USE Sales GO CREATE PARTITION SCHEME ps_OrderDate AS PARTITION pf_OrderDate TO (FG1, FG2, FG3) GO -创建实验用数据表，并将其绑定到分区方案上 USE Sales GO CREATE TABLE dbo.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 dbo.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 - SELECT * FROM sys.partition_schemes - SELECT * FROM sys.partitions -向数据表中写入2002年的范例数据 USE Sales 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 SELECT * FROM Orders SELECT * FROM dbo.OrdersHistory GO -2003年年初利用分区交换执行快速的数据归档 USE Sales GO ALTER TABLE dbo.Orders SWITCH PARTITION 1 TO dbo.OrdersHistory PARTITION 1 GO SELECT * FROM Orders SELECT * FROM dbo.OrdersHistory GO -可以通过以下代码确认数据归档是否成功： -SELECT * FROM dbo.Orders -SELECT * FROM dbo.OrdersHistory -向数据表中写入2003年的范例数据 USE Sales 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 Orders SELECT * FROM dbo.OrdersHistory GO -2004年年初利用分区交换执行快速的数据归档 USE Sales GO ALTER TABLE dbo.Orders SWITCH PARTITION 2 TO dbo.OrdersHistory PARTITION 2 GO SELECT * FROM Orders SELECT * FROM dbo.OrdersHistory GO -对2002年数据分区和2003年数据分区进行合并 USE Sales GO ALTER PARTITION FUNCTION pf_OrderDate() MERGE RANGE (2003/01/01) GO -SELECT * FROM sys.partition_schemes -SELECT * FROM sys.partitions -使用分区分裂功能准备2005年的数据分区 USE Sales GO ALTER PARTITION SCHEME ps_OrderDate NEXT USED FG2 ALTER PARTITION FUNCTION pf_OrderDate() SPLIT RANGE (2005/01/01) GO -可使用以下代码检查各分区中的数据行数： SELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition, COUNT(*) AS COUNT FROM dbo.Orders GROUP BY $PARTITION.pf_OrderDate(OrderDate) ORDER BY Partition ; GO SELECT $PARTITION.pf_OrderDate(OrderDate) AS Partition, COUNT(*) AS COUNT FROM dbo.OrdersHistory GROUP BY $PARTITION.pf_OrderDate(OrderDate) ORDER BY Partition ; GO - USE Sales GO INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (2004/6/25, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (2004/8/13, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (2004/8/25, 1000) INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES (2004/9/23, 1000) GO INSERT INTO dbo.Orders (OrderDa