转载出处：http://blog.csdn.net/doyouknowm/archive/2009/08/20/4466072.aspx
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