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

2 18381
3 18245
4 18139
5 17970
6 18106
7 17934
8 18360
9 18153
10 18297
11 18168
12 9051
13 1852
14 1771
15 99998
-- We now have a 15 th

partition with all the new people in it

Querying the partition table, you now have a 15 th partition with 99998 people in it.

DeletingDatafromthePartitionedTable
After a new set of data that that represents a new day, week, or month of data has been added to your
table, you also need to move out the old data by either deleting or archiving it somewhere. The first step
in this process is to create a new table to put the data into. This needs to be an empty table that has the
exact same structure as the partition table and it needs to be on the same file group as the partition you
are going to remove. You'll also need to create the same index structure as before.

-- Step one, we need a table to put the data into
CREATE TABLE oldPeople (
[personID] [uniqueidentifier] NULL DEFAULT (newsequentialid()),
[firstName] [varchar](80) NOT NULL,
[lastName] [varchar](80) NOT NULL,
[DOB] [datetime] NOT NULL,
[DOD] [datetime] NULL,
[sex] [char](1) NOT NULL
) ON [PRIMARY]
-- Must be on the same filegroup as the source partition!

-- we need a clustered index on DOB to match the partition
CREATE CLUSTERED INDEX cix_oldPeople ON oldPeople(dob, personID)
GO

Note that you don't need a check constraint on this table as it's moving out of the partition, not into it.

Now that you have the table and clustered index to match the partitioned table and index, you can
execute the switch to move out a set of data. This is done using the ALTER TABLE SWITCH statement again,
but note that now the syntax has changed a littleandyouhavetospecifypartitionnumbers.

-- now go switch out the partition
ALTER TABLE People
SWITCH partition 1 -- which partition are we removing

TO OldPeople
-- No partition here as we are switching to a NON partitioned table
GO