Sql Server 2005資料庫分割槽
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
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- sql server 2005資料庫快照SQLServer資料庫
- 如何建立和還原SQL Server 2005資料庫？SQLServer資料庫
- SQL SERVER之分割槽表SQLServer
- SQL SERVER資料庫檔案刪除、分割槽格式化解決方案SQLServer資料庫
- 使用SQL-Server分割槽表功能提高資料庫的讀寫效能SQLServer資料庫
- HGDB的分割槽表實現SQL Server的分割槽檢視SQLServer
- sql server 2005 資料修改的內部原理SQLServer
- SQL Server 表分割槽注意事項HXSQLServer
- 如何將SQL Server 2000備份的資料庫檔案還原（升級）為SQL Server 2005或更高版本的資料庫？SQLServer資料庫
- SQL Server表分割槽刪除詳情DSCCSQLServer
- SQL Server大分割槽表沒有空分割槽的情況下如何擴充套件分割槽的方法SQLServer套件
- SQL Server 2000/2005/2008刪除或壓縮資料庫日誌的方法SQLServer資料庫
- 資料庫映象 (SQL Server)資料庫SQLServer
- SQL Server資料庫安全SQLServer資料庫
- 資料庫系統設計：分割槽資料庫
- 已安裝 SQL Server 2005 Express 工具。若要繼續，請刪除 SQL Server 2005 Express 工具SQLServerExpress
- Blazor使用sql server 資料庫BlazorSQLServer資料庫
- SQL Server資料庫巡檢SQLServer資料庫
- SQL Server收縮資料庫SQLServer資料庫
- sql server 資料庫收縮SQLServer資料庫
- SQL Server資料庫遷移SQLServer資料庫
- 管理SQL Server資料庫安全SQLServer資料庫
- SQL Server資料庫恢復，SQL Server資料恢復，SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- E6 資料庫分割槽技術資料庫
- MySql資料分割槽操作之新增分割槽操作MySql
- Sql Server 資料庫學習-常用資料庫 物件SQLServer資料庫物件
- SQL Server 2005效能調整一(zt)SQLServer
- SQL Server 2005效能調整二(zt)SQLServer
- SQL Server 跨資料庫查詢SQLServer資料庫
- 資料庫映象 (SQL Server)操作模式資料庫SQLServer模式
- zabbix上對mysql資料庫做分割槽表MySql資料庫
- 【資料庫學習】資料庫平臺：mysql，sql server資料庫MySqlServer
- 【資料庫資料恢復】Sql Server資料庫資料恢復案例資料庫資料恢復SQLServer
- SQL Server 資料庫基本記錄（一）SQLServer資料庫
- 資料庫監視器(SQL Server Profilter)資料庫SQLServerFilter
- SQL Server DACPAC資料庫部署錯誤SQLServer資料庫
- SQL Server統計資料庫中表大小SQLServer資料庫
- SQL Server 資料庫基本記錄（二）SQLServer資料庫
- SQL Server 資料庫基本記錄（三）SQLServer資料庫