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:/Databases/Sales_dat.mdf',
SIZE=3MB,
MAXSIZE=10MB,
FILEGROWTH=10%
),
FILEGROUP FG1
(
NAME = 'File1',
FILENAME = 'C:/Databases/File1_dat.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%
),
FILEGROUP FG2
(
NAME = 'File2',
FILENAME = 'C:/Databases/File2_dat.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%
),
FILEGROUP FG3
(
NAME = 'File3',
FILENAME = 'C:/Databases/File3_dat.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%
)
LOG ON
(
NAME = 'Sales_Log',
FILENAME = 'C:/Databases/Sales_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 (OrderDate, CustomerID) VALUES ('2005/6/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/8/13', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/8/25', 1000)
INSERT INTO dbo.Orders (OrderDate, CustomerID) VALUES ('2005/9/23', 1000)
GO
SQL Server 2000和SQL Server 2005的分区
最新推荐文章于 2024-10-17 10:02:06 发布