Database Reference
In-Depth Information
create nonclustered index IDX_tblOrdersStage_CustomerId
on dbo.tblOrdersStage(CustomerId)
with (drop_existing=on, online=on)
on [FG1];
As the final step, you need to move the
tblOrders
table May data partition to the
FG1
filegroup by merging and
splitting the partition function. The partition is empty and a schema modification (SCH-M) lock will not be held for a
long time. After that, you can switch the staging table back as a partition to the
tblOrders
table, drop the trigger, and
alter the view again. The code for doing this is shown in Listing 15-23.
Listing 15-23.
Using a temporary table to move partition data: Moving the staging table
alter partition function pfOrders()
merge range ('2014-05-01');
alter partition scheme psOrders
next used [FG1];
alter partition function pfOrders()
split range ('2014-05-01');
alter table dbo.tblOrdersStage
switch to dbo.tblOrders partition 5;
drop trigger dbo.trgOrdersView_Ins;
alter view dbo.Orders(OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/)
with schemabinding
as
select OrderId, OrderDate, OrderNum
,OrderTotal, CustomerId /*Other Columns*/
from dbo.tblOrders;
The same technique would work if you need to archive data into another table. You can switch the staging table
as a partition there as long as the table schemas and indexes are the same.
Moving Data Files Between Disk Arrays
As you can see, there are plenty of limitations that can prevent online cross-filegroup data movement, even in the
Enterprise Edition of SQL Server. It is simply impossible to do this in the non-Enterprise editions, which do not
support online index rebuild at all.
Fortunately, there is still a workaround that allows you to build tiered storage, regardless of those limitations. You
can keep the objects in the same filegroups by moving the filegroup database files to different disk arrays.
There are two ways to implement this. You can manually copy the data files and alter the database to specify their
new location. Unfortunately, that approach requires system downtime for the duration of the file copy operation,
which can take a long time with large amounts of data.
If downtime is not acceptable, you can move the data online by adding new files to the filegroup and shrinking
the original files with the
DBCC SHRINK(EMPTYFILE)
command. SQL Server moves the data between files transparently
to the client applications, keeping the system online, no matter the edition of SQL Server.
