5.将新的文件组添加到分区方案中
前面我们查看了分区表当前的状态,为了添加新的分区(从现有分区当中拆分出来),我们还要为新的分区准备一个存放位置。新的分区将存放在2004Q3 文件组中,该文件组我们前面已经创建。执行如下代码将该文件组添加到分区方案中:
ALTER PARTITION SCHEME OrderDatePScheme
NEXT USED [2004Q3]
GO
NEXT USED [2004Q3]
GO
6.更改数据表的约束
分区视图非常依赖约束,而分区数据表对约束的依赖没有这么强。但出于数据完整性的考虑,此实验中我们为数据表添加了约束。现有约束只允许添加2003Q3 到2004Q2之间的数据。为了能够将新数据切换到分区表中,我们首先要更改此约束
(1)执行如下代码,更改分区表的约束:
ALTER TABLE OrdersRange
ADD CONSTRAINT OrdersRangeMax
CHECK ([OrderDate] < '20041001')
go
ALTER TABLE OrdersRange
ADD CONSTRAINT OrdersRangeMin
CHECK ([OrderDate] >= '20031001')
go
ALTER TABLE OrdersRange
DROP CONSTRAINT OrdersRangeYear
go
ADD CONSTRAINT OrdersRangeMax
CHECK ([OrderDate] < '20041001')
go
ALTER TABLE OrdersRange
ADD CONSTRAINT OrdersRangeMin
CHECK ([OrderDate] >= '20031001')
go
ALTER TABLE OrdersRange
DROP CONSTRAINT OrdersRangeYear
go
(2)接下来执行下面的代码,分裂出新的分区:
ALTER PARTITION FUNCTION OrderDateRangePFN()
SPLIT RANGE ('20040701')
GO
SPLIT RANGE ('20040701')
GO
(3)将数据切换到新的分区中:
ALTER TABLE Orders2004Q3
SWITCH TO OrdersRange PARTITION 5
GO
SWITCH TO OrdersRange PARTITION 5
GO
(4)最后验证数据:
SELECT $partition.OrderDateRangePFN(OrderDate)
AS 'Parition Number'
, min(OrderDate) AS 'Min Order Date'
, max(OrderDate) AS 'Max Order Date'
, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1
GO
AS 'Parition Number'
, min(OrderDate) AS 'Min Order Date'
, max(OrderDate) AS 'Max Order Date'
, count(*) AS 'Rows In Partition'
FROM OrdersRange
GROUP BY $partition.OrderDateRangePFN(OrderDate)
ORDER BY 1
GO
(5)关闭Script4 - RollingRangeScenario.sql.