'partition-id' in AW2008R2

  • Hello,

    Can anyone could explain to me:

    1) how SQL Server manages the PartitionID number,

    2) it's relation with Indexes,

    3) it's relation with the PartitionNumber,

    I'm asking that because, when I execute DBCC IND (AdventureWorks2008R2, 'Person.Address', -1) in AW2008R2, I can identify 4 differents PartitionID corresponding to 4 indexes of the table, even if the PartitionNumber is the same for the whole table (table is not partionned).

    Thanks.

  • Every table consists of at least one partition, every index consists of at least one partition. Hence 4 indexes mean 4 different partition IDs. It's just a number, like object id, useful only in looking up in the metadata views.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Understood !

    What confused me was the difference between PartitionNumber and PartitionID.

    Thank you.

  • Partition number is literally just an incremental ID of partitions in a table. If a table is not partitioned, that will be 1. If the table is partitioned then it will be 1..n where n is the number of partitions.

    Partition id is also known as the allocation unit id, it's just an identifier for the table's storage unit (for lack of a better word). Every table and index will have at least one partition id, even if it's not a partitoned table.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply