SQL修改表分區(qū)方案

1、SQL修改表分區(qū)萬(wàn)案（局效）背景：由于數(shù)據(jù)的日益龐大，對(duì)數(shù)據(jù)做了表分區(qū)并按時(shí)間段來(lái)劃分（按年劃分） ，現(xiàn)需要增加一個(gè)分區(qū)文件，需要對(duì)之前的分區(qū)方案和分區(qū)函數(shù)進(jìn)行修改。前提：數(shù)據(jù)表之前已經(jīng)做了分區(qū)方案，下面只是修改分區(qū)方案， 如果是為數(shù)據(jù)表新建分區(qū)方案的話，必須得刪除索引才可以操作（如：主鍵、全文索引等）數(shù)據(jù)庫(kù)版本：SQL2008方法一：可視化操作+SQL語(yǔ)句（快速、簡(jiǎn)單、效率高）步驟：第一步：添加文件組 （注意：修改時(shí)最好只添加一個(gè)文件組）產(chǎn)西孔中和爆青皿皿小卜=1在口|-IBIAI3tP）*二。IHI 立除Ji茸士可|!Tt - IQiStll了寸強(qiáng)濘 ，十山 :n?工牲 “知 -*若口殳總

2、frHE而，己博1與 wk2ag 下仆2n產(chǎn) VjjfllS 事團(tuán)及 J.室Mt門第二步：添加文件（用了存儲(chǔ)分區(qū)表數(shù)據(jù)）（注意：最好一次只添加一個(gè)分區(qū)文件）第三步：修改分局方案ALTER PARTITION SCHEME PS_HistoryArchive（當(dāng)前需修改的分區(qū)方案名稱）NEXTUSED qmzb2018（文件組名稱）第四步：修改分區(qū)函數(shù)ALTER PARTITION FUNCTION PF_HistoryArchive（）（分區(qū)函數(shù)名稱）SPLIT RANGE（N2018-01-01T00:00:00.000）（分區(qū)邊界如：當(dāng)前將 2018年以后的數(shù)據(jù)放在新分區(qū)文件中）方法二：直

3、接采用SQL句操作 HYPERLINK /ccyyss/article/details/32711579 /ccyyss/article/details/32711579-進(jìn)行演示操作前，先備份，以便可以在演示完成后，恢復(fù)到原始 狀態(tài)USE master-備份BACKUP DATABASEAdventureWorksTO DISK = AdventureWorks.bakWITH FORMAT 恢復(fù)-RESTORE DATABASE AdventureWorks- FROM DISK = AdventureWorks.bak- WITH REPLACEGO-=- 轉(zhuǎn)換為分區(qū)表-=USE Adv

