Table Partition

No longer is it necessary to partition a table with views and weird primary keys. SQL Server 2005 lets you create a table on a partition scheme Much like you can create a table on a different filegroup, SQL9 lets you specify the partition scheme in place of the file group. The partition scheme, derived from a partition function determines where to physically place your data in the file groups, and how to handle overflows and underflows.


/* add additional filegroups to database */
alter database Airline add filegroup Flights1;
alter database Airline add filegroup Flights2;
alter database Airline add filegroup Flights3;

/* add actual files to filegroups */
alter database Airline add file (name = 'flights_file1', filename = 'C:\Flights1.ndf') to filegroup Flights1;
alter database Airline add file (name = 'flights_file2', filename = 'C:\Flights2.ndf') to filegroup Flights2;
alter database Airline add file (name = 'flights_file3', filename = 'C:\Flights3.ndf') to filegroup Flights3;

/* create a partition function */
create partition function PF_Flights(smallint)
as range left for values (30,100)

/* create partition scheme */
create partition scheme PS_Flights
as partition PF_Flights
to (Flights1, Flights2, Flights3);

/* now we'll create the table, note how we specify onto which partition SCHEME it resides */
create table tblFlights
(
FlightID smallint identity(1,1),
FlightName varchar(20)
) on PS_Flights(FlightID)

/* now we'll add some data to demonstrate the partitioning effect */
declare @counter smallint
set @counter = 0
while (@counter <>
begin
insert tblFlights(Flightname) values ('Flight ' + CAST(@counter as varchar))
set @counter=@counter+1
end

You can use the following query to determine on which partition a record sits
select *, $Partition.PF_Flights(FlightID) from tblFlights

Changing the Range direction from Range-Left to Range-Right, alters the distribution of records. For example, when inserting 120 records onto a Range-Left partition, you'll notice that the boundary values are allocated onto the Left most partition; whereas when allocating those same 120 records the boundary values are stored in the right most partition.

Using the above example, a range LEFT partition will hold the values:
1-30 in Filegroup1
31-100 in Filegroup2
101+ in Filegroup3

But a range RIGHT partition will store the same values in this configuration:
1-29 in Filegroup1
30-99 in Filegroup2
100+ in Filegroup3

In most cases it probably wont make a difference where you store the boundary values, I would probably use a range-left as the default as this makes more sense with the syntax for setting up the boundaries themselves.