分区表
测试版本：
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
------新加文件组------
ALTER DATABASE [PARTITION] ADD FILEGROUP LogFG1
GO
ALTER DATABASE [PARTITION] ADD FILEGROUP LogFG2
GO
ALTER DATABASE [PARTITION] ADD FILEGROUP LogFG3
GO
ALTER DATABASE [PARTITION] ADD FILEGROUP LogFG4
GO
---添加文件组文件-------
---初始大小和增长速度和PRIMARY文件组保持一致------------
ALTER DATABASE [PARTITION] ADD FILE
(
NAME=N'LogFile1',
FILENAME=N'F:PartitionLogData1.ndf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240KB
)TO FILEGROUP LogFG1
GO
ALTER DATABASE [PARTITION] ADD FILE
(
NAME=N'LogFile2',
FILENAME=N'F:PartitionLogData2.ndf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240KB
)TO FILEGROUP LogFG2
GO
ALTER DATABASE [PARTITION] ADD FILE
(
NAME=N'LogFile3',
FILENAME=N'F:PartitionLogData3.ndf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240KB
)TO FILEGROUP LogFG3
GO
ALTER DATABASE [PARTITION] ADD FILE
(
NAME=N'LogFile4',
FILENAME=N'F:PartitionLogData4.ndf',
SIZE = 5120KB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10240KB
)TO FILEGROUP LogFG4
GO
SELECT * FROM SYS.FILEGROUPS---文件组
----创建分区函数------
CREATE PARTITION FUNCTION PF_LOG (DATETIME)
AS RANGE LEFT FOR VALUES
(
'2016-01-31 23:59:59.997',
'2016-02-29 23:59:59.997',
'2016-03-31 23:59:59.997'
)
GO
当月的最大时间和最小时间
---最小时间--
SELECT CONVERT(datetime,CONVERT(char(8),getdate(),120)+'1')
------当月最大时间------------------
SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1,getdate()),120)+'1')+' 23:59:59.998'
------创建分区方案----
CREATE PARTITION SCHEME PS_LOG AS PARTITION PF_LOG
TO
(
LogFG1,
LogFG2,
LogFG3,
LogFG4
)
GO
----创建分区表-----
CREATE TABLE Switch_Log
(
ID INT IDENTITY(1,1),
NAME1 CHAR(5),
NAME2 CHAR(5),
DATE1 DATETIME NOT NULL,
)
ON PS_LOG(DATE1)
GO
-------创建分区索引以及主键-----------
（创建唯一性索引，索引要包含分区列）
CREATE CLUSTERED INDEX IX_DATE1 ON Switch_Log(DATE1)
ALTER TABLE Switch_Log ADD CONSTRAINT PK_Log PRIMARY KEY NONCLUSTERED (ID,DATE1)
CREATE INDEX IX_NAME1 ON Switch_Log(NAME1)
------插入测试数据-----
INSERT INTO Switch_Log (NAME1,NAME2,DATE1)
VALUES ('AAAAA','BBBBB','2016-01-01 00:00:00.000')
GO 100000
INSERT INTO Switch_Log (NAME1,NAME2,DATE1)
VALUES ('CCCCC','DDDDD','2016-02-01 00:00:00.000')
GO 100000
INSERT INTO Switch_Log (NAME1,NAME2,DATE1)
VALUES ('EEEEE','FFFFF','2016-03-01 00:00:00.000')
GO 100000
-------------测试1-----------------
SELECT $PARTITION.PF_LOG('2016-01-02 00:00:00.000')
查找某个日期所在的分区
-------测试SWITCH-------------------
CREATE TABLE Switch_Log1
(
ID INT NOT NULL,
NAME1 CHAR(5),
NAME2 CHAR(5),
DATE1 DATETIME NOT NULL,
) ON PS_LOG(DATE1)
GO
---Switch目标表时，目标表要和分区表聚集索引要保持一致，数据是按聚集排列的，switch秒级别需要两个表聚集一致
--- 如果目标表swtich 分区表 则所有索引都要保持一致
CREATE CLUSTERED INDEX IX_DATE1 ON Switch_Log1(DATE1)
ALTER TABLE Switch_Log1 ADD CONSTRAINT PK_Log1 PRIMARY KEY NONCLUSTERED (ID,DATE1)
CREATE INDEX IX_NAME1 ON Switch_Log1(NAME1)
----SWITCH--------------
ALTER TABLE [dbo].[Switch_Log] SWITCH PARTITION 1 TO [dbo].[Switch_Log1] PARTITION 1
ALTER TABLE [dbo].[Switch_Log1] SWITCH PARTITION 1 TO [dbo].[Switch_Log] PARTITION 1
------MERGE--------
ALTER PARTITION FUNCTION [PF_LOG]() MERGE RANGE ('2016-01-31 23:59:59.997')
合并分区 根据 开始分区函数定义LEFT/RIGHT 定义来 删除分区范围
比如： 测试案例，我们用了left ，则第一分区的数据会合并到第二分区；RIGHT反之亦然。
-----------------SPLIT------------------
ALTER PARTITION SCHEME [PS_LOG] NEXT USED LogFG1
ALTER PARTITION FUNCTION [PF_LOG]() SPLIT RANGE ('2016-01-31 23:59:59.997')
先指定分区方案，再新加分区函数范围。
新的分区建成；
查询所有分区，所在的区、页数、行数、分区范围；
SELECT OBJECT_NAME(INS.OBJECT_ID)as '表名',
PS.partition_number AS '分区',
FG.NAME as '文件组',
AU.TOTAL_PAGES AS '页数',
CASE boundary_value_on_right WHEN 1 THEN 'RIGHT'
ELSE 'LEFT' END as 'L/R',
VALUE AS '分区界限',
PS.ROWS AS '行数',
CASE when ps.data_compression=0 then '未压缩'
ELSE '压缩'
END AS '是/否压缩'
FROM SYS.PARTITIONS PS
INNER JOIN SYS.INDEXES INS
ON PS.OBJECT_ID=INS.OBJECT_ID
AND PS.INDEX_ID=INS.INDEX_ID
JOIN SYS.partition_schemes PSS
ON PSS.data_space_id=INS.data_space_id
JOIN SYS.partition_functions PF
ON PF.function_id=PSS.function_id
LEFT JOIN SYS.partition_range_values PRV
ON PRV.function_id=PF.function_id
AND PS.partition_number=PRV.boundary_id
JOIN SYS.destination_data_spaces DS
ON DS.partition_scheme_id=PSS.data_space_id
AND DS.destination_id=PS.partition_number
JOIN SYS.FILEGROUPS FG
ON DS.destination_id=FG.data_space_id
JOIN (SELECT CONTAINER_ID,SUM(TOTAL_PAGES)AS TOTAL_PAGES
FROM SYS.allocation_units GROUP BY CONTAINER_ID) AS AU
ON AU.container_id=PS.partition_id
WHERE INS.INDEX_ID IN (0,1)
-----删除分区函数----刚好和我们创建顺序相反------
删除架构下面的表对象---架构-----函数----组文件------组---
ALTER DATABASE [PARTITION] REMOVE FILE LogFile1
ALTER DATABASE [PARTITION] REMOVE FILE LogFile2
ALTER DATABASE [PARTITION] REMOVE FILE LogFile3
ALTER DATABASE [PARTITION] REMOVE FILE LogFile4
ALTER DATABASE [PARTITION] REMOVE FILEGROUP LogFG1
ALTER DATABASE [PARTITION] REMOVE FILEGROUP LogFG2
ALTER DATABASE [PARTITION] REMOVE FILEGROUP LogFG3
ALTER DATABASE [PARTITION] REMOVE FILEGROUP LogFG4