官方文件https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-partition-function-transact-sql?view=sql-server-ver16
Best Practices
Always keep empty partitions at both ends of the partition range. Keep the partitions at both ends to guarantee that the partition split and the partition merge don't incur any data movement. The partition split occurs at the beginning and the partition merge occurs at the end. Avoid splitting or merging populated partitions. Splitting or merging populated partitions can be inefficient. They can be inefficient because the split or merge may cause as much as four times more log generation, and may also cause severe locking.
最佳實踐
始終在分割槽範圍的兩端保留空分割槽。保留兩端的分割槽，以保證分割槽拆分和分割槽合併不會引起任何資料移動。分割槽拆分發生在開頭，分割槽合併發生在結尾。避免拆分或合併填充的分割槽。拆分或合併填充的分割槽可能效率低下。它們可能效率低下，因為拆分或合併可能會導致多達四倍的日誌生成，並且還可能導致嚴重的鎖定。

**結論**
1、	如果分割槽表對應的分割槽中最後一個分割槽還是空分割槽，即最後一個分割槽還沒有填充資料，則可以很快的對這個分割槽表的分割槽函式和分割槽架構進行擴充套件，不需要幾秒就是一個DLL語句的時間ALTER PARTITION SCHEME 和ALTER PARTITION FUNCTION 
2、	如果分割槽表對應的分割槽中最後一個分割槽不是空分割槽，即最後一個分割槽已經填充資料，則直接對這個分割槽表的分割槽函式和分割槽架構進行擴充套件會很麻煩，擴充套件時間取決於這個分割槽架構對應的所有表的總計大小，在本人經歷案例中，分割槽架構12表的總計大小1.2TB，直接擴充套件分割槽函式和分割槽架構耗時16天，其中擴充套件分割槽函式幾乎不耗時，主要耗在擴充套件分割槽架構
3、	如果分割槽表對應的分割槽中最後一個分割槽不是空分割槽，即最後一個分割槽已經填充資料，如果需要擴充套件分割槽，如果歷史不怎麼更改的話，最好的方法是建立新的資料庫檔案組和資料庫檔案和分割槽函式和分割槽方案和新的分割槽表，把原分割槽錶的歷史分割槽資料bcp out\bcp in到新的分割槽表，再選擇一個停機時間視窗，把最後一個分割槽bcp out\bcp in到新的分割槽表，再對原表和新的分割槽名稱互改
4、	如果想要分割槽架構中一個分割槽對應一個檔案組可以按年份增長，則當我們使用CREATE PARTITION SCHEME 建立分割槽架構時，如果後續有需求需要擴充套件分割槽架構，無論怎麼執行ALTER PARTITION SCHEME，分割槽架構對應最後一個檔案組不會變的。所以一定不能用未來的年份檔案組來作為分割槽架構的最後一個檔案組，否則年份擴充套件幾年後，最後一個檔案組原來是未來年份，多年後可能變成以前年份
5、	新增分割槽，只能一個個區來加，不能一條alter partition function語句一次性增加幾個分割槽範圍
6、	如果一個分割槽存放一個檔案，則一般情況下分割槽架構中檔案組的數目比分割槽函式中的範圍數目多1，因為超出分割槽範圍的資料需要分割槽架構中的最後一個檔案組來存放，所以我們擴充套件的時候，需要先擴充套件分割槽架構中檔案組再擴充套件分割槽函式中的範圍
7、	查詢某個分割槽的資料時需要在分割槽函式$PARTITION.FNDate前面加上資料庫名，否則會報錯Invalid object name '$PARTITION.FNDate'
select * from RayHistory.dbo.RAY_FinalEpsFCHistory where** RayHistory**.$PARTITION.FNDate(SnapshotDate)=1

現象：
12張表共用了一個partition schema分割槽方案，12表總計容量大小是1.2TB，分割槽錶的歷史資料不會修改，每天新增資料到分割槽表。這個分割槽方案關聯的分割槽函式是按年分割槽，每年的資料都寫入到一個指定的檔案，這個分割槽函式最後一個指定的分割槽邊界是2020年，這就導致之後2021、2022…年份的資料都存放在一個預設分割槽中，也就是2020年之後年份的資料都存放在一個檔案中，隨著時間的推移該檔案越來越大會引發嚴重的IO問題。

