SQL Server 2005 分区表应用实例

最新推荐文章于 2023-10-31 14:02:43 发布

panys

最新推荐文章于 2023-10-31 14:02:43 发布

阅读量2.3k

点赞数

分类专栏： SQLServer&Oracle 文章标签： sql server insert scheme function table schema

本文链接：https://blog.csdn.net/panys/article/details/1882481

版权

SQLServer&Oracle 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

本文详细介绍如何在SQL Server中实现分区表，包括创建分区函数、分区方案、带有分区字段的表，以及创建分区索引的过程。此外，还提供了演示数据的插入方法，并展示了如何查询数据所在的分区及各分区的记录数。

摘要由CSDN通过智能技术生成

--1 建分区函数，用于自动划分物理表数据的流向（建好后可以在Databases/[dbName]/Storage中看到）
/* 下面分成四个区域<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 建分区方案，用于与上面的function关联（建好后可以在Storage中看到）
--概念上的schema可以与物理文件组关联，这样可以获得更加效率，具体可以参考http://www.agilelabs.cn/blogs/woody/archive/2006/08/24/1574.aspx

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

--3 建表，把需要分区的字段关联到分区方案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

--创建分区索引
--CREATE INDEX PARTITION_INDEX ON SalesHistoryArchive(Product) ON [ps_Product_Scheme](Product)

--4 建立演示数据

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

-- 其它测试数据
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES ( ' Aomputer ' , DATEADD (mm, 1 , ' 3/11/1919 ' ), DATEPART (ms, GETDATE ()) + ( 1 + 57 ))

-- 其它测试数据
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES ( ' Bia ' , DATEADD (mm, 1 , ' 3/11/1919 ' ), DATEPART (ms, GETDATE ()) + ( 2 + 57 ))

-- 其它测试数据
INSERT INTO SalesHistoryArchive(Product, SaleDate, SalePrice)
VALUES ( ' Zomputer ' , DATEADD (mm, 2 , ' 3/11/1919 ' ), DATEPART (ms, GETDATE ()) + ( 3 + 57 ))

--5 可以看到每条数据在第几个分区中（根据上述情况，共有四个分区）

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

--6 看看每个分区的记录数

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

--7 转移 把表SalesHistoryArchive分区2中的数据转移到bak中，也可以转移到指定的分区2中
--好处，速度太快了，你可以对这组数据进行查询或删除

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