Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

partition_id VS. hobt_id Expand / Collapse
Author
Message
Posted Sunday, March 3, 2013 5:44 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:31 AM
Points: 57, Visits: 278
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?
Post #1425936
Posted Sunday, March 3, 2013 7:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1425950
Posted Sunday, March 3, 2013 8:17 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1425956
Posted Monday, March 4, 2013 7:23 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, July 2, 2014 8:31 AM
Points: 57, Visits: 278
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?
Post #1426237
Posted Friday, March 8, 2013 8:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1428592
Posted Friday, March 8, 2013 8:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1428593
Posted Friday, March 8, 2013 8:46 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1428616
Posted Friday, March 8, 2013 9:23 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:08 PM
Points: 20,676, Visits: 32,266
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?



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1428642
Posted Friday, March 8, 2013 9:29 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 7:27 PM
Points: 7,107, Visits: 12,657
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
Post #1428647
Posted Friday, March 8, 2013 9:33 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: 2 days ago @ 3:43 PM
Points: 39,866, Visits: 36,206
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 2008, MVP
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

Post #1428652
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse