Sql Server 2005資料庫分割槽

iSQlServer發表於2010-02-01
SQLServer資料庫
  1 -- 進行演示操作前, 先備份, 以便可以在演示完成後, 恢復到原始狀態
  2 USE master
  3 -- 備份
  4 BACKUP DATABASE AdventureWorks
  5     TO DISK = 'AdventureWorks.bak'
  6     WITH FORMAT
  7 
  8 ---- 恢復
  9 --RESTORE DATABASE AdventureWorks
 10 --    FROM DISK = 'AdventureWorks.bak'
 11 --    WITH REPLACE
 12 GO
 13 
 14 --=========================================
 15 -- 轉換為分割槽表
 16 --=========================================
 17 USE AdventureWorks
 18 GO
 19 
 20 -- 1. 建立分割槽函式
 21 --    a. 適用於儲存歷史存檔記錄的分割槽表的分割槽函式
 22 DECLARE @dt datetime
 23 SET @dt = '20020101'
 24 CREATE PARTITION FUNCTION PF_HistoryArchive(datetime)
 25 AS RANGE RIGHT
 26 FOR VALUES(
 27     @dt, 
 28     DATEADD(Year, 1, @dt))
 29 
 30 --    b. 適用於儲存歷史記錄的分割槽表的分割槽函式
 31 --DECLARE @dt datetime
 32 SET @dt = '20030901'
 33 CREATE PARTITION FUNCTION PF_History(datetime)
 34 AS RANGE RIGHT
 35 FOR VALUES(
 36     @dt, 
 37     DATEADD(Month, 1, @dt), DATEADD(Month, 2, @dt), DATEADD(Month, 3, @dt),
 38     DATEADD(Month, 4, @dt), DATEADD(Month, 5, @dt), DATEADD(Month, 6, @dt),
 39     DATEADD(Month, 7, @dt), DATEADD(Month, 8, @dt), DATEADD(Month, 9, @dt),
 40     DATEADD(Month, 10, @dt), DATEADD(Month, 11, @dt), DATEADD(Month, 12, @dt))
 41 GO
 42 
 43 -- 2. 建立分割槽架構
 44 --    a. 適用於儲存歷史存檔記錄的分割槽表的分割槽架構
 45 CREATE PARTITION SCHEME PS_HistoryArchive
 46 AS PARTITION PF_HistoryArchive
 47 TO([PRIMARY], [PRIMARY], [PRIMARY])
 48 
 49 --    b. 適用於儲存歷史記錄的分割槽表的分割槽架構
 50 CREATE PARTITION SCHEME PS_History
 51 AS PARTITION PF_History
 52 TO([PRIMARY], [PRIMARY], 
 53     [PRIMARY], [PRIMARY], [PRIMARY],
 54     [PRIMARY], [PRIMARY], [PRIMARY],
 55     [PRIMARY], [PRIMARY], [PRIMARY],
 56     [PRIMARY], [PRIMARY], [PRIMARY])
 57 GO
 58 
 59 -- 3. 刪除索引
 60 --    a. 刪除儲存歷史存檔記錄的表中的索引
 61 DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ProductID
 62 DROP INDEX Production.TransactionHistoryArchive.IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
 63 
 64 --    b. 刪除儲存歷史記錄的表中的索引
 65 DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ProductID
 66 DROP INDEX Production.TransactionHistory.IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
 67 GO
 68 
 69 -- 4. 轉換為分割槽表
 70 --    a. 將儲存歷史存檔記錄的錶轉換為分割槽表
 71 ALTER TABLE Production.TransactionHistoryArchive
 72     DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID
 73     WITH(
 74         MOVE TO PS_HistoryArchive(TransactionDate))
 75 
 76 --    b.將儲存歷史記錄的錶轉換為分割槽表
 77 ALTER TABLE Production.TransactionHistory
 78     DROP CONSTRAINT PK_TransactionHistory_TransactionID
 79     WITH(
 80         MOVE TO PS_History(TransactionDate))
 81 GO
 82 
 83 -- 5. 恢復主鍵
 84 --    a. 恢復儲存歷史存檔記錄的分割槽表的主鍵
 85 ALTER TABLE Production.TransactionHistoryArchive
 86     ADD CONSTRAINT PK_TransactionHistoryArchive_TransactionID
 87         PRIMARY KEY CLUSTERED(
 88             TransactionID,
 89             TransactionDate)
 90 
 91 --    b. 恢復儲存歷史記錄的分割槽表的主鍵
 92 ALTER TABLE Production.TransactionHistory
 93     ADD CONSTRAINT PK_TransactionHistory_TransactionID
 94         PRIMARY KEY CLUSTERED(
 95             TransactionID,
 96             TransactionDate)
 97 GO
 98 
 99 -- 6. 恢復索引
