SQLServer创建分区表

分区表的定义大体上分为三个步骤：

1)定义分区函数

2)定义分区构架

3)定义分区表

************* 以下为测试代码 *************

USE [master]

GO

CREATE DATABASE [Test] ON  PRIMARY

( NAME = N‘Test‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test.mdf‘ , SIZE = 3MB , MAXSIZE = UNLIMITED, FILEGROWTH = 2048KB ),

FILEGROUP [FG1]

( NAME = N‘Test1‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test1.ndf‘ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [FG2]

( NAME = N‘Test2‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test2.ndf‘ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB ),

FILEGROUP [FG3]

( NAME = N‘Test3‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Test3.ndf‘ , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

LOG ON

( NAME = N‘Test_log‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\test_1.ldf‘ , SIZE = 1024KB , MAXSIZE = 2048MB , FILEGROWTH = 1024KB )

GO

USE [Test]

GO

--创建分区函数

CREATE PARTITION FUNCTION fnPartition(DATE)

AS RANGE RIGHT--LEFT是左边包含分割点，RIGHT是右边包含

FOR VALUES(‘2010-01-01‘,‘2012-01-01‘)

--查看分区函数是否创建成功

SELECT * FROM sys.partition_functions

USE [Test]

GO

--创建分区架构

CREATE PARTITION SCHEME SchemaForPartition

AS PARTITION fnPartition

TO ([primary], FG1, FG2)--因为有2个边界值，则有3个分区，需要指定3个文件组，也可以使用ALL指向一个文件组

--查看已创建的分区架构schema

SELECT * FROM sys.partition_schemes

--创建分区表

CREATE TABLE PartitionTable

(

id INT,

SalesDate DATE

) ON SchemaForPartition(SalesDate)--SalesDate为指定划分区列

--查看创建的分区表

SELECT * FROM SYS.partitions WHERE OBJECT_ID=OBJECT_ID(‘PartitionTable‘)

--插入测试记录

DECLARE @T DATE

SET @T = ‘2009-01-01‘

WHILE @T < GETDATE()

BEGIN

INSERT INTO PartitionTable VALUES(123, @T)

SET @T = DATEADD(MM, 1, @T)

END

--查询表记录

SELECT * FROM PartitionTable

--查询分区

SELECT $PARTITION.fnPartition(‘2009-01-01‘) AS PartitionNum--2009-01-01这条记录所在的分区号

SELECT * FROM PartitionTable WHERE $PARTITION.fnPartition(SalesDate)=2--查询2号分区的所有记录

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

SELECT $partition.fnPartition(SalesDate) AS PartitionNum

, COUNT(*) AS NumRows

FROM PartitionTable

GROUP BY $partition.fnPartition(SalesDate)

ORDER BY $partition.fnPartition(SalesDate)

--提供分割点，合并分区

ALTER PARTITION FUNCTION fnPartition()

MERGE RANGE(‘2012-01-01‘)

--拆分分区，首先加入文件组

ALTER DATABASE [Test] ADD FILEGROUP [FG4]

GO

ALTER DATABASE [Test] ADD FILE ( NAME = N‘FG4‘, FILENAME = N‘D:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\FG4.ndf‘ , SIZE = 3072KB , FILEGROWTH = 1024KB ) TO FILEGROUP [FG4]

GO

--知道分割后多出来的分区应该存储在哪个文件组

ALTER PARTITION SCHEME SchemaForPartition NEXT USED ‘FG4‘

--添加分割点

ALTER PARTITION FUNCTION fnPartition()

SPLIT RANGE(‘2013-01-01‘)

--切换分区

--PartitionTable2没有分区，将数据全部插入PartitionTable的分区4中，表结构要一致

ALTER TABLE dbo.PartitionTable2

SWITCH TO dbo.PartitionTable PARTITION 4

本文出自 “畅想天空” 博客，请务必保留此出处http://kinwar.blog.51cto.com/3723399/1381419

SQLServer创建分区表,古老的榕树,5-wow.com