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
0
votes
1 answer

Does SQL Server "NOT Allow" multiple roots?

I am very new to HierarchyId world and trying to implement HierarchyId() into my SQLServer design and I was under impression that SQL Server allows only one Root node per Column per table and if I try to something like following if a table already…
Lost
  • 12,007
  • 32
  • 121
  • 193
0
votes
1 answer

Should I worry about running out of HierarchyIDs?

When you ask for a new HierarchyID between two others, the result gets progressively longer. For example, between 2/5.6 and 2/5.7 there's only 2/5.6.1 and other 4 component paths. The HierarchyID data type is limited to 800 some bytes, so you…
Bruno Martinez
  • 2,850
  • 2
  • 39
  • 47
0
votes
1 answer

Automatically creating HierarchyID in MSSQL 2008

I am creating a table with both a self referencing foreign key and a hierarchyid. Is there a simpler way to fill the hierarchyid than creating it for every node somehow like the following? DECLARE @ID INT = 9 UPDATE dbo.Tree SET Path = (…
Dominik G
  • 1,459
  • 3
  • 17
  • 37
0
votes
1 answer

SqlMetal, Sql Server 2008 database, Table with HierachyID, dal cs file is created sometimes?

I have 2 databases with a 2 tables with HierachyID fields. For one database I can get a dal cs file, for the other database I cannot get a dal cs file ? HBus is a database I can get the dal cs for, ... SqlMetal /server:.\SQLSERVER2008 /database:HBus…
user206890
  • 531
  • 6
  • 17
0
votes
1 answer

GetAncestor query on SQL hierarchyid to always pull certain levels

We commonly get asked to include certain levels of a hierarchy in a report and I am looking for a way to speed up the query performance with hierarchyid. I did some testing and and I have queries that work but I think the performance could be…
pretzelb
  • 1,131
  • 2
  • 16
  • 38
0
votes
1 answer

Flatten the tree path in SQL server Hierarchy ID

I am using SQL Hierarchy data type to model a taxonomy structure in my application. The taxonomy can have the same name in different levels During the setup this data needs to be uploaded via an excel sheet. Before inserting any node I would like…
Sharath Chandra
  • 654
  • 8
  • 26
0
votes
0 answers

hierarchyid / SSIS / memory variable

I realize that I can convert a hierarchyid to a string variable, and store it as a string in an SSIS memory variable. what I can't figure out is a bulk insert within SSIS, and use the memory variable to insert it into a table with the column typed…
codeputer
  • 1,987
  • 3
  • 19
  • 45
0
votes
1 answer

hierarchyID GetAncestor as ID

Problem: I need to extrapolate the parentID from the hierarchyID. Example: My columnID is 8 and my current hierarchyID is /1/2/4/8. When I call columnName.GetAncestor(1), this will give me /1/2/4/. What I need is the ID of 4. How do I do this? Or…
rlb.usa
  • 14,942
  • 16
  • 80
  • 128
0
votes
1 answer

SQL Server 2012 HierarchyId with Two Parents?

I have a working system that uses a hierarchy, sample is below. I now need to make a child have two parents, in my example I need to make 'tomato' (id=4) both a fruit and a vegetable. I could restructure the table with a new PK and then duplicate…
Snowy
  • 5,942
  • 19
  • 65
  • 119
0
votes
2 answers

how can I check that a node with hierarchyid '/1/1/' has a child node with hierarchyid '/1/1/x'?

Could you, please, help me to checkthat a node with hierarchyid '/1/1/' has (or has not) a child node with hierarchyid '/1/1/X'?
user1681317
0
votes
1 answer

Efficient management of hierarchyid values in MS SQL Server

With the hierarchyid datatype in SQL Server 2008 and onward, would there be any benefit to trying to optimize the issuing of the next child of /1/1/8/ [ /1/1/8/x/ ] such that x is the closest non-negative whole number to 1 possible? An easy…
Hans Reiser
  • 1
  • 1
0
votes
1 answer

use hierarchyid to find leaf nodes

I have a Locations table that uses a hierarchyid column to map cities/regions/countries/continents. The table looks like this: declare @Locations table ( LocationNodeID hierarchyid, LocationID int, LocationName varchar(50) ) insert into…
John D
  • 79
  • 1
  • 5
0
votes
1 answer

SQL Server: how to fill hierarchyID fields for big table?

I have a big table with 100 000 000 rows. It contains a tree with one root. The tree implemented as 2 fields: id int parent_id int No additionals data about this tree. I want to add new column to the table hier hierarchyid and fill it with…
ZedZip
  • 5,794
  • 15
  • 66
  • 119
0
votes
2 answers

Find all nodes that have children with hierarchyid

Given this table: CREATE TABLE Employee ( EmpId INT PRIMARY KEY IDENTITY, EmpName VARCHAR(100) NOT NULL, Position HierarchyID NOT NULL ) INSERT INTO Employee (EmpName, Position) VALUES ('CEO', '/'), ('COO', '/1/'), ('CIO',…
B Z
  • 9,363
  • 16
  • 67
  • 91
0
votes
3 answers

How to locate row within a hierarchy

I'm trying to check if a specific ID can be located within a hierarchy. I have a list of categories stored in a hierarchical fashion. Example (0x, 0, 1, 'root'), (0x58, 1, 2, 'Market Zone'), (0x5AC0, 2, 3, 'Informática'), (0x5AD6, …
Carlos
  • 23
  • 5
1 2 3
…
10
11