改進需求：
需要擴充套件分割槽，把分割槽函式和分割槽方案從2021擴充套件到2030年，新建資料檔案組和資料庫檔案，按年份從2021開始建立到到2030，每年資料存放到一個資料檔案，隔年資料庫檔案存放到不同磁碟，有下面三個方案，綜合考慮下來還是採用了方案3，建立新的分割槽函式\分割槽方案\資料庫檔案組資料庫檔案，再建立新的空分割槽表(分割槽表名_new)關聯新的分割槽函式\分割槽方案\資料庫檔案組資料庫檔案，新的空分割槽表建立和原來分割槽表一樣的主鍵索引等，在工作日把原來分割槽錶的歷史資料bcp out\bcp in到新表，再在停機時間視窗把在原來分割槽表改名為分割槽表_archive並把分割槽表名_new改名為分割槽表，再把分割槽表_archive當前分割槽資料bcp out\bcp in到分割槽表

四種方案的優劣
![](http://img.blog.itpub.net/blog/2022/09/30/41f422e4f134101b.jpeg?x-oss-process=style/bb)

**方案1**
新增2021到2030年的10個資料庫檔案組和10個資料庫檔案，擴充套件分割槽函式到2030年並擴充套件分割槽架構到2030並將分割槽架構關聯到新建的檔案組

優點：
1、不需要建立新的表
2、12張表一起同時完成
缺點：
1、12張表在維護期間任何一張表都無法讀寫
2、所有表資料總計1.2TB，維護時間視窗太長，需要16天

指令碼：增加資料庫檔案組
ALTER DATABASE [RayHistory] ADD FILEGROUP [FG_2021]
…
ALTER DATABASE [RayHistory] ADD FILEGROUP [FG_2031]

指令碼：增加資料檔案匹配新增的資料庫檔案組
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATA_2021', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATA_2021.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FG_2021]
…
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATA_2030', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATA_2030.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FG_2030]

指令碼：擴充套件分割槽
ALTER PARTITION SCHEME SHDate NEXT USED FG_2021;  --不怎麼耗時
alter PARTITION FUNCTION [FNDate]() SPLIT RANGE (N'2021-12-31T00:00:00.000')--最耗時，耗時10.5天
ALTER PARTITION SCHEME SHDate NEXT USED FG_2022;  --不怎麼耗時
alter PARTITION FUNCTION [FNDate]() SPLIT RANGE (N'2022-12-31T00:00:00.000')--最耗時，耗時5.5天
…
ALTER PARTITION SCHEME SHDate NEXT USED FG_2030;  --不怎麼耗時
alter PARTITION FUNCTION [FNDate]() SPLIT RANGE (N'2030-12-31T00:00:00.000') --不怎麼耗時
備註：正在執行alter partition時，該partition上的所有表都會被鎖定，執行select * from table with(nolock)都會被堵塞，鎖型別是LCK_M_SCH_S，和該partition無關的表不受影響
![](http://img.blog.itpub.net/blog/2022/09/30/9aa2bec9ed6a7679.png?x-oss-process=style/bb)

**方案2**
新建資料庫檔案組和資料庫檔案到2030，建立新的分割槽方案和分割槽函式到2030，並將分割槽架構關聯分割槽函式並將分割槽架構關聯到新建的資料庫檔案，再把原分割槽表線上轉換成非分割槽表，再把這些已經轉換為非分割槽的錶轉為分割槽表，管理新的分割槽方案

優點：
1、不需要建立新的表
2、轉換分割槽表到非分割槽表的過程中可以線上操作，表不會被鎖可以讀寫
3、在操作一張的表時候，其他11張表不受影響
缺點：
1、需要12個停機時間視窗，12表需要一張張表來做
2、轉換非分割槽表到分割槽表的過程中表會被鎖，不可以讀寫
3、所有表資料總計1.2TB，維護時間視窗太長，總計需要25天，比如最大的表從分割槽錶轉換為非分割槽表就需要2天，再從非分割槽錶轉換為分割槽表需要5天

指令碼：新資料庫檔案組
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_old]
…
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_2020]
…
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_2031]

