Partitioning in databases

When databases grow large, it is ideal to identify tables containing high volume of data and split them into multiple smaller sets of tables. This approach is called partitioning. Performance and manageability are the primary benefits.

Partitioning can be done in one of the two ways, viz, horizontal and vertical.

Horizontal partitioning involves creating logical groups of data within a table based on one or more columns. Values stored in a horizontal partitioning column help create mutually exclusive sets of data. An example is a table containing order data based on region. Orders pertaining to a region could form one set. Likewise, orders pertaining to a different region could form a different set.

Vertical partitioning, on the other hand, is the process of splitting columns into multiple tables. For example, if a table contains 50 columns, some of the 50 columns could be in one partitioned table, the remainder of the columns could be in another partitioned table.

Typically, in OLAP databases, it is common to adopt horizontal partitioning to store table data in multiple tables. Both SQL Server 2000 and SQL Server 2005 support partitioning. However, the way SQL Server internally implements partitioning has been greatly enhanced in SQL 2005.

An excellent article on SQL Server table partitioning is available here.