分区表

分区表的基本操作和通过分区切换来操作分区表

1.创建数据库和添加文件组

IF DB_ID('TestDB_PT') IS NOT NULL
   DROP DATABASE TestDB_PT;
GO
CREATE DATABASE TestDB_PT
ON
PRIMARY
(Name=TestDB_PT,
FILENAME='C:\DB\TestDB_PT\TestDB_PT.mdf',
SIZE=2MB,MAXSIZE=1024MB,FILEGROWTH=10MB
)
LOG ON
(
Name=TestDB_PT_LOG,
FILENAME='C:\DB\TestDB_PT\TestDB_PT_LOG.ldf',
SIZE=1MB,MAXSIZE=1024MB,FILEGROWTH=10MB
)
GO
USE TestDB_PT
GO

-- 添加文件组到数据库TestDB_PT

DECLARE @Count INT =4; -- 添加4个文件组
DECLARE @i INT =0;
DECLARE @SQL varchar(1000)='';
WHILE (@i<@Count)
BEGIN
SET @SQL='ALTER DATABASE TestDB_PT ADD FILEGROUP FG'+convert(varchar(10),@i+1)+';'
Exec (@SQL);
SET @i=@i+1;
END
-- 添加文件到文件组
SET @i =0;
SET @SQL =''
WHILE (@i<@Count)
BEGIN
SET @SQL='ALTER DATABASE TestDB_PT ADD FILE( NAME=FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1,FILENAME=''C:\DB\TestDB_PT\FG'+CONVERT(VARCHAR(10),@i+1)+'_Data1.ndf'' ,SIZE=1MB,MAXSIZE=100MB,FILEGROWTH=1MB ) TO FILEGROUP FG'+CONVERT(VARCHAR(10),@i+1)
Exec (@SQL)
SET @i=@i+1;
END

2.创建分区函数

CREATE PARTITION FUNCTION PFMonthly(int)
AS RANGE RIGHT –- 还可以是RANGE LEFT.详细了解RANGE RIGHT就足够了
FOR VALUES(20130601,20130701,20130801)

数据的分布如下所示：

3.创建分区方案

CREATE PARTITION SCHEME PSMonthly
AS PARTITION PFMonthly -- 分区函数
TO (FG1,FG2,FG3,FG4) -- 对应文件组个数为分区函数范围值加1

数据的分布如下所示：

4.创建分区表

CREATE TABLE [dbo].[TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON PSMonthly([DateKey]) -- 此处对应分区方案和分区列

分区函数，分区方案，分区表关系如下图：

5.用切换分区的方式填充分区表

-- 创建切换时需要的临时表
IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL
DROP TABLE TestDB_PT.dbo.Stage_TB
GO
CREATE TABLE [dbo].[Stage_TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON FG1 --必须是将要加载的数据对应的文件组

-- 添加DateKey的约束。这个必须添加，否则在切换的时候会报语法错误。这个约束的目的是防止切换的时候需要将临时表中的数据切换到不同的分区。
ALTER TABLE [dbo].[Stage_TB]
ADD CONSTRAINT CK_Stage_TB_DateKey CHECK(DateKey>= 20130501and DateKey<20130601)
-- 添加数据到Stage_TB
insert into dbo.Stage_TB
SELECT Convert(varchar(20), Dateadd(DAY,number,'2013-05-21'),112 ) DateKey
,number
FROM master.dbo.spt_values
where type='p'
and Dateadd(DAY,number,'2013-05-21') <'20130601'           
--  通过switch语句做切换
ALTER TABLE  Stage_TB
SWITCH  TO TB PARTITION 1 -- 这个地方的分区号在前面的查询中能看到

切换前如下图：

切换后入下图：

其他分区的数据加载，以此类推。加载完5，6，7月份的数据后，如下图：

-- 查询分区表中实际数据情况
select $partition.PFMonthly(DateKey) as [Partition#]
,count(*) RowCnt
,Min(DateKey) AS MinDate
,Max(DateKey) AS MaxDate
from TB
group by $partition.PFMonthly(DateKey)
order by [Partition#]    
--  删除临时表
DROP TABLE Stage_TB

6.用切换分区的方式删除最早分区数据

--   删除20130501到20130531的数据
-- 创建切换时需要的临时表
IF OBJECT_ID('TestDB_PT.dbo.Stage_TB') IS NOT NULL
DROP TABLE TestDB_PT.dbo.Stage_TB
GO
CREATE TABLE [dbo].[Stage_TB](
   [DateKey] [int] NOT NULL,
   [number] [int]  NULL
) ON FG1 --必须是将要加载的数据对应的文件组
--  通过switch语句，切换数据到临时表
ALTER TABLE  TB
SWITCH PARTITION 1 TO Stage_TB  -- 这个地方的分区号在前面的查询中能看到
-- 查看对应的分区值
SELECT 
     SPS.name AS PartitionSchemeName
   , CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
        ELSE NULL END AS PartitionID
   , SPF.name AS PartitionFunctionName
   , SPRV.value AS BoundaryValue
   , CASE WHEN SDD.destination_id > SPF.fanout THEN 1 
        ELSE 0 END AS NextUsed
   , SF.name AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF 
   ON SPS.function_id = SPF.function_id
JOIN sys.destination_data_spaces AS SDD 
   ON SDD.partition_scheme_id = SPS.data_space_id
JOIN sys.filegroups AS SF 
   ON SF.data_space_id = SDD.data_space_id
LEFT JOIN sys.partition_range_values AS SPRV 
   ON SPRV.function_id = SPF.function_id
   AND SDD.destination_id = 
CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id
   ELSE SPRV.boundary_id + 1 END 
WHERE SPS.name = 'PSMonthly';
-- 从分区函数中去掉对应值
ALTER PARTITION FUNCTION PFMonthly()
MERGE RANGE(20130601)
-- 删除临时表
DROP TABLE Stage_TB

删除前如下图：

删除后如下图：

注意：RANGE RIGHT的分区表。合并两个相邻分区后，数据存放在左边分区对应数据文件组。上图中分区1和分区2合并后存放在FG1 。

7.用切换分区的方式添加8月份数据

--将FG2设置为 NEXT USED，用split添加新的分区后，新的分区的数据将存放在FG2
ALTER PARTITION SCHEME [PSMonthly] NEXT USED FG2
-- 添加新分区值
ALTER PARTITION FUNCTION PFMonthly()
SPLIT RANGE(20130901)

添加新分区值前：

添加新分区值后：

通过临时表切换分区的方式加载8月份的数据，参照“5.用切换分区的方式填充分区表”。