因成績資料的Table過於龐大，效能開始低落，想實作Partition Table看效能是否可改善。
只有在SQL SERVER Enterprise、Developer 和 Evaluation 版本以上才有支援Partition Table。
建立邏輯檔案群組及實體檔案位置。
USE PartitionA
--建立FileGroup
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo90];
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo95];
ALTER DATABASE [PartitionA] ADD FILEGROUP [PartitionTo100];
建立實體檔案位置。
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)
以年度的範圍建立Partition Function，在這建立90年以前、91~95、96~，另外也可使用日期來做區隔，
詳細說明可參考Create Partition Function。
USE PartitionA
CREATE PARTITION FUNCTION PartitionTest(INT) AS RANGE RIGHT FOR
VALUES(90, 95);
GO
建立Partition Scheme，根據Partition Function的值放到對應的檔案群組，
詳細說明可參考Create Partition Scheme。
USE PartitionA
CREATE PARTITION SCHEME PartitionTest AS PARTITION PartitionTest TO
(
[PartitionTestTo90],
[PartitionTestTo95],
[PartitionTestTo100]
)
GO
建立一個檢查用的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
建立一個測試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
新增一些測試資料，看是否有正確寫在對的地方，利用PartitionTestInfo查看狀況。
SELECT * FROM PartitionAF71Info('dbo.test')
日後要新增新的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)
移轉資料，讓資料群組可刪除，建立一個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
資料群組搬移出來後，要刪除資料群組，必須先刪除資料檔案才可刪除檔案群組。
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 的實作筆記
