SQL SERVER PARTITION TABLE 分割Table

1. 因成績資料的Table過於龐大，效能開始低落，想實作Partition Table看效能是否可改善。

2. 只有在SQL SERVER Enterprise、Developer 和 Evaluation 版本以上才有支援Partition Table。

3. 建立邏輯檔案群組及實體檔案位置。

USE PartitionA 
--建立FileGroup
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo90];
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo95];
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo100];

4. 建立實體檔案位置。

USE PartitionA 
--
DECLARE @PATH NVARCHAR(128);
DECLARE @SQL VARCHAR(1000);
--
SELECT @PATH=physical_name FROM sys.database_files WHERE file_id = 1
--
--建立實體檔案對應
SET @SQL = 'ALTER DATABASE [PartitionA] ADD FILE ( 
NAME=[PartitionTo90], 
FILENAME="' + @PATH + 'PartitionTo90.ndf", 
SIZE=100MB ) TO FILEGROUP[PartitionTo90]';
EXEC(@SQL)
SET @SQL = 'ALTER DATABASE [PartitionA] ADD FILE ( 
NAME=[PartitionTo95], 
FILENAME="' + @PATH + 'PartitionTo95.ndf", 
SIZE=100MB ) TO FILEGROUP[PartitionTo95]';
EXEC(@SQL)
SET @SQL = 'ALTER DATABASE [PartitionA] ADD FILE ( 
NAME=[PartitionTo100], 
FILENAME="' + @PATH + 'PartitionTo100.ndf", 
SIZE=100MB ) TO FILEGROUP[PartitionTo100]';
EXEC(@SQL)

5. 以年度的範圍建立Partition Function，在這建立90年以前、91~95、96~，另外也可使用日期來做區隔， 詳細說明可參考Create Partition Function。

USE PartitionA 
CREATE PARTITION FUNCTION PartitionTest(INT) AS RANGE RIGHT FOR 
VALUES(90, 95);
GO

6. 建立Partition Scheme，根據Partition Function的值放到對應的檔案群組， 詳細說明可參考Create Partition Scheme。

USE PartitionA 
CREATE PARTITION SCHEME PartitionTest AS PARTITION PartitionTest TO 
(
 [PartitionTestTo90],
 [PartitionTestTo95],
 [PartitionTestTo100]
)
GO

7. 建立一個檢查用的Function，此方法是可以依據切割的設定查看資料是否寫在正確的位置上。

CREATE FUNCTION PartitionTestInfo( @tablename sysname ) returns table as return
SELECT OBJECT_NAME(p.object_id) as TableName,
p.partition_number as PartitionNumber,prv_left.value as LowerBoundary,
prv_right.value as  UpperBoundary,ps.name as PartitionScheme,
pf.name as PartitionFunction,fg.name as FileGroupName,
CAST(p.used_page_count * 8.0 / 1024 AS NUMERIC(18,2)) AS UsedPages_MB
FROM  sys.dm_db_partition_stats p
INNER JOIN sys.indexes i ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN sys.partition_schemes ps ON ps.data_space_id = i.data_space_id
INNER JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
INNER JOIN sys.destination_data_spaces dds ON 
    dds.partition_scheme_id = ps.data_space_id AND 
    dds.destination_id = p.partition_number
INNER JOIN sys.filegroups fg ON fg.data_space_id = dds.data_space_id
LEFT  JOIN sys.partition_range_values prv_right ON 
    prv_right.function_id = ps.function_id AND 
    prv_right.boundary_id = p.partition_number
LEFT  join sys.partition_range_values prv_left  ON 
    prv_left.function_id = ps.function_id AND 
    prv_left.boundary_id = p.partition_number - 1
WHERE p.object_id = OBJECT_ID(@tablename) AND p.index_id < 2
GO

8. 建立一個測試Table連結到Partition，將Table建立在PartitionTest的Partition Scheme上。

CREATE TABLE [dbo].[test](
 [year] [int] NOT NULL,
 [student] [nchar](6) NOT NULL,
 [score] [decimal](7, 4) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
 (
  [year] ASC,
  [student] ASC
 )) ON PartitionTest([year])
GO

9. 新增一些測試資料，看是否有正確寫在對的地方，利用PartitionTestInfo查看狀況。

SELECT * FROM PartitionAF71Info('dbo.test')

10. 日後要新增新的Partition，一樣要增加FileGroup、實體檔案位置，調整Partition Function及Partition Scheme的區間

--建立出新的Partition
DECLARE @PATH NVARCHAR(128);
DECLARE @SQL VARCHAR(1000);
--
SELECT @PATH=physical_name FROM sys.database_files WHERE file_id = 1
--
--產生下一個5年的群組
ALTER DATABASE [PartitionA ] ADD FILEGROUP [PartitionTo105];
--產生下一個5年的實體資料檔案
SET @SQL = 'ALTER DATABASE [PartitionA ] ADD FILE ( NAME=[PartitionTo105], FILENAME="' + @PATH + 'PartitionTo105.ndf", SIZE=100MB ) TO FILEGROUP[PartitionTo105]';
EXEC(@SQL)
--變更Partition Scheme 指定新的資料群組
ALTER PARTITION SCHEME [PartitionTest] NEXT USED [PartitionTo105]
--變更Partition Function 指定新的Range
ALTER PARTITION FUNCTION [PartitionTest]() SPLIT RANGE (100)

11. 移轉資料，讓資料群組可刪除，建立一個Table跟原本的一樣，利用同樣的倘案群組，假設要移轉第一個區段。

CREATE TABLE [dbo].[MOCK_test](
 [year] [int] NOT NULL,
 [student] [nchar](6) NOT NULL,
 [score] [decimal](7, 4) NULL,
CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED 
 (
  [year] ASC,
  [student] ASC
 )) ON PartitionTo90([year])
GO

12. 資料群組搬移出來後，要刪除資料群組，必須先刪除資料檔案才可刪除檔案群組。

ALTER PARTITION FUNCTION PartitionTest() MERGE RANGE (90)
SELECT * FROM PartitionAF71Info('dbo.test')
USE [master]
GO
ALTER DATABASE [PartitionA ] REMOVE FILE [PartitionTo90]
ALTER DATABASE [PartitionA ] REMOVE FILEGROUP [PartitionTo90]

參考資料 Partition Table 的實作筆記