Stack Overflow
Stack Overflow

Questions tagged [hierarchyid]

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

hierarchyid is a TSQL extension available from SQL Server 2008 to map hierarchical structures in a flat table. It supports in-order-sorting, get-descendant and level-queries

152 questions
34
votes
6 answers

How do you get all ancestors of a node using SQL Server 2008 hierarchyid?

Given a table with a hierarchyid type column, how do you write a query to return all rows that are ancestors of a specific node? There is an IsDescendantOf() function, which is perfect for getting the children, but there's no corresponding…
marc esher
  • 4,871
  • 3
  • 36
  • 51
29
votes
6 answers

SQL 2008 HierarchyID with Multiple Root Nodes

I wanted to use the new HierarchyID type in SQL Server 2008 to handle the page relations in a small wiki application. However It would need to have multiple root nodes since every main article/page per account would be a root node. From what I have…
Element
  • 3,981
  • 7
  • 42
  • 51
22
votes
2 answers

SQL 2008 HierarchyID support in NHibernate

Searched various NHibernate lists and haven't come up with a definitive answer. The SQL2008 dialect doesn't appear to have support for the HierarchyID data type - new date and time types only. Does anyone have a good implementation or an effective…
JasonCoder
  • 1,126
  • 2
  • 12
  • 24
22
votes
1 answer

Hierarchical SQL data (Recursive CTE vs HierarchyID vs closure table)

I have a set of hierarchical data being used in a SQL Server database. The data is stored with a guid as the primary key, and a parentGuid as a foreign key pointing to the objects immediate parent. I access the data most often through Entity…
jp36
  • 1,873
  • 18
  • 28
14
votes
3 answers

How to find ALL descendants using HierarchyID for SQL Server

I need to find all descendants of a category using HierarchyID for SQL Server. I know how to find direct children but I would like to find children of children of children and so on. Is there a way to do this using the HierarchyID?
Luke101
  • 63,072
  • 85
  • 231
  • 359
12
votes
2 answers

HierarchyID in Entity Framework not working

We are using WCF Data Service based on an Entity Framework model for our application. In this we need to add the table with a column of type HierarchyId. When I add that table to the EDMX file, the HierarchId column is not appearing in the class…
Mohanavel
  • 1,763
  • 3
  • 22
  • 44
12
votes
2 answers

Materialized path pattern VS Hierarchyid

I am reading the SQL server 2008 bible and it says the materialized path pattern is significantly faster then the hierarchyid. Is this really true? How can I make the hierarchyid have equal or better performance.
Luke101
  • 63,072
  • 85
  • 231
  • 359
11
votes
3 answers

Some questions about HierarchyId (SQL Server 2008)

I am a newbie in SQL Server 2008 and just got introduced to HierarchyId's. I am learning from SQL Server 2008 - HIERARCHYID - PART I. So basically I am following the article line by line and while practicing in SSMS I found that for every ChildId …
deeps_rule
  • 339
  • 2
  • 7
  • 14
10
votes
4 answers

HierarchyID: Get all descendants for a list of parents

I have a list of parent ids like this 100, 110, 120, 130 which is dynamic and can change. I want to get all descendants for specified parents in a single set. To get children for a single parent I used such query: WITH parent AS ( SELECT…
Andriy Horen
  • 2,861
  • 4
  • 18
  • 38
9
votes
1 answer

Represent File System in DB (using hierarchyid in SQL Server 2008)

I haven't found any specific examples of this but I am interested in representing a whole directory structure with updates, etc using the hierarchyid datatype. This is a common use case cited for the hierarchyid but I can't find any articles…
user8790899800
  • 91
  • 1
  • 3
9
votes
2 answers

Generating HierarchyID

I would like to insert the hierarchyId like this / - CEO (Root) /1/ - Purchase Manager /1/1/ - Purchase Executive /2/ - Sales Manager /2/1/ - Sales Executive This is what the hierarchy i would like to use, is it right one, if so how can…
Mohanavel
  • 1,763
  • 3
  • 22
  • 44
8
votes
3 answers

Sql HierarchyId How do I get the last descendants?

Using t-sql hierarchy Id how do I get all of the rows that have no children (that is the last decendants)? Say my table is structured like this: Id, Name, HierarchyId And has these rows: 1, Craig, / 2, Steve, /1/ 3, John, /1/1/ 4, Sam, /2/ 5,…
Eric
  • 3,632
  • 2
  • 33
  • 28
8
votes
3 answers

How can two hierarchies be efficiently merged in SQL Server?

I have two tables with hierarchyid fields, one of which is a staging table with new data that needs to be merged into the other (that is, a set of nodes that need to be added to the main tree, some of which might already be there). In addition to…
Tom
  • 1,204
  • 2
  • 10
  • 25
8
votes
2 answers

Question about SQL Server HierarchyID depth-first performance

I am trying to implement hierarchyID in a table (dbo.[Message]) containing roughly 50,000 rows (will grow substantially in the future). However it takes 30-40 seconds to retrieve about 25 results. The root node is a filler in order to provide…
ObjectiveCat
  • 312
  • 1
  • 4
  • 12
8
votes
2 answers

How can I do a Cascading Delete with the SQL 2008 HierarchyID data type?

I haven't used the HierarchyID much, so I'm a little unsure. If my table has a HierarchyID, how do I perform a cascading delete? (i.e. delete all 'children' when deleting the 'parent') I assume I would have to use a CTE and HierarchyID functions,…
willem
  • 25,977
  • 22
  • 75
  • 115
1
2 3
…
10 11