4、entureWorksGO- 1.創(chuàng)建分區(qū)函數(shù)- a.適用于存儲(chǔ)歷史存檔記錄的分區(qū)表的分區(qū)函數(shù)DECLAREdt datetimeSET dt = 20020101,CREATEPARTITION FUNCTION PF_HistoryArchive(datetime)AS RANGE RIGHTFOR VALUERdt,DATEADD( Year , 1, dt)- b.適用于存儲(chǔ)歷史記錄的分區(qū)表的分區(qū)函數(shù)-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. 創(chuàng)建分區(qū)架構(gòu)- a.適用于存儲(chǔ)歷史存檔記錄的分區(qū)表的分區(qū)架構(gòu)CREATEPARTITION SCHEME PS_HistoryArchiveAS PARTITION PF_HistoryArchiveTO( PRIMARYPRIMARY, PRIMARY)- b.適用于存儲(chǔ)歷史記錄的分區(qū)表的分區(qū)架構(gòu)CREATEPARTITION SCHEME PS_HistoryAS PARTITION PF_HistoryPRIMARY, PRIMARY, PRIMARY, PRIMARY)TO( PRIMARY, PRIMARY

7、,PRIMARY PRIMARYPRIMARY PRIMARYPRIMARY PRIMARYPRIMARY PRIMARYGO- 3. 刪除索引- a.刪除存儲(chǔ)歷史存檔記錄的表中的索引DROPINDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_Produ ctID. DROPINDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_Refer enceOrderID_ReferenceOrderLineID- b.刪除

8、存儲(chǔ)歷史記錄的表中的索引DROPINDEX Production.TransactionHistory.IX_TransactionHistory_ProductIDDROPINDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_Re ferenceOrderLineIDGO- 4. 轉(zhuǎn)換為分區(qū)表- a.將存儲(chǔ)歷史存檔記錄的表轉(zhuǎn)換為分區(qū)表ALTER TABLE Production.TransactionHistoryArchiveDROPCONSTRAINTPK_TransactionHistory

9、Archive_TransactionIDWITH(MOVETO PS_HistoryArchive(TransactionDate)- b. 將存儲(chǔ)歷史記錄的表轉(zhuǎn)換為分區(qū)表ALTER TABLE Production.TransactionHistoryDROPCONSTRAINTPK_TransactionHistory_TransactionIDWITH(MOVETO PS_History(TransactionDate)GO- 5.恢復(fù)主鍵- a.恢復(fù)存儲(chǔ)歷史存檔記錄的分區(qū)表的主鍵ALTER TABLE Production.TransactionHistoryArchiveADD C

10、ONSTRAINTPK_TransactionHistoryArchive_TransactionIDPRIMARY KEYCLUSTERED(TransactionID,TransactionDate)- b.恢復(fù)存儲(chǔ)歷史記錄的分區(qū)表的主鍵ALTER TABLE Production.TransactionHistoryADD CONSTRAINTPK_TransactionHistory_TransactionIDPRIMARY KEYCLUSTERED(TransactionID,TransactionDate)GO- 6.恢復(fù)索引- a.恢復(fù)存儲(chǔ)歷史存檔記錄的分區(qū)表的索引CREATE

11、INDEX IX_TransactionHistoryArchive_ProductIDONProduction.TransactionHistoryArchive(ProductID)CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID ONProduction.TransactionHistoryArchive( ReferenceOrderID, ReferenceOrderLineID)-b.恢復(fù)存儲(chǔ)歷史記錄的分區(qū)表的索引CREATE INDEX IX_TransactionHis

12、tory_ProductIDONProduction.TransactionHistory(ProductID)CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineIDONProduction.TransactionHistory(ReferenceOrderID,ReferenceOrderLineID)GO-7.查看分區(qū)表的相關(guān)信息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.-移動(dòng)分區(qū)表數(shù)據(jù)GO- 1.為存儲(chǔ)歷史存檔記錄的分區(qū)表增加分區(qū)，并接受從歷史記錄分區(qū)表移動(dòng)過(guò)來(lái)的數(shù)據(jù)- a.

15、 修改分區(qū)架構(gòu)，增加用以接受新分區(qū)的文件組ALTER PARTITION SCHEME PS_HistoryArchiveNEXTUSED PRIMARY- b. 修改分區(qū)函數(shù)，增加分區(qū)用以接受從歷史記錄分區(qū)表移動(dòng)過(guò)來(lái)的數(shù)據(jù)DECLAREdt datetimeSET dt = 20030901ALTER PARTITION FUNCTION PF_HistoryArchive()SPLIT RANGE(dt)- c.將歷史記錄表中的過(guò)期數(shù)據(jù)移動(dòng)到歷史存檔記錄表中ALTER TABLE Production.TransactionHistorySWITCH PARTITION 2TO Produ

16、ction.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(dt)- d. 將接受到的數(shù)據(jù)與原來(lái)的分區(qū)合并ALTER PARTITION FUNCTION PF_HistoryArchive()MERGE RANGE(dt)GO- 2.將存儲(chǔ)歷史記錄的分區(qū)表中不包含數(shù)據(jù)的分區(qū)刪除，并增加新的分區(qū)以接受新數(shù)據(jù)- a.合并不包含數(shù)據(jù)的分區(qū)DECLAREdt datetimeSET dt = 20030901ALTER PARTITION FUNCTION PF_History()MERGE RANGE(dt)- b.

17、 修改分區(qū)架構(gòu)，增加用以接受新分區(qū)的文件組ALTER PARTITION SCHEME PS_HistoryNEXTUSED PRIMARY- c. 修改分區(qū)函數(shù)，增加分區(qū)用以接受新數(shù)據(jù)SET dt = 20041001ALTER PARTITION FUNCTION PF_History()SPLIT RANGE(dt)GO-=-清除歷史存檔記錄中的過(guò)期數(shù)據(jù)-=- 1.創(chuàng)建用于保存過(guò)期的歷史存檔數(shù)據(jù)的表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.將數(shù)據(jù)從歷史存檔記錄分區(qū)表移動(dòng)到第1步創(chuàng)建的表中ALTER TABLE Production.TransactionHistoryArchiveSWITCH PARTITION 1TO Production.TransactionHistoryArchive_2