Creating Hierarcies with HierarchyId in SQL Server 2008

SQL Server 2008 adds a new feature to help with modeling hierarchical relationships: the HIERARCHYID data type. It provides compact storage and convenient methods to manipulate hierarchies. In a way it is very much like optimized materialized path. In addition the SqlHierarchyId CLR data type is available for client applications.

Plamen Ratchev has over two decades experience in the software architecture and development field. He has enjoyed building solutions for UNIX, DOS and Windows platforms, with main focus on SQL Server since version 4.21. Plamen is founder of Tangra, specializing in relational database applications analysis, implementation, and tuning. His particular interest is in design patterns, performance a...

Carle Eric Giannelli on 4/13/2009

good brief intro on data type

Murali on 7/23/2009

Thanks you, very good explanation

Caleb Bell on 7/30/2009

Good quick overview!

3EAA01C6D4 on 9/6/2009

Accent of the speaker was kind of difficult to understand.

Travis on 9/18/2009

Another good job Plamen! Attached code would be nice also...

pete on 9/18/2009

Would have been great had the quality of the audio not kept breaking up.

Lou Gallo on 9/18/2009

Good technique to know. The accent was a bit heavy but, with concentration, it was understandable.

Richard Cook on 9/18/2009

Concise look at the new feature. Now how would a developer consume it. In other words, how would I get data inot a hierarchial datasource.

Eric Willesmtein on 9/18/2009

Over all comment. I would like to see transcripts of the examples used in the video's. This would be just as helpful as the video itself

Jonathon Moorman on 9/18/2009

Very interesting feature. I hadn't used it before but now that i understand it better, I have an immediate use for it! Just curious. Are there methodologies for changing the hierarchy structure. In the employee example, how easy is it to change an employee if promoted or demoted?

Plamen Ratchev on 9/18/2009

Eric, the other video for manipulating hierarchies has the code attached: http://www.sqlshare.com/manipulating-hierarchies-with-hierarchyid_544.aspx

Plamen Ratchev on 9/18/2009

Jonathon, please see the other video for manipulating hierarchies which has example of what you are asking for: http://www.sqlshare.com/manipulating-hierarchies-with-hierarchyid_544.aspx

Plamen Ratchev on 9/18/2009

Richard, here are links to two resources that provide a lot of details on loading a hierarchy into table and consuming it: http://technet.microsoft.com/en-us/library/bb677212.aspx?ppud=4 http://technet.microsoft.com/en-us/library/bb677213.aspx

Wayne Pollack on 9/18/2009

This would have been a little better if it was show by adding in a few more employees as examples for spreading out the tree

Diana Dee on 9/19/2009

Way too fast for a newbie to hierarchyid data type. When will the scripts seen on these videos be made available????????

Mark Hions on 10/2/2009

Too fast, too complex, not enough detail. This could easily be a 10 minute video. And you should try to modulate your voice also.

Steve Harris on 12/22/2009

maybe show an org chart before as an example of what you are going to accomplish

David Hurley on 4/26/2010

Good intro on the data type! attached code would have been nice! Thanks

Plamen Ratchev on 4/27/2010

Hi David, the code is attached on the top of the page under References, see the Code Sample link.

Tom Hamilton on 12/24/2010

I'd like to see more on this subject(Hierarchy) - thanks and Merry Christmas

Enrique Hernandez on 12/24/2010

Good video, with useful information. However the accent of the speaker makes it hard to understand at times.

Simon Jonasson on 12/27/2010

Good intro.

Charlie Bruno on 12/27/2010

Nice basic explanation.

Keith Mescha on 12/27/2010

Hard to hear and understand the presenter.

Talha on 12/27/2010

Great stuff. Would apreciate if a bit more use is demonstrated.

Joe DeMarco on 12/28/2010

Would like to see more on this subject

Michael P. Antonovich on 12/28/2010

Would have liked to see intermediate results at each step rather than all at one time at end.

Martin Miller on 12/28/2010

This is a good introduction to HierarchyID.

Derek on 12/29/2010

This seems to be a pretty in-depth topic, so it doesn't seem suited for a 5-minute tutorial.

Derek on 12/29/2010

I knew that there would be a bunch of comments on the speaker's accent. C'mon people! English is not his first language and he is already aware of that, so telling him that he has an accent is not very constructive. How many of you can speak a foreign language?

Leon on 12/29/2010

Too fast for me...

WChaster on 12/30/2010

Good demo of this feature.

Plamen Ratchev on 12/30/2010

Joe, here is a follow up video that gets into more details: http://www.sqlshare.com/manipulating-hierarchies-with-hierarchyid_544.aspx

Mat on 1/3/2011

didn't know of that data type

Prerna on 1/10/2011

Very Informative !

Keith Badeau on 5/8/2011

As usual, another great video. This author is new to me but the quality is still reflected. Thank you.

Keith Badeau on 5/18/2011

Good intro, up until a few days agao I din't even know what hierarchyid really was in terms of a data type. Nice simple example as well, Plaman, thanks.

Anil Babu on 6/16/2011

nice thanks

Anil Babu on 6/16/2011

nice thanks

CECity.com on 7/13/2011

very useful feature

suneel on 7/22/2011

good. Appreciate, if an example is shown on using this with real time example.

T. Carnahan on 11/12/2011

I think I missed the video that talks about HIERARCHY::descendent or root. I am not familiar with these.

Christian Bahnsen on 12/26/2011

Thanks. I can see this bears looking into.

Vijay S on 1/25/2012

Very nice. Thanks

Prasanna Prabhu on 2/13/2012

Excellent