On Thu, Jul 10, 2014 at 11:36 AM, AlexK <alkuzo@gmail.com> wrote:
Kevin,
What would be the advantages of partitioning on ranges of ParentID? Each query will touch at most one partition. I might or might not get PK indexes one level of depth less.
I understand that I will CLUSTER these smaller tables and benefit from that. Other than clustering, what are other advantages?
If you don't partition, it will take an unacceptably long time to run CLUSTER on the entire table. If you do partition, you can CLUSTER one partition at a time, and only need to CLUSTER the fast-changing partitions more than once. But based on your description, you probably don't need to run explicit CLUSTERs anyway as your data would end up naturally clustered.