Table partitioning

I’m never going to explain this subject in more detail than the experts so I might as well go the other way on this and make it brief. In SQL Server 2000 the concept of partitioned views was introduced which along with the introduction of INSTEAD OF triggers gave users the ability to effectively deliver data into separate tables whilst retaining a single point of entry. If I cast my mind back a few years I think this was the same technique used to get SQL Server on top of the TPC benchmarks. In SQL Server 2005 you now have partitioned tables and the generation and usage around it is pretty funky to be honest. Partitioned tables are created in three stages. First is the creation of the partition function which effectively is a statement of the data range and definition of the boundaries within the range (e.g. before Jan, Jan, Feb, Mar, After Mar). Key to the statement are the LEFT or RIGHT designation which rather than attempt to badly clarify its nuances there’s an excellent posting on Kimberly Tripp’s Blog that delivers a lot of useful information on this subject.
After the partition function comes the partition scheme that maps the partitions (whose number is defined by the function) to the filegroups and database. When the partition function and scheme are in place then a partitioned table can be built in the following syntax;

CREATE TABLE <table_name>
(
<column_list>
) ON <partion_scheme>(<bound_column>)

The great thing is that you can re-use the partition function again on another table if that’s how you want to partition the table. For the majority of the OLAP work I do that is almost always going to be the case and I would partition my tables by time just as I would partition cubes I built by the time dimension.