DaveWentzel.com            All Things Data

Partitioning

 

horizontal partitioning is splitting a table into multiple smaller "tables" by rows using a partitioning scheme.  Tables must have a col that is NOT NULL and can be used to divide the rows into discreet, non-overlapping sections. 
 
In 2000 we only had partitioned views, not partitioned tables.  If you migrate to 2005 you may want to test migrating your partitioned views to partitioned tables. 
 
Partitions must be in the same instance/db so it's not a scale-out solution.  Distributed Partitioned Views on federated servers is the scale-out solution. 
 
Table Partitioning
In previous versions of SQL we had access to distributed partitioned views (materialized views) (DPV).  These require a lot of effort to setup and sometimes still don't work as advertised. 
 
With 2005 we now have table partitioning.  Each partition resides on the same server in its own data file.  Each data file is in a filegroup and multiple partitions can be in the same filegroup.  You only need have separate filegroups if you are splitting I/O load for performance reasons. 
 
The database engine determines at query runtime which partitions need to be read and if multiple partitions are needed the work is done in parallel with different processors. 
 
This is also valuable for loading DW fact tables.  See more on this here
 
Alignment
It's probably best to always partition a table and it's indexes using the same partitioning columns and boundaries.  This is called alignment. 
 
Boundaries
It's common to use datetime boundaries.  Assume the boundary is by date using a datetime col.  RANGE RIGHT is helpful here to ensure you don't accidentally split days into two different partitions.  If you would rather use RANGE LEFT (which is what you will use most of the time), then ensure you have the maximum possible value for the given day (or month or whatever) for the datetime (or whatever) datatype. 
 
Partition Existing Data
Create the clustered index WITH DROP_EXISTING with the new partition scheme.  This can be done ONLINE = ON. 
 
 

Add new comment