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

for Sept. 20, 2007, and you want all the new data to be in the new partition, so using RANGE RIGHT ,you
specify the boundary as Sept. 20, 2007.

-- Split the newest partition at Sept 20, 2007
-- This is a RANGE RIGHT function,
-- so anything on 09/20 goes into the NEW partition,
-- anything BEFORE 09/20 goes into the OLD partition
-- The new partition this creates goes to the new partition
-- we prepared in the alter scheme above
ALTER PARTITION FUNCTION PeopleRangePF1()
SPLIT RANGE ('09/20/2007');
GO

The next step is going to switch the new data into the partition, but before doing that, check to see how
many people you have in the newPeople table.

EXEC sp_spaceused newPeople
GO

name rows reserved data index_size unused
------------------------------------------------------------------------------
newPeople 99998 14472 KB 14416 KB 8 KB 48 KB

Now you can apply the switch and move all those people into the new partition.

-- Now switch in the data from the new table
ALTER TABLE newPeople
SWITCH -- No partition here as we are switching in a NON partitioned table
TO People PARTITION 15; -- but need to add the partition here !
GO

The switch ran very quickly. Now check how many people are in which table. First, look at how many
people are in the newPeople table:

EXEC sp_spaceused newPeople
GO

name rows reserved data index_size unused
------------------------------------------------------------------------------
newPeople 0 0 KB 0 KB 0 KB 0 KB

As expected, the table is empty. And hopefully all 99,998 people are in the partitions table in the new
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 5571