指令碼：新資料檔案
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_old', FILENAME = N'I:\DEFAULT.DATA\RayHistory_FNDATANEW_old.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_old]
…
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_2020', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATANEW_2020.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_2020]
…
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_2030', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATANEW_2030.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_2030]

指令碼：新建分割槽函式
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000', N'2009-12-31T00:00:00.000', N'2010-12-31T00:00:00.000', N'2011-12-31T00:00:00.000', N'2012-12-31T00:00:00.000', N'2013-12-31T00:00:00.000', N'2014-12-31T00:00:00.000', N'2015-12-31T00:00:00.000', N'2016-12-31T00:00:00.000', N'2017-12-31T00:00:00.000', N'2018-12-31T00:00:00.000', N'2019-12-31T00:00:00.000', N'2020-12-31T00:00:00.000', N'2021-12-31T00:00:00.000', N'2022-12-31T00:00:00.000', N'2023-12-31T00:00:00.000', N'2024-12-31T00:00:00.000', N'2025-12-31T00:00:00.000', N'2026-12-31T00:00:00.000', N'2027-12-31T00:00:00.000', N'2028-12-31T00:00:00.000', N'2029-12-31T00:00:00.000', N'2030-12-31T00:00:00.000')
指令碼：新建分割槽架構
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_old], [FGNEW_2008], [FGNEW_2009], [FGNEW_2010], [FGNEW_2011], [FGNEW_2012], [FGNEW_2013], [FGNEW_2014], [FGNEW_2015], [FGNEW_2016], [FGNEW_2017], [FGNEW_2018], [FGNEW_2019], [FGNEW_2020], [FGNEW_2021], [FGNEW_2022], [FGNEW_2023], [FGNEW_2024], [FGNEW_2025], [FGNEW_2026], [FGNEW_2027], [FGNEW_2028], [FGNEW_2029], [FGNEW_2030], [Primary])

指令碼：錶轉換為非分割槽表
select getdate() 'convert RAY_FinalEpsFCHistory to non-partition start'
GO
ALTER TABLE [dbo].[RAY_FinalEpsFCHistory] DROP CONSTRAINT  [PK_RAY_FinalEpsFCHistory] with(online=on)
備註：以上過程不鎖表
GO
select getdate() 'convert RAY_FinalEpsFCHistory to non-partition end'

指令碼：非分割槽錶轉換為分割槽表關聯新的分割槽架構
select getdate() 'convert RAY_FinalEpsFCHistory to partition start'
GO
ALTER TABLE [dbo].[RAY_FinalEpsFCHistory] ADD  CONSTRAINT [PK_RAY_FinalEpsFCHistory1] PRIMARY KEY CLUSTERED 
(
	[msid] ASC,
	[SnapshotDate] DESC,
	[FiscalDate] DESC
)WITH(online=on) ON [SHDate_NEW]([SnapshotDate])
備註：以上過程，開始不鎖表，期間有段時間會鎖表
GO
select getdate() 'convert RAY_FinalEpsFCHistory to partition end'
![](http://img.blog.itpub.net/blog/2022/09/30/d1a10550958415a1.png?x-oss-process=style/bb)

**方案3**
新建資料庫檔案組和資料庫檔案到2030，建立新的分割槽方案和分割槽函式到2030，並將分割槽架構關聯分割槽函式並將分割槽架構關聯到新建的資料庫檔案，建立新的空分割槽表關聯新的分割槽方案，新的空分割槽表建立和分割槽表一樣的索引，新的空分割槽表名稱為分割槽表名_new，複製分割槽表歷史分割槽資料2021-01-01之前的資料到分割槽表名_new，當所有的歷史分割槽資料都複製完成後，請求一個業務停止的時間視窗，在這個時間視窗內再把分割槽表改名為分割槽表_archive，把分割槽表名_new改名為分割槽表名，再把分割槽表_archive當前分割槽資料庫複製到分割槽表名

優點：
1、12張表都不會被鎖
2、可以在平時工作日一點點地把歷史資料(2021年之前的資料)複製到新表
3、在維護視窗時間內，可以正常訪問新錶的歷史資料(2021年之前的資料)
4、停機時間視窗最短，總計大概需要20小時
缺點：1、需要建立12張新表
2、20個小時的停機時間視窗需要分配成12個小的停機時間視窗，一個停機時間視窗操作1張表，因為12張需要一張張表來操作
3、在某張表的停機時間視窗中 ，無法訪問該表的2021-2022年的資料

步驟
1、新建資料庫檔案組和資料庫檔案到2030，建立新的分割槽方案和分割槽函式到2030，並將分割槽架構關聯分割槽函式並將分割槽架構關聯到新建的資料庫檔案
2、建立新的空分割槽表關聯新的分割槽方案，新的空分割槽表名稱為分割槽表名_new
3、新的空分割槽表建立和分割槽表一樣的索引，索引壓縮為data_compresssion
4、複製原來分割槽表歷史分割槽資料2021-01-01之前的資料到新的分割槽表名
5、當所有的歷史分割槽資料都複製完成後，請求一個業務停止的時間視窗，在這個時間視窗內再把原來的分割槽表改名為分割槽表_archive，把新的分割槽改名為原來的分割槽表
6、再把分割槽表_archive當前分割槽資料庫複製到分割槽表

指令碼：新建資料庫檔案組
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_old]
…
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_2020]
…
ALTER DATABASE [RayHistory] ADD FILEGROUP [FGNEW_2031]

