Friday, November 27, 2009

FAQ: What all are the types of table partitions in SQL Server

There are two types of partitions. Horizontal and vertical.

Horizontal partition
Horizontal partition divides the data on subset of rows. Ie. Divide the data in such a way that all the rows of single year /month /week(Range) may be in one partition kind. In earlier versions of SQL Server this method was achieved by creating physical tables for each subsets and UNION the tables using a View(Partition View) and application access the data using Views.

Vertical partition
In Vertical Partition , the columns of a very wide table are spread across multiple physical tables containing distinct subsets of the columns with the same number of rows. The relation would be ONE TO ONE among the tables. The result is multiple tables containing the same number of rows but different columns, usually with the same primary key column in each table. Often a view is defined across the multiple tables and queries directed against the view.

No comments:

Locations of visitors to this page