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

SELECT * INTO archivePeople
FROM oldPeople

-- alter the default filegroup back again!
ALTER DATABASE People MODIFY FILEGROUP [primary] DEFAULT
GO

-- you can now drop the oldPeople table
DROP TABLE oldPeople

Finally, look at the system metadata to see where your new table is living. The following is the query that
can be used for this, with abreviated results showing just the objects of interest:

-- Lets check which filegroup all our objects are on
SELECT OBJECT_NAME(i.object_id) AS ObjectName, i.name AS IndexName, f.name as
filegroupName
FROM sys.indexes AS i INNER JOIN sys.filegroups AS f ON i.data_space_id = f.data_
space_id
WHERE i.object_id > 100

-- Returns
ObjectName IndexName filegroupName

---------- --------- -------------

boysnames NULL PRIMARY

girlsnames NULL PRIMARY

lastnames NULL PRIMARY

newPeople cix_newPeople PRIMARY

archivePeople NULL cheapDisks

You can see that archivePeople is now in the cheapDisks file group.

PartitioningaProductionSystem
The scenarios covered so far help to show how you can implement a sliding window scenario but they
gloss over some of the finer details that are going to be the key points on a production system. One of
the main issues on a production system is going to be that you will have multiple file groups matched to
different physical storage.

The only place this really changes any of what you've seen so far is when you're creating and altering the
partition scheme. So rather than the example you saw earlier, you would create a partition scheme using
something like this:

CREATE PARTITION SCHEME [PeoplePS1]
AS PARTITION [PeopleRangePF1]
TO ([FileGroup1], [FileGroup2], [FileGroup3] );
GO

Each of your partitions go to a different file group.

When you create file groups, you need to make sure you have enough not just for the live table, but
also for new data and for the old data before it's moved to archiving storage or deleted, so you need file
groups for at least the number of live partitions, plus two more; one for the new data and one for the
old data.