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

This is pretty simple syntax. The main thing you need to look out for is the exact number of partitions to
be created. The partition function created 13 boundaries, so you need to have 13 + 1 = 14 partitions for
the data to go into.

Because you are partitioning an existing table and I don't want you to have to move the data, you're
keeping it all on the existing file group.

Now you're going to create a clustered index partitioned using the partition scheme to ''rebuild'' the data
into the correct partitions:

CREATE CLUSTERED INDEX cix_People ON people(dob,personID)
ON peoplePS1(dob)

The clustered index key is on dob and personID because dob is the partition key and personID will make
each index record unique (see Section1: Clustered Indexes). An index created with the same partition
scheme as the underlying table is referred to as an aligned index .Thisisn'tarequirementbutwillbethe
default when creating an index on an already partitioned table.

Finally, you can check the system metadata to confirm that you have partitioned correctly. To do this
there is some new syntax you can use to determine the partition that data lives on: $partition .Hereis
an example of using this to see how many rows are on each partition:

- this will tell us how many entries there are in each partition
SELECT $partition.PeopleRangePF1(dob) [Partition Number], count(*) AS total
FROM people
GROUP BY $partition.PeopleRangePF1(dob)
ORDER BY $partition.PeopleRangePF1(dob)

Partition Number total
---------------- -----------
1 5379

2 18240

3 18206

4 18259

5 18147

6 18075

7 18295

8 18131

9 18313

10 18242

11 18091

12 8987

13 1810

14 1823

Using this syntax, you can see the number of rows on each partition to confirm the data is distributed as
you planned for.

SwitchinginNewDatatothePartitionedTable
Now that your table is partitioned, you need a way to add new data offline and switch that new data into
the partitioned table. The first step is to create a table to hold all the new data to be loaded. This needs to
have an identical layout to the partitioned table: