SqlServer2005数据库分区

最新推荐文章于 2020-06-21 13:37:00 发布

weixin_33859665

最新推荐文章于 2020-06-21 13:37:00 发布

阅读量141

点赞数

文章标签：数据库

本文详细介绍如何在 SQL Server 中创建和管理分区表，包括备份与恢复、转换为分区表的过程、移动分区表数据及清除过期数据的具体步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转载出处：http://blog.youkuaiyun.com/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