分区表

 

 

测试版本：   

    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:\Partition\LogData1.ndf',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10240KB

)TO FILEGROUP LogFG1

GO

ALTER DATABASE  [PARTITION] ADD FILE

(

NAME=N'LogFile2',

FILENAME=N'F:\Partition\LogData2.ndf',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10240KB

)TO FILEGROUP LogFG2

GO

ALTER DATABASE  [PARTITION] ADD FILE

(

NAME=N'LogFile3',

FILENAME=N'F:\Partition\LogData3.ndf',

SIZE = 5120KB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 10240KB

)TO FILEGROUP LogFG3

GO

ALTER DATABASE  [PARTITION] ADD FILE

(

NAME=N'LogFile4',

FILENAME=N'F:\Partition\LogData4.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