Databases Reference
In-Depth Information
CreatingaPartitionTablefromanExistingTable
You're going to start with an existing table and turn it into a partitioned table, but you're going to keep
it all within the same file group to avoid having to physically move the data around.
The partition function determines how your data is split between partitions. The first step is to create
the partition function. Here is the code for a partition function that splits the table called People into
multiple partitions based on the DOB field:
-- Range partition function for the People table,
-- every 10 years from 2006 - 110 = 1896,
-- start the first partition at everything before 1 Jan 1900
-- 1890-1900, 1900-1910, 1910 - 1920, 1920-1930, 1930-1940, 1940-1950, 1950-1960,
-- 1960-1970, 1970-1980, 1980-1990, 1990-2000, 2000 onwards
CREATE PARTITION FUNCTION [peopleRangePF1] (datetime)
AS RANGE RIGHT FOR VALUES ('01/01/1900', '01/01/1910', '01/01/1920', '01/01/1930',
'01/01/1940', '01/01/1950','01/01/1960', '01/01/1970', '01/01/1980',
'01/01/1990', '01/01/2000', '01/01/2005', '01/01/2006'
);
GO
This example is based on the sample database, tables, and procedures mentioned at the beginning of
the chapter in the ''Sample Database'' section. Files to create this can be found on the topic's web site at
www.wrox.com
.
Most of this looks straightforward, but there is one new bit of syntax that's needs explaining;
range
right for values
. It's there to determine how the boundary condition is applied at the actual range
value. It does this by telling the function what to do with data that exactly matches the range bound-
ary. In this case, the first range boundary is 01/01/1900. What should the function do with data that
matches that value? Does it go above or below? Range Right tells the function to put the matching
data into the right side (higher values, or above the range boundary) of the data, whereas range left
tells the function to put the data into the left side (lower values, or below the range boundary) of
the data.
Something else to watch for here when using
datetime
fieldsistheprecisedateandtimeyou'rematch-
ing. In this example, you have the luxury of not worrying about the exact placement of a few values close
to the boundary that may get into the next partition. In a production system, you will be concerned that
the partition holds exactly the values you want. In that case, you need to pay particular attention to the
exact date and time constant you specify for the range boundary.
The second step is to create the partition scheme, as follows:
CREATE PARTITION SCHEME [peoplePS1]
AS PARTITION [peopleRangePF1]
TO ([PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY], [PRIMARY]
, [PRIMARY], [PRIMARY]);
GO
Search WWH ::
Custom Search