Partitioning is all about dividing of datas to make it faster access either by reading or writing into the table. i.e improving query execution time. Partitioning can be done at Table and Index level. These are the new features in SQL server 2005 and these are supported only at Enterprise and Developer edition. The data in partitioned tables and indexes is horizontally divided into units that can be spread across more than one filegroup in a database. Partitioning can make large tables and indexes more manageable and scalable. Up to 1000 partitions per object (table or index) are supported. Lets go into detail about the topic.
Table Partitioning
Systables related to partitioning
Table Partitioning
Partitioning makes large table more manageable because it lets you manage and access and subsets of data quickly and efficiently, while maintaining the integrity of a data collection. The data of partitioned tables is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. Before partitioning a table you should decide whether its necessary to partition the table, its depend on two major factor below.
* The amount of table in the data or the growth of the table in the near future.
* Queries or updates against the table are not performing as intended.(eg. If current month data is used for Select, insert or update operations and previous months data will used only Select operations, in that case we can partition datas based on month.
Leave a Reply