Tuning Indexes - Professional SQL Server 2005 Performance Tuning - page 405

/*
Partition Number total
---------------- -----------
Partition Number total
---------------- -----------
1 18381

2 18245

3 18139

4 17970

5 18106

6 17934

7 18360

8 18153

9 18297

10 18168

11 9051

12 1852

13 1771

14 99998

*/
-- Now we only have partitions 1-14 again

That was all quite straightforward but you aren't quite done yet. You have moved the old partition data
out of the partition table but it's still on the same file group, consuming your most expensive storage.
You want the data to be on the cheaper storage for archiving or in some location ready to be backed up
and deleted.

To do this, you need to physically move the data from one file group to another. There are several ways
you can do this but the fastest and most efficient is to use a select into . There are three steps you need
to complete here.

1.

Alter the default file group to be the archiving file group.

2.

Select into a new table.

3.

Alter the default file group back.

Here is the code you can use to do this:

-- Once its slid out, we have to physically move the data
-- to a different filegroup for archiving storage
-- Change the default filegroup to be cheapDisks
-- This is so that the SELECT INTO creates the new table on cheapDisks
ALTER DATABASE people ADD FILEGROUP cheapDisks
GO
ALTER DATABASE people ADD FILE (NAME = cheapdisk1, FILENAME = 'D: \ cheapdisk.ndf')
TO FILEGROUP cheapDisks
GO
ALTER DATABASE people MODIFY FILEGROUP cheapDisks DEFAULT
GO

-- Move the data!