Mistake: treating a partition scheme as the boundary definition. A partition function sets boundary values and range direction; the partition scheme maps the resulting partitions to filegroups. Using ALL can leave every partition on the same filegroup, so partitioning does not automatically distribute storage. section 10.7
Use this sequence when designing a partitioned table:
- Create a partition function with a datatype, boundary values, and RANGE LEFT or RANGE RIGHT behavior. section 10.7
- Create a partition scheme that assigns each partition to precreated filegroups, or deliberately uses ALL for one filegroup. section 10.7
- Create the table with ON <PARTITION SCHEME>(<PARTITIONING KEY>) instead of ON <FILEGROUP>. section 10.7
Impression archive
A date-based archive can use an ImpressionDatesPF function and ImpressionDatesPS scheme, then query the partitioned table by EventTime. The demonstrated query reduced logical reads from 46,979 to 15,732.
