Friday, November 27, 2009

FAQ : What is the advantages and disadvantages of Table Partitioning of 2005 over Partition View of earlier versions?

Advantage of Table Partitioning
• No need to create separate physical tables for each ranges manually.
• There are few limitations when you insert data into Partition View like you cannot use BULK INSERT/BCP etc.
• Less complex to manage and administrate
• SQL Server automatically manages the placement of data in the proper partitions.

Disadvantage of Table Partitioning
• Major disadvantage is, we cannot have different index model for different range. Ie. In general, we may need to have more number of indexes when the data is READ ONLY and less or different index model for the data which is READWRITE. That is not possible with partition table.
• You cannot rebuild a partitioned index with the ONLINE option set to ON, because the entire table will be locked during the rebuild.

No comments:

Locations of visitors to this page