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

SQL Server Nested set vs Hierarchyid performance

I have a hierarchical data. The most common queries will be "get parent branch for node" and "get subtree of node". Updates and inserts are not likely to occur often. I am choosing between nested sets and hierarchyid. As far as I am concerned,…
Vasaka
  • 579
  • 6
  • 17
6
votes
2 answers

Entity Framework Core Scaffolding "Could not find type mapping for column with data type 'hierarchyid'. Skipping column"

I'm experimenting with .net core, entity framework and sqlserver, i thought the hierarchyID is a perfect datatype for what i'm trying to model. But when i scaffolded the database, EFC was not able to map the hierarchy. Could not find type mapping…
Red
  • 126
  • 1
  • 6
6
votes
4 answers

Anyone used SQl Server 2008 HierarchialID type to store genealogy data

I have a genealogical database (about sheep actually), that is used by breeders to research genetic information. In each record I store fatherid and motherid. In a seperate table I store complete 'roll up' information so that I can quickly tell the…
E.J. Brennan
  • 45,870
  • 7
  • 88
  • 116
6
votes
4 answers

HierarchyID How to get all Parent from a child

i've a Problem with the hierarchyID and UserRights for a Menu. I only want to give the User the Right for Level 4 for example and my QUery should automaticly select all Parent from the Level 4 Child. How to do this ? Do you understand my Problem ?…
Manuel
  • 61
  • 1
  • 1
  • 3
5
votes
3 answers

Sql Hierarchy ID Sorting By Level

Is it possible to sort sql data in a hierarchy by it's hierarchy id, and then for each level sort it say alphabetically? So say we have an Employees Table that lists the organizational hierarchy based on the Employees ID You have Bob (5) who has…
Mike Kshymensky
  • 101
  • 1
  • 7
5
votes
1 answer

not found: 'Void CoreTypeMappingParameters..ctor

I updated my project from ef 6 to 7 and got this error not found: 'Void CoreTypeMappingParameters..ctor(System.Type, Microsoft.EntityFrameworkCore.Storage.ValueConversion.ValueConverter, Microsoft.EntityFrameworkCore.ChangeTracking.ValueComparer,…
mahdi
  • 189
  • 2
  • 2
  • 11
5
votes
2 answers

How is HierarchyId stored in SQL Server?

I have a hierarchyid with the value '/1/'. When I insert it into the SQL Database it's stored as 0x58. Here is my table: CREATE TABLE [dbo].[Category]( [CategoryId] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NULL, …
Decano
  • 93
  • 6
5
votes
2 answers

How to call stored procedure from EntityFramework 6 with 'hierarchyid' parameter

I am developing a service using WebApi2 and EntityFramework6. I have a legacy SQLServer DB that my service must work with. That DB is using heavily the 'hierarchyid' data type and this type is used internally in DB's stored procedures. Seems like…
Shonn Lyga
  • 109
  • 2
  • 8
5
votes
2 answers

How to transform list of hierarchyid into a binary tree

I am working on a multi-level marketing (binary) which looks like this: (but the binary tree is not required to be perfect. A node can have 0-2 child) My problem is the data that I fetch from the database is flat list. Notice that I am using…
Jaime Sangcap
  • 2,515
  • 6
  • 27
  • 45
5
votes
0 answers

CTE with HierarchyID suddenly causes parse error

So I have this self-referencing table in my database named Nodes, used for storing the tree structure of an organization: [Id] [int] IDENTITY(1,1) NOT NULL, [Name] [nvarchar](max) NULL, [ParentId] [int] NULL, (+ other metadata columns) And from it…
barbatron
  • 123
  • 1
  • 6
5
votes
2 answers

MySQL: Alternate solution of SQL Server's HierarchyId datatype

My current application was built up in SQL Server 2008 server in JAVA with Hibernate and I had used HierarchyId data type for department hierarchy in my database. I had written SQL queries to deal with HierarchyId datatype. And I also have n-Level…
Saharsh Shah
  • 28,687
  • 8
  • 48
  • 83
5
votes
3 answers

Why is there no equivalent for hierarchyid SQL data type?

I understand it that we have to map this type as binary to get to it in the application code. But why isn't there an exact equivalent with all those type methods? How are we supposed to work with in the code? Or we aren't supposed to work with it…
User
  • 30,403
  • 22
  • 79
  • 107
4
votes
2 answers

Cannot insert duplicate key in object (GetReparentedValue / hierarchyid)

Using examples I found on the web I have created a function which reparents children using the GetReparentedValue. However when I have ran the code I get the following error: Cannot insert duplicate key in object. I understand why (because I am…
ClareBear
  • 1,493
  • 6
  • 25
  • 47
4
votes
2 answers

Is hierarchyid suitable for large trees with frequent insertions of leaf nodes?

We have a database that models a tree. This data can grow fairly huge, that is to say many, may million of rows. (The primary key is actually a bigint, so I guess potentially we would like to support billions of rows, although this is probably never…
DeCaf
  • 6,026
  • 1
  • 29
  • 51
4
votes
1 answer

Find all leaf node records using hierarchyid

How would I go about querying for only records that are "only leaf nodes" (ie. no children)? I have tried a query like this: select * from TableA tt where tt.HierarchyId.GetDescendant(null, null) not in ( Select t.HierarchyId from TableA…
Marty
  • 2,965
  • 4
  • 30
  • 45
1
2
3
…
10 11