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

-- Create a new People table to hold any births after 09/20/2007
-- MUST BE IDENTICAL to People
CREATE TABLE newPeople (
[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 target partition!

After doing this, you need to create modified versions of the usp_birthInsert and usp_loopBirth-
Insert stored procedures to insert rows to the newPeople table rather than inserting to the People table.
For this example, call the new procedures usp_newbirthInsert and usp_loopNewBirthInsert .

Inserting to the new table will be fast because it's empty and there are no indexes to slow down the
insert rate. Insert some data into the table — a hundred thousand rows should do it. Do this by running
usp_loopNewBirthInsert . Changing the arguments will determine how many rows it inserts.

EXEC usp_loopNewBirthInsert 50000, 10000
GO

Next, create a clustered index to match the partitioned index:

-- create the index on the new table.
CREATE CLUSTERED INDEX cix_newPeople ON newPeople(dob,personID)

One more thing you need to do is create a check constraint on the new table to ensure that the data
matches the partition function boundary you are about to set up:

-- Before we do the switch,
-- create a check constraint on the source table
-- to enforce the integrity of the data in the partition
ALTER TABLE newPeople
ADD CONSTRAINT [CK_DOB_DateRange]
CHECK ([DOB] > = '09/20/2007');
GO

Now you can start making changes to the live partition to prepare it for the new set of data you want
to load. The first step is to alter the partition scheme to specify where the new partition is going to live.
This has to be on the same file group as the new table. In this case, it's easy as everything is on the Primary
file group anyway. In a production system with multiple file groups this would be one of the empty file
groups available. For more details on using multiple file groups, see the ''Partitioning a Production
System'' section coming up shortly.

-- alter the partition scheme to ready a new empty partition
ALTER PARTITION SCHEME PeoplePS1
NEXT USED [PRIMARY];
GO

The next step is to create the new partition boundary in the partition function. This is done by using an
ALTER PARTITION FUNCTION statement. In this case, you have a range right function, your new data is