partition_id VS. hobt_id

  • Hello!

    There is 2 columns in sys.partitions view - partition_id and hobt_id. From my point of view/experience for any given row from this view partition_id always equal hobt_id and I never see the 2 different values. It's seems reasonable, because (simplifying complex thing) we can say: "partition is hobt, hobt is partition". But at the same time article about another view - sys.allocation_units tell us:

    If type = 1 or 3, container_id = sys.partitions.hobt_id.

    If type is 2, then container_id = sys.partitions.partition_id.

    So - these 2 values can ever be NOT equal?? As I said I had never see this in practical. Can you please give the example of table (and DB, if it needs), for which partition_id and hobt_id will be NOT equal?

  • Shcherbunov Neil (3/3/2013)


    So - these 2 values can ever be NOT equal?? As I said I had never see this in practical. Can you please give the example of table (and DB, if it needs), for which partition_id and hobt_id will be NOT equal?

    In SQL Server 2008/R2 the two columns will always have the same value.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • They're the same thing. They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    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
  • GilaMonster (3/3/2013)


    They're the same thing. They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    Understand. Suppose thing like this, but thanks for qualification on this question.

    In SQL Server 2008/R2 the two columns will always have the same value.

    ...and, I am almost sure, in SQL 2012 the same columns again will be just duplicate one another?

  • In SQL Server 2008/R2 the two columns will always have the same value.

    ...and, I am almost sure, in SQL 2012 the same columns again will be just duplicate one another?

    Sorry, just got around to checking this. Yes, they are the same everywhere in 2012 as well.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/8/2013)


    GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    Yes.

    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
  • GilaMonster (3/8/2013)


    opc.three (3/8/2013)


    GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    Yes.

    Please, can you enlighten us?

  • GilaMonster (3/8/2013)


    opc.three (3/8/2013)


    GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    Yes.

    😛 I walked right into that one....what Lynn said, please?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Lynn Pettis (3/8/2013)


    GilaMonster (3/8/2013)


    opc.three (3/8/2013)


    GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    Yes.

    Please, can you enlighten us?

    No.

    Feature that was never discussed or released publicly.

    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
  • GilaMonster (3/8/2013)


    Lynn Pettis (3/8/2013)


    GilaMonster (3/8/2013)


    opc.three (3/8/2013)


    GilaMonster (3/3/2013)


    They were added as separate columns to support a feature that was planned, partially added to SQL 2005 and later scrapped.

    You know I am curious about these kinds of things...remember which feature?

    Yes.

    Please, can you enlighten us?

    No.

    Feature that was never discussed or released publicly.

    Oh, one of those NDA features. Okay, got it.

  • bummer

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Definitely DBCC TIMEWARP.

  • Ha, I still do not know the backstory on that one but it sounds funny. Building on that, maybe DBCC MIDDLE_EARTH since it involves a hobt_id?

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • opc.three (3/8/2013)


    Ha, I still do not know the backstory on that one but it sounds funny. Building on that, maybe DBCC MIDDLE_EARTH since it involves a hobt_id?

    Ah yes, know that command, it replaces all your little rubber feet with big hairy ones. 🙂

Viewing 15 posts - 1 through 15 (of 16 total)

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