Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The HierarchyID Datatype in SQL Server 2008


The HierarchyID Datatype in SQL Server 2008

Author
Message
jean-pierre riehl
jean-pierre riehl
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 54
Comments posted to this topic are about the item The HierarchyID Datatype in SQL Server 2008
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)SSCertifiable (6.3K reputation)

Group: General Forum Members
Points: 6277 Visits: 1407
Good article. The concept will be more clear when I started to use SQL Server 2008. But kudos to the author.



Jack Corbett
  Jack Corbett
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11034 Visits: 14858
Good article. The only question I have is on the performance testing. By making the heirarchyid column the primary key you gave that method a distinct advantage over the classic method because that is now the clustered key. How likely is it that your heirarchy is going to be the clustered key? I do not have SQL 08 so I cannot test against it, but by changing the clustered key to the manager id column you get a clustered index seek within the CTE. How would this performance compare to the heirarchyid method? Could someone who has SQL 08 check it?



Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming
At best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at work

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
timothyawiseman
timothyawiseman
SSC Eights!
SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)SSC Eights! (804 reputation)

Group: General Forum Members
Points: 804 Visits: 920
This article was clear, concise, and well written, and it will be a big help when we do make the transition to SQL Server 2008.

I am curious about the performance testing you listed though. Have you tried any time tests on similar datasets to get actual time results? Could your choice of indexes have had a major impact on it?

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Gregg Walker
Gregg Walker
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 26
Sorry but I was lost after the first part of the article. I don't know if you are a non-english speaker but you speak in incomplete sentences and it makes your article hard to follow.
steitelbaum
steitelbaum
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 81
JPR thanks, articles like your's help us to better understand this new feature.

We're curious about MS's approach to inserting a sibling between two others. It looks like after all the "slash delimited" path info (we understand its not really slashes but rather some binary scheme), an id is available that can be used to order "this" node in a consistent fashion relative to it's siblings.

So pretending like periods are used, let's say we want to insert a sibling between siblings 1.2 and 1.3. We assume the new id would be 1.2.1. Can you tell us how many more bytes (or nibbles or bits) it took to represent 1.2.1 than either 1.2 or 1.3?

Also, if we keep inserting after the last id, between 1.2.1 and 1.3, 1.2.2 and 1.3, 1.2.3 and 1.3 etc, will we go up to 1.2.256 before rolling over to 1.2.256.1?
Sir Slicendice
Sir Slicendice
SSC Rookie
SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)SSC Rookie (44 reputation)

Group: General Forum Members
Points: 44 Visits: 315
Nice article.

Does anyone know if AS in SS08 supports the HierarchyID type for parent-child hierarchies? There's a lot of very specific logic in AS for the current linkage (ID and parentID columns) approach.

Also, it seems to me that the performance aspects would need to be considered carefully on an application basis: if re-parenting a sub-tree is a frequent application function, I would think the current linkage approach would dramatically outperform the new HierarchyID type approach. Similarly, if an application executes more queries that are only interested in a single level (not a full sub-tree), performance with the existing approach would be generally better.

-frank


The End.
Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
Thanks for the article! Hierarchy functionality is always tricky, and this just might simplify it. I'll need to play around with it.

I'm wondering if this solves the complexities inherent in the adjacency model we all know and love so well /sarcasm.

Is there talk about this being a new ansi standard, or it is destined to be a vendor specific implementation? I'll use it either way, but more convenient if cross platform compatibility is at least an option.

I also like Change Data Capture for ETL, it's very convenient and powerful. And the new Merge syntax is sweet, and does away with multiple source updates to the same destination.

It seemed like English was your second language, which is awesome, good job. However, you might want to get a native speaking English person to act only as editor; your articles will be stronger for it.

Signature is NULL
bwatt
bwatt
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 27
Wow, I barely understood a word of this article! Hopefully the SS documentation will do a better job of explaining this data type, because now I'm not sure what it does!

I realise that English is not everyone's first language, but then again not everyone is attempting to write articles in a language they have barely grasped.



Calvin Lawson
Calvin Lawson
Mr or Mrs. 500
Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)Mr or Mrs. 500 (582 reputation)

Group: General Forum Members
Points: 582 Visits: 102
There are constructive ways to say things and non-constructive ways to say things. Didn't your momma teach you the difference between these?

Signature is NULL
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search