CREATE DATABASE PartitionDB ON PRIMARY (NAME = 'PartitionDB_Pri', FILENAME = 'D:\Data\PartitionDB_Pri.mdf', SIZE = 100MB, FILEGROWTH = 50MB), FILEGROUP FG2020 (NAME = 'FG2020', FILENAME = 'E:\Data\FG2020.ndf', SIZE = 100MB, FILEGROWTH = 50MB), FILEGROUP FG2021 (NAME = 'FG2021', FILENAME = 'F:\Data\FG2021.ndf', SIZE = 100MB, FILEGROWTH = 50MB), FILEGROUP FG2022 (NAME = 'FG2022', FILENAME = 'G:\Data\FG2022.ndf', SIZE = 100MB, FILEGROWTH = 50MB), FILEGROUP FG2023 (NAME = 'FG2023', FILENAME = 'H:\Data\FG2023.ndf', SIZE = 100MB, FILEGROWTH = 50MB) LOG ON (NAME = 'PartitionDB_Log', FILENAME = 'L:\Log\PartitionDB_Log.ldf', SIZE = 50MB, FILEGROWTH = 20MB);
重點:每個檔案群組對應獨立物理磁碟(D/E/F/G/H),最大化 I/O 平行度。
-- 以日期為分區鍵,RANGE RIGHT 表示界限值屬於右側分區
CREATE PARTITION FUNCTION PF_Yearly (DATETIME)
AS RANGE RIGHT FOR VALUES ('2020-01-01', '2021-01-01', '2022-01-01', '2023-01-01');
RANGE LEFT 則界限值屬左側分區。CREATE PARTITION SCHEME PS_Yearly AS PARTITION PF_Yearly TO (PRIMARY, FG2020, FG2021, FG2022, FG2023, FG2023); -- 最後一個分區同放 FG2023 或另建 FG_Future
CREATE TABLE dbo.PartitionedLog (
LogID BIGINT IDENTITY(1,1) NOT NULL,
LogTime DATETIME NOT NULL, -- 分區欄位
Message NVARCHAR(MAX) NOT NULL,
CONSTRAINT PK_PartitionedLog PRIMARY KEY NONCLUSTERED (LogID) -- PK 不能在分區欄上除非包含分區欄
)
ON PS_Yearly(LogTime); -- 指定分區配置與分區欄位
SELECT
p.partition_number,
f.name AS filegroup_name,
p.rows,
prv.value AS boundary_value
FROM sys.partitions p
JOIN sys.indexes i ON p.[object_id] = i.[object_id] AND p.index_id = i.index_id
JOIN sys.data_spaces ds ON i.data_space_id = ds.data_space_id
JOIN sys.filegroups f ON ds.data_space_id = f.data_space_id
LEFT JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
LEFT JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
LEFT JOIN sys.partition_range_values prv ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id
WHERE OBJECT_NAME(p.[object_id]) = 'PartitionedLog'
ORDER BY p.partition_number;
ALTER PARTITION FUNCTION PF_Yearly() SPLIT RANGE ('2024-01-01');ALTER PARTITION FUNCTION PF_Yearly() MERGE RANGE ('2020-01-01');ALTER TABLE PartitionedLog SWITCH PARTITION 1 TO ArchiveLog PARTITION 1;TRUNCATE TABLE ... WITH (PARTITIONS (1,2))。DATA_COMPRESSION = PAGE。