SQL修改表分区方案

1、SQL修改表分区万案（局效）背景：由于数据的日益庞大，对数据做了表分区并按时间段来划分（按年划分） ，现需要增加一个分区文件，需要对之前的分区方案和分区函数进行修改。前提：数据表之前已经做了分区方案，下面只是修改分区方案， 如果是为数据表新建分区方案的话，必须得删除索引才可以操作（如：主键、全文索引等）数据库版本：SQL2008方法一：可视化操作+SQL语句（快速、简单、效率高）步骤：第一步：添加文件组 （注意：修改时最好只添加一个文件组）产西孔中和爆青皿皿小卜=1在口|-IBIAI3tP）*二。IHI 立除Ji茸士可|!Tt - IQiStll了寸强泞 ，十山 :n?工牲 “知 -*若口殳总

2、frHE而，己博1与 wk2ag 下仆2n产 VjjfllS 事团及 J.室Mt门第二步：添加文件（用了存储分区表数据）（注意：最好一次只添加一个分区文件）第三步：修改分局方案ALTER PARTITION SCHEME PS_HistoryArchive（当前需修改的分区方案名称）NEXTUSED qmzb2018（文件组名称）第四步：修改分区函数ALTER PARTITION FUNCTION PF_HistoryArchive（）（分区函数名称）SPLIT RANGE（N2018-01-01T00:00:00.000）（分区边界如：当前将 2018年以后的数据放在新分区文件中）方法二：直

3、接采用SQL句操作 HYPERLINK /ccyyss/article/details/32711579 /ccyyss/article/details/32711579-进行演示操作前，先备份，以便可以在演示完成后，恢复到原始 状态USE master-备份BACKUP DATABASEAdventureWorksTO DISK = AdventureWorks.bakWITH FORMAT 恢复-RESTORE DATABASE AdventureWorks- FROM DISK = AdventureWorks.bak- WITH REPLACEGO-=- 转换为分区表-=USE Adv

4、entureWorksGO- 1.创建分区函数- a.适用于存储历史存档记录的分区表的分区函数DECLAREdt datetimeSET dt = 20020101,CREATEPARTITION FUNCTION PF_HistoryArchive(datetime)AS RANGE RIGHTFOR VALUERdt,DATEADD( Year , 1, dt)- b.适用于存储历史记录的分区表的分区函数-DECLARE dt datetimeSET dt = 20030901,CREATEPARTITION FUNCTION PF_History(datetime)AS RANGE RI

