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

Now check and see how many rows are in each partition again.

-- 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
---------------- -----------
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
-- Notice that partition 1 has gone!

-- lets see how many people are now in the oldPeople table
EXEC sp_spaceused oldPeople
GO

name rows reserved data index_size unused
------------------------------------------------------------------------------
oldPeople 5571 904 KB 784 KB 16 KB 104 KB

You can see that partition 1 is no longer part of the partition table, and that the rows from that partition
are now in the oldPeople table.

There is one more bit of tidying up to perform and that's to alter the partition function to merge the old
range that you no longer need. This is done using another ALTER PARTITION function statement, where
you specify the old boundary that you no longer need.

-- next we can merge the first partition
ALTER PARTITION FUNCTION PeopleRangePF1()
MERGE RANGE ('01/01/1900');
GO
-- now check the partition layout after the merge

-- 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)