指令碼：新建資料庫檔案
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_old', FILENAME = N'I:\DEFAULT.DATA\RayHistory_FNDATANEW_old.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_2007]
…
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_2020', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATANEW_2020.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_2020]
…
ALTER DATABASE [RayHistory] ADD FILE ( NAME = N'FNDATANEW_2031', FILENAME = N'T:\DEFAULT.DATA\RayHistory_FNDATANEW_2031.ndf' , SIZE = 1024000KB , FILEGROWTH = 1024000KB ) TO FILEGROUP [FGNEW_2031]

指令碼：新建分割槽函式
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000', N'2009-12-31T00:00:00.000', N'2010-12-31T00:00:00.000', N'2011-12-31T00:00:00.000', N'2012-12-31T00:00:00.000', N'2013-12-31T00:00:00.000', N'2014-12-31T00:00:00.000', N'2015-12-31T00:00:00.000', N'2016-12-31T00:00:00.000', N'2017-12-31T00:00:00.000', N'2018-12-31T00:00:00.000', N'2019-12-31T00:00:00.000', N'2020-12-31T00:00:00.000', N'2021-12-31T00:00:00.000', N'2022-12-31T00:00:00.000', N'2023-12-31T00:00:00.000', N'2024-12-31T00:00:00.000', N'2025-12-31T00:00:00.000', N'2026-12-31T00:00:00.000', N'2027-12-31T00:00:00.000', N'2028-12-31T00:00:00.000', N'2029-12-31T00:00:00.000', N'2030-12-31T00:00:00.000')
指令碼：新建分割槽架構
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007], [FGNEW_2008], [FGNEW_2009], [FGNEW_2010], [FGNEW_2011], [FGNEW_2012], [FGNEW_2013], [FGNEW_2014], [FGNEW_2015], [FGNEW_2016], [FGNEW_2017], [FGNEW_2018], [FGNEW_2019], [FGNEW_2020], [FGNEW_2021], [FGNEW_2022], [FGNEW_2023], [FGNEW_2024], [FGNEW_2025], [FGNEW_2026], [FGNEW_2027], [FGNEW_2028], [FGNEW_2029], [FGNEW_2030], [FGNEW_2031])

指令碼：bcp out和bcp in歷史資料從原表到新表
bcp out
bcp "select * from RayHistory.dbo.RAY_FinalEpsFCHistory where $PARTITION.FNDate(SnapshotDate)=1" queryout L:\BCP\RAY_FinalEpsFCHistory_p1.txt -n -T -k
報錯Invalid object name '$PARTITION.FNDate'，需要在$PARTITION.FNDate前面加上資料庫名，比如RayHistory.$PARTITION.FNDate(SnapshotDate)