5、GHT35. FOR VALUER36.dt,37.DATEADD( Month , 1, dt), DATEADD(38.DATEADD( Month , 4, dt), DATEADD(39.DATEADD( Month , 7, dt), DATEADD(40.DATEADD( Month , 10, dt), DATEADD(41. GOMonth , 2, dt), DATEADD(Month , 3, dt),Month , 5, dt), DATEADD(Month , 6, dt),Month , 8, dt), DATEADD(Month , 9, dt),Month , 1

6、1, dt), DATEADD(Month , 12, dt)42.- 2. 创建分区架构- a.适用于存储历史存档记录的分区表的分区架构CREATEPARTITION SCHEME PS_HistoryArchiveAS PARTITION PF_HistoryArchiveTO( PRIMARYPRIMARY, PRIMARY)- b.适用于存储历史记录的分区表的分区架构CREATEPARTITION SCHEME PS_HistoryAS PARTITION PF_HistoryPRIMARY, PRIMARY, PRIMARY, PRIMARY)TO( PRIMARY, PRIMARY

7、,PRIMARY PRIMARYPRIMARY PRIMARYPRIMARY PRIMARYPRIMARY PRIMARYGO- 3. 删除索引- a.删除存储历史存档记录的表中的索引DROPINDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_Produ ctID. DROPINDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_Refer enceOrderID_ReferenceOrderLineID- b.删除

8、存储历史记录的表中的索引DROPINDEX Production.TransactionHistory.IX_TransactionHistory_ProductIDDROPINDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_Re ferenceOrderLineIDGO- 4. 转换为分区表- a.将存储历史存档记录的表转换为分区表ALTER TABLE Production.TransactionHistoryArchiveDROPCONSTRAINTPK_TransactionHistory

9、Archive_TransactionIDWITH(MOVETO PS_HistoryArchive(TransactionDate)- b. 将存储历史记录的表转换为分区表ALTER TABLE Production.TransactionHistoryDROPCONSTRAINTPK_TransactionHistory_TransactionIDWITH(MOVETO PS_History(TransactionDate)GO- 5.恢复主键- a.恢复存储历史存档记录的分区表的主键ALTER TABLE Production.TransactionHistoryArchiveADD C

10、ONSTRAINTPK_TransactionHistoryArchive_TransactionIDPRIMARY KEYCLUSTERED(TransactionID,TransactionDate)- b.恢复存储历史记录的分区表的主键ALTER TABLE Production.TransactionHistoryADD CONSTRAINTPK_TransactionHistory_TransactionIDPRIMARY KEYCLUSTERED(TransactionID,TransactionDate)GO- 6.恢复索引- a.恢复存储历史存档记录的分区表的索引CREATE

11、INDEX IX_TransactionHistoryArchive_ProductIDONProduction.TransactionHistoryArchive(ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID ONProduction.TransactionHistoryArchive( ReferenceOrderID, ReferenceOrderLineID)-b.恢复存储历史记录的分区表的索引CREATE INDEX IX_TransactionHis

12、tory_ProductIDONProduction.TransactionHistory(ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineIDONProduction.TransactionHistory(ReferenceOrderID,ReferenceOrderLineID)GO-7.查看分区表的相关信息SELECTSchemaName = S.name,TableName = TB.name,PartitionScheme = PS.name,PartitionFuncti

13、on = PF.name,PartitionFunctionRangeType =CASEWHENboundary_value_on_right = 0THEN LEFTELSE RIGHT ENDPartitionFunctionFanout = PF.fanout,SchemaID = S.schema_id,ObjectID = TB.object_id,PartitionSchemeID = PS.data_space_id,PartitionFunctionID = PS.function_idFROMsys.schemas SINNER JOIN sys.tables TBONS.

14、schema_id = TB.schema_idINNER JOIN sys.indexes IDXon TB.object_id = IDX.object_idAND IDX.index_id 2INNER JOIN sys.partition_schemes PSONPS.data_space_id = IDX.data_space_idINNER JOIN sys.partition_functions PFONPS.function_id = PF.function_id149.-移动分区表数据GO- 1.为存储历史存档记录的分区表增加分区，并接受从历史记录分区表移动过来的数据- a.

15、 修改分区架构，增加用以接受新分区的文件组ALTER PARTITION SCHEME PS_HistoryArchiveNEXTUSED PRIMARY- b. 修改分区函数，增加分区用以接受从历史记录分区表移动过来的数据DECLAREdt datetimeSET dt = 20030901ALTER PARTITION FUNCTION PF_HistoryArchive()SPLIT RANGE(dt)- c.将历史记录表中的过期数据移动到历史存档记录表中ALTER TABLE Production.TransactionHistorySWITCH PARTITION 2TO Produ

16、ction.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(dt)- d. 将接受到的数据与原来的分区合并ALTER PARTITION FUNCTION PF_HistoryArchive()MERGE RANGE(dt)GO- 2.将存储历史记录的分区表中不包含数据的分区删除，并增加新的分区以接受新数据- a.合并不包含数据的分区DECLAREdt datetimeSET dt = 20030901ALTER PARTITION FUNCTION PF_History()MERGE RANGE(dt)- b.

17、 修改分区架构，增加用以接受新分区的文件组ALTER PARTITION SCHEME PS_HistoryNEXTUSED PRIMARY- c. 修改分区函数，增加分区用以接受新数据SET dt = 20041001ALTER PARTITION FUNCTION PF_History()SPLIT RANGE(dt)GO-=-清除历史存档记录中的过期数据-=- 1.创建用于保存过期的历史存档数据的表CREATE TABLE Production.TransactionHistoryArchive_2001_temp(TransactionlD int NOT NULL,ProductID

18、 int NOT NULL,ReferenceOrderID int NOT NULL,ReferenceOrderLineID int NOT NULLDEFAULT(0),TransactionDate datetime NOT NULLDEFAULT (GETDATE(),TransactionType nchar (1) NOT NULL,Quantity int NOT NULL,ActualCost money NOT NULL,ModifiedDate datetime NOT NULLDEFAULT (GETDATE(),CONSTRAINTPK_TransactionHistoryArchive_2001_temp_TransactionIDPRIMARY KEY CLUSTERED(TransactionID,TransactionDate)- 2.将数据从历史存档记录分区表移动到第1步创建的表中ALTER TABLE Production.TransactionHistoryArchiveSWITCH PARTITION 1TO Production.TransactionHistoryArchive_2