SqlServer2005数据库分区

转载出处：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