100 --    a. 恢復儲存歷史存檔記錄的分割槽表的索引
101 CREATE INDEX IX_TransactionHistoryArchive_ProductID 
102     ON Production.TransactionHistoryArchive(
103         ProductID)
104 
105 CREATE INDEX IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID
106     ON Production.TransactionHistoryArchive(
107         ReferenceOrderID,
108         ReferenceOrderLineID)
109 
110 --    b. 恢復儲存歷史記錄的分割槽表的索引
111 CREATE INDEX IX_TransactionHistory_ProductID 
112     ON Production.TransactionHistory(
113         ProductID)
114 
115 CREATE INDEX IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID
116     ON Production.TransactionHistory(
117         ReferenceOrderID,
118         ReferenceOrderLineID)
119 GO
120 
121 -- 7. 檢視分割槽表的相關資訊
122 SELECT
123     SchemaName = S.name,
124     TableName = TB.name,
125     PartitionScheme = PS.name,
126     PartitionFunction = PF.name,
127     PartitionFunctionRangeType = CASE
128             WHEN boundary_value_on_right = 0 THEN 'LEFT'
129             ELSE 'RIGHT' END,
130     PartitionFunctionFanout = PF.fanout,
131     SchemaID = S.schema_id,
132     ObjectID = TB.object_id,
133     PartitionSchemeID = PS.data_space_id,
134     PartitionFunctionID = PS.function_id
135 FROM sys.schemas S
136     INNER JOIN sys.tables TB
137         ON S.schema_id = TB.schema_id
138     INNER JOIN sys.indexes IDX
139         on TB.object_id = IDX.object_id
140             AND IDX.index_id < 2
141     INNER JOIN sys.partition_schemes PS
142         ON PS.data_space_id = IDX.data_space_id
143     INNER JOIN sys.partition_functions PF
144         ON PS.function_id = PF.function_id
145 GO
146 
147 --=========================================
148 -- 移動分割槽表資料
149 --=========================================
150 -- 1. 為儲存歷史存檔記錄的分割槽表增加分割槽, 並接受從歷史記錄分割槽表移動過來的資料
151 --    a. 修改分割槽架構, 增加用以接受新分割槽的檔案組
152 ALTER PARTITION SCHEME PS_HistoryArchive
153 NEXT USED [PRIMARY]
154 
155 --    b. 修改分割槽函式, 增加分割槽用以接受從歷史記錄分割槽表移動過來的資料
156 DECLARE @dt datetime
157 SET @dt = '20030901'
158 ALTER PARTITION FUNCTION PF_HistoryArchive()
159 SPLIT RANGE(@dt)
160 
161 --    c. 將歷史記錄表中的過期資料移動到歷史存檔記錄表中
162 ALTER TABLE Production.TransactionHistory
163     SWITCH PARTITION 2
164         TO Production.TransactionHistoryArchive PARTITION $PARTITION.PF_HistoryArchive(@dt)
165 
166 --    d. 將接受到的資料與原來的分割槽合併
167 ALTER PARTITION FUNCTION PF_HistoryArchive()
168 MERGE RANGE(@dt)
169 GO
170 
171 -- 2. 將儲存歷史記錄的分割槽表中不包含資料的分割槽刪除, 並增加新的分割槽以接受新資料
172 --    a. 合併不包含資料的分割槽
173 DECLARE @dt datetime
174 SET @dt = '20030901'
175 ALTER PARTITION FUNCTION PF_History()
176 MERGE RANGE(@dt)
177 
178 --    b.  修改分割槽架構, 增加用以接受新分割槽的檔案組
179 ALTER PARTITION SCHEME PS_History
180 NEXT USED [PRIMARY]
181 
182 --    c. 修改分割槽函式, 增加分割槽用以接受新資料
183 SET @dt = '20041001'
184 ALTER PARTITION FUNCTION PF_History()
185 SPLIT RANGE(@dt)
186 GO
187 
188 
189 --=========================================
190 -- 清除歷史存檔記錄中的過期資料
191 --=========================================
192 -- 1. 建立用於儲存過期的歷史存檔資料的表
193 CREATE TABLE Production.TransactionHistoryArchive_2001_temp(
194     TransactionID int NOT NULL,
195     ProductID int NOT NULL,
196     ReferenceOrderID int NOT NULL,
197     ReferenceOrderLineID int NOT NULL
198         DEFAULT ((0)),
199     TransactionDate datetime NOT NULL
200         DEFAULT (GETDATE()),
201     TransactionType nchar(1) NOT NULL,
202     Quantity int NOT NULL,
203     ActualCost money NOT NULL,
204     ModifiedDate datetime NOT NULL
205         DEFAULT (GETDATE()),
206     CONSTRAINT PK_TransactionHistoryArchive_2001_temp_TransactionID
207         PRIMARY KEY CLUSTERED(
208             TransactionID,
209             TransactionDate)
210 )
211 
212 -- 2. 將資料從歷史存檔記錄分割槽表移動到第1步建立的表中
213 ALTER TABLE Production.TransactionHistoryArchive
214     SWITCH PARTITION 1
215         TO Production.TransactionHistoryArchive_2001_temp
216 
217 -- 3. 刪除不再包含資料的分割槽
218 DECLARE @dt datetime
219 SET @dt = '20020101'
220 ALTER PARTITION FUNCTION PF_HistoryArchive()
221 MERGE RANGE(@dt)
222 
223 -- 4. 修改分割槽架構, 增加用以接受新分割槽的檔案組
224 ALTER PARTITION SCHEME PS_HistoryArchive
225 NEXT USED [PRIMARY]
226 
227 -- 5. 修改分割槽函式, 增加分割槽用以接受新資料
228 SET @dt = '20040101'
229 ALTER PARTITION FUNCTION PF_HistoryArchive()
230 SPLIT RANGE(@dt)
231 
232 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/16436858/viewspace-626549/，如需轉載，請註明出處，否則將追究法律責任。

相關文章