Pros and Cons of Partitioning

This article describe pros and cons of partitioning in SQL Server


* 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


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






2 responses to “Pros and Cons of Partitioning”

  1. corbeille avatar

    don’t use bizare abreviations, what’s PV DPV

    1. VidhyaSagar avatar

      Thanks Corbeille for the input, in future articles let me use it appropriately.

      PV- Partitioned View
      DPV – Data Partitioned View

Leave a Reply

Your email address will not be published. Required fields are marked *