SQLServer2005分區(qū)表應(yīng)用實例

thy 2008-12-23

展開全文

--1 建分區(qū)函數(shù)，用于自動劃分物理表數(shù)據(jù)的流向（建好后可以在Databases\[dbName]\Storage中看到）
/* 下面分成四個區(qū)域<BigScreen, >=BigScreen且<Computer, >=Computer且<PoolTable, >=PoolTable
若是RIGHT，則X1 < BigScreen <= X2 < Computer <= X3 < PoolTable <= X4
若是LEFT，則X1 <= BigScreen < X2 <= Computer < X3 <= PoolTable < X4
*/

CREATE PARTITION FUNCTION [pf_Product_Partition](VARCHAR(10)) AS RANGE
RIGHT
FOR VALUES (N'BigScreen', N'Computer', N'PoolTable')

--2 建分區(qū)方案，用于與上面的function關(guān)聯(lián)（建好后可以在Storage中看到）
--概念上的schema可以與物理文件組關(guān)聯(lián)，這樣可以獲得更加效率，具體可以參考http://www./blogs/woody/archive/2006/08/24/1574.aspx

CREATE PARTITION SCHEME [ps_Product_Scheme] AS PARTITION
[pf_Product_Partition]
ALL TO ([PRIMARY])

--3 建表，把需要分區(qū)的字段關(guān)聯(lián)到分區(qū)方案schema上

CREATE TABLE [SalesHistoryArchive]
(
       [SaleID] [int] IDENTITY(1,1),
       [Product] [varchar](10) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
)ON [ps_Product_Scheme](Product)
GO

--創(chuàng)建分區(qū)索引
--CREATE INDEX PARTITION_INDEX ON SalesHistoryArchive(Product) ON [ps_Product_Scheme](Product)

--4 建立演示數(shù)據(jù)

DECLARE @i SMALLINT
SET @i = 1
WHILE (@i <=10000)
BEGIN
    INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES('Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57))
    INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES('BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13))
    INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
    VALUES('PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29))
    SET @i = @i + 1
END

--其它測試數(shù)據(jù)
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES('Aomputer', DATEADD(mm, 1, '3/11/1919'), DATEPART(ms, GETDATE()) + (1 + 57))

--其它測試數(shù)據(jù)
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES('Bia', DATEADD(mm, 1, '3/11/1919'), DATEPART(ms, GETDATE()) + (2 + 57))

--其它測試數(shù)據(jù)
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES('Zomputer', DATEADD(mm, 2, '3/11/1919'), DATEPART(ms, GETDATE()) + (3 + 57))

--5 可以看到每條數(shù)據(jù)在第幾個分區(qū)中（根據(jù)上述情況，共有四個分區(qū)）

SELECT $partition.[pf_Product_Partition](Product), *
FROM SalesHistoryArchive

--6 看看每個分區(qū)的記錄數(shù)

SELECT * From sys.partitions
WHERE OBJECT_NAME(object_id) = 'SalesHistoryArchive'

--7 轉(zhuǎn)移 把表SalesHistoryArchive分區(qū)2中的數(shù)據(jù)轉(zhuǎn)移到bak中，也可以轉(zhuǎn)移到指定的分區(qū)2中
--好處，速度太快了，你可以對這組數(shù)據(jù)進行查詢或刪除

SELECT * INTO SalesHistoryArchiveBak FROM SalesHistoryArchive WHERE 1=2
ALTER TABLE SalesHistoryArchive SWITCH PARTITION 2 TO SalesHistoryArchiveBak
--ALTER TABLE SalesHistoryArchive SWITCH PARTITION 4 TO SalesHistoryArchiveBak PARTITION 4

--8 性能對比，請參考
--http://www.cnblogs.com/13590/archive/2007/07/09/810770.html

--9 刪除

DROP TABLE SalesHistoryArchive
GO
DROP TABLE SalesHistoryArchiveBak
GO
DROP PARTITION SCHEME ps_Product_Scheme
GO
DROP PARTITION FUNCTION pf_Product_Partition
GO