bcp "select * from RayHistory.dbo.RAY_FinalEpsFCHistory where RayHistory.$PARTITION.FNDate(SnapshotDate)=1 and SnapshotDate<='2001-12-31'" queryout R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1.txt -S DBPROD68\MARKETDATA -n -T -k -e R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1_error_in.log>R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1_log.log
bcp "select * from RayHistory.dbo.RAY_FinalEpsFCHistory where RayHistory.$PARTITION.FNDate(SnapshotDate)=1 and SnapshotDate>'2001-12-31'" queryout R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2.txt -S DBPROD68\MARKETDATA -n -T -k -e R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2_error_in.log>R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2_log.log
bcp "select * from RayHistory.dbo.RAY_FinalEpsFCHistory where RayHistory.$PARTITION.FNDate(SnapshotDate)=2" queryout R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p2.txt -S DBPROD68\MARKETDATA -n -T -k -e R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p2_error_in.log>R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p2_log.log
備註：--n,本機格式

bcp in
bcp RayHistory.dbo.RAY_FinalEpsFCHistory_NEW in R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1.txt -S DBPROD68\MARKETDATA -n -T -k -b 10000 -e R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1_error_out.log>>R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_1_log.log
bcp RayHistory.dbo.RAY_FinalEpsFCHistory_NEW in R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2.txt -S DBPROD68\MARKETDATA -n -T -k -b 10000 -e R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2_error_out.log>>R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p1_2_log.log
bcp RayHistory.dbo.RAY_FinalEpsFCHistory_NEW in R:\MARKETDATA.DATA\RAY_FinalEpsFCHistory_p2.txt -S DBPROD68\MARKETDATA -n -T -k -b 10000 -e R:\MARKETDATA.DATA\Bcpin_log\RAY_FinalEpsFCHistory\RAY_FinalEpsFCHistory_p2_error_out.log>>R:\MARKETDATA.DATA\Bcpin_log\RAY_FinalEpsFCHistory\RAY_FinalEpsFCHistory_p2_log.log
備註：-b 10000表示每1萬行就提交一次

指令碼：rename 原表和新表
EXEC sp_rename 'dbo.RAY_FinalEpsFCHistory', 'RAY_FinalEpsFCHistory_archive';
EXEC sp_rename 'dbo.RAY_FinalEpsFCHistory_NEW', 'RAY_FinalEpsFCHistory';

指令碼：bcp out和bcp in當前分割槽資料從原表到新表
bcp "select * from RayHistory.dbo.RAY_FinalEpsHistory_archive where RayHistory.$PARTITION.FNDate(SnapshotDate)=15" queryout R:\MARKETDATA.DATA\RAY_FinalEpsHistory_archive_p15.txt -S DBPROD68\MARKETDATA -n -T -k -e R:\MARKETDATA.DATA\Bcpout_log\RAY_FinalEpsHistory\RAY_FinalEpsHistory_archive_p15_error.log>R:\MARKETDATA.DATA\Bcpout_log\RAY_FinalEpsHistory\RAY_FinalEpsHistory_archive_p15_log.log

bcp RayHistory.dbo.RAY_FinalEpsHistory in R:\MARKETDATA.DATA\RAY_FinalEpsHistory_archive_p15.txt -S DBPROD68\MARKETDATA -n -T -k -b 10000 -e R:\MARKETDATA.DATA\Bcpin_log\RAY_FinalEpsHistory\RAY_FinalEpsHistory_archive_p15_error.log>R:\MARKETDATA.DATA\Bcpin_log\RAY_FinalEpsHistory\RAY_FinalEpsHistory_archive_p15_log.log

方案3的一些實踐經歷
建立分割槽函式和分割槽架構時的語句，分割槽架構中寫死一個分割槽對應一個檔案組
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000', N'2009-12-31T00:00:00.000')
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007], [FGNEW_2008], [FGNEW_2009], [primary])

執行分割槽合併後，即把2009年的資料和以後的資料合併
ALTER PARTITION FUNCTION [FNDate_NEW2022]() MERGE RANGE (N'2009-12-31T00:00:00.000');

