Pros and Cons of Partitioning

This article describe pros and cons of partitioning in SQL Server

PROS

* Manageability
* Fast Data Deletion and Data Load
* Piecemeal backup / restore of historical data
* Partition-wise index management
* Minimize index fragmentation for historically-partitioned tables
* Support alternative storage for historical data
* Performance querying Large Tables
* Join efficiency
* Smaller index tree or table scan when querying a single partition
* Simpler query plans compared to Partition Views
* Partitioned Table: a single object in query plans
* Single set of statistics
* Smaller plans, faster compilation than Partition Views
* Auto-parameterization supported
* Insert / Bulk Insert / BCP fully supported
* Numerous fine-grained partitions work well
* Queries may access partitions in parallel
* Partition is the unit of parallelism

CONS

* Cannot span multiple DBs or instances
* Potentially use PV or DPVs a top Partitioned Tables