分割槽函式合併後，分割槽架構也會自動變化，即2009年對應的分割槽架構檔案組自動變成了分割槽架構建立時最後的檔案組primary
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000')
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007], [FGNEW_2008], [primary])

一個重要的實踐結論1：如果想要分割槽架構中一個分割槽對應一個檔案組可以按年份增長，則當我們使用CREATE PARTITION SCHEME 建立分割槽架構時，如果後續有需求需要擴充套件分割槽架構，無論怎麼執行ALTER PARTITION SCHEME，分割槽架構對應最後一個檔案組不會變的。所以一定不能用未來的年份檔案組來作為分割槽架構的最後一個檔案組，否則年份擴充套件幾年後，最後一個檔案組原來是未來年份，多年後可能變成以前年份，示例如下
建立分割槽函式和分割槽架構，按年來分，先只建立了2年的分割槽，2007到2008年
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000')
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007], [FGNEW_2008], [FGNEW_2009])
2008年底的時候擴充套件到2011年，則寫語句如下
ALTER PARTITION SCHEME [SHDate_NEW] NEXT USED FGNEW_2009
ALTER PARTITION FUNCTION [FNDate_NEW]() SPLIT RANGE (N'2009-12-31T00:00:00.000')
ALTER PARTITION SCHEME [SHDate_NEW] NEXT USED FGNEW_2010
ALTER PARTITION FUNCTION [FNDate_NEW]() SPLIT RANGE (N'2010-12-31T00:00:00.000')
ALTER PARTITION SCHEME [SHDate_NEW] NEXT USED FGNEW_2011
ALTER PARTITION FUNCTION [FNDate_NEW]() SPLIT RANGE (N'2011-12-31T00:00:00.000')
則分割槽函式和分割槽架構的建立指令碼變成了
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000', N'2009-12-31T00:00:00.000', N'2010-12-31T00:00:00.000', N'2011-12-31T00:00:00.000')
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007],[FGNEW_2008], [FGNEW_2009],[FGNEW_2010],[FGNEW_2011],[FGNEW_2009])

一個重要的實踐結論2：新增分割槽，只能一個個區來加，不能一條alter partition function語句一次性增加幾個分割槽範圍

一個重要的實踐結論3：如果一個分割槽存放一個檔案，則一般情況下分割槽架構中檔案組的數目比分割槽函式中的範圍數目多1，因為超出分割槽範圍的資料需要分割槽架構中的最後一個檔案組來存放，所以我們擴充套件的時候，需要先擴充套件分割槽架構中檔案組再擴充套件分割槽函式中的範圍；假如分割槽函式和分割槽架構的建立語句如下
CREATE PARTITION FUNCTION [FNDate_NEW](date) AS RANGE LEFT FOR VALUES (N'2007-12-31T00:00:00.000', N'2008-12-31T00:00:00.000')
CREATE PARTITION SCHEME [SHDate_NEW] AS PARTITION [FNDate_NEW] TO ([FGNEW_2007], [FGNEW_2008], [primary])

新增分割槽到2009年
ALTER PARTITION FUNCTION [FNDate_NEW]() SPLIT RANGE (N'2009-12-31T00:00:00.000')
報錯Warning: The partition scheme 'FNDate_NEW' does not have any next used filegroup. Partition scheme has not been changed.
改成如下即可
ALTER PARTITION SCHEME SHDate NEXT USED FGNEW_2009;  
ALTER PARTITION FUNCTION [FNDate_NEW]() SPLIT RANGE (N'2009-12-31T00:00:00.000')

**方案4**
新建資料庫檔案組和資料庫檔案到2030，建立新的分割槽方案和分割槽函式到2030，並將分割槽架構關聯分割槽函式並將分割槽架構關聯到新建的資料庫檔案，建立新的空分割槽表關聯新的分割槽方案，新的空分割槽表建立和分割槽表一樣的索引，新的空分割槽表名稱為分割槽表名_new

缺點：
1、需要建立12張新表
2、需要修改程式邏輯，在指定的日期部署，以指定日期為分界線，讀寫指定日期前的資料只能去找原表，讀寫指定日期後的資料只能去找新表

SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法

相關文章