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

Update SQL Server HierarchyId in after insert trigger

I'm developing a message center in a information system, and today user Eric suggested use hierarchyid datatype to track the message reply, because the objective is to show as a Outlook or Gmail conversation. To simplify, I've in my database table…
joaoasrosa
  • 301
  • 1
  • 17
3
votes
0 answers

Type 'Microsoft.SqlServer.Types.SqlHierarchyId' is not supported for identity members

I'm getting the above exception while trying to read data from a table using Linq2Sql. Does anyone know what's happening here?
Dmitri Nesteruk
  • 23,067
  • 22
  • 97
  • 166
3
votes
1 answer

Using HierarchyId with ASP.NET Core causes error in send post request?

I want to make countries using HierarchyId type so I created a Country model class with these properties: namespace DotNetCore5Crud.Models { public class Country { public int Id { get; set; } [Required] public string…
m-Esmaeil
  • 33
  • 1
  • 7
3
votes
2 answers

how to change a parent id of child and subchilds in hierarchyid

id hierid --------------------- 2 | /2/ 7 | /2/7/ 8 | /2/8/ 11 | /2/7/11/ 13 | /2/8/13/ 17 | /2/8/17/ 37 | /2/8/37/ i want to change the root of this to /3/ that means the output i want to get is id …
Nighil
  • 4,099
  • 7
  • 30
  • 56
3
votes
2 answers

How to show all recursive results with hierarchyid sql

I have a table categories: ID | NAME | PARENT ID | POSITION | LEVEL | ORDER ---------------------------------------------------------------------------- 1 | root | -1 | 0x | 0 …
feronovak
  • 2,687
  • 6
  • 35
  • 54
3
votes
1 answer

Is there any examples of storing social network graph in hierarchyid in sql server 2008

I have been investigating an approach towards to store our social graph data which obviously is going to be huge when time evolves. The graph is a bidirectional graph, meaning, two nodes are always connected to each other. I was wondering if I can…
asyncwait
  • 4,457
  • 4
  • 40
  • 53
3
votes
1 answer

Get Direct Descendants Count with HierarchyId

SQL Server 2008 w/ hierarchyId Given this structure: CREATE TABLE Employee ( EmpId INT PRIMARY KEY IDENTITY, EmpName VARCHAR(100) NOT NULL, Position HierarchyID NOT NULL ) INSERT INTO Employee (EmpName, Position) VALUES ('CEO', '/'), …
B Z
  • 9,363
  • 16
  • 67
  • 91
3
votes
1 answer

What native dotNet data type is most appropriate for conveying SQL Server hierarchyId values?

Specifically we have a SQL Server stored procedure that accepts a hierarchyId as a parameter, and typically we have a SOAP layer on our stored procs that allows them to be called via SOAP. The SOAP services are implemented using the [WebMethod]…
redcalx
  • 8,177
  • 4
  • 56
  • 105
3
votes
1 answer

SQL: Finding the Lowest Common Ancestor of hierarchyids

I have a dataset containing (among others) the values hierarchyids and the Parent-hierarchyids. For another query I need the lowest common ancestor of the hierarchyids and since I am fairly new to sql and espacially hierarchyids. ID HID …
Fantaftw
  • 95
  • 1
  • 6
2
votes
2 answers

HierarchyID Aggregate Functions in T-SQL

I've been asked to query a time logging database, to display all the work done for given projects. Each project is broken into tasks, each of which may itself be broken into tasks. The task hierarchy can be an arbitrary number of levels deep. …
Simon Elms
  • 17,832
  • 21
  • 87
  • 103
2
votes
1 answer

SQL Server - Checking for integer (id) within hierarchy of ids

How would one go about checking whether a given ID is within a hierarchy of IDs? For example, let's say I have the following hierarchies in a self-referencing table called locations (e.g. these are hierarchical locations): -- Declare the locations…
rlarson
  • 75
  • 6
2
votes
1 answer

Updating all HierarchyID nodes in SQL Server 2008

I am importing data from one database to another. The hierarchyid column must also be ported over with the relationships intact. If the target table is empty, it's easy. When the target contains rows, the hierarchy as it is in the source table will…
callisto
  • 4,921
  • 11
  • 51
  • 92
2
votes
1 answer

How do you build a recursive Expression tree in Entity Framework Core?

We are using EFCore.SqlServer.HierarchyId to represent a hierarchy in our data. My goal is to return the descendants of an object with a particular path of indeterminate length, e.g. given a tree with the hierarchy one->two->three->four, the path…
dtryan
  • 527
  • 3
  • 14
2
votes
1 answer

List menu hierarchy using SQL Server hierarchyid

I try to build a menu using the datatype hierarchyid. I have the root node and the current selected node. now I want to list of all elements that are related wetween root and selected node AND there siblings. I get all related elements with…
Thomas Brand
  • 23
  • 2
2
votes
1 answer

Assembly Load Error - > Could not load file or assembly 'Microsoft.SqlServer.Types, Version=10.0.0.0' or one of its dependencies

In my .NET CORE project, I'm using layered structure and for Spatial Types I am using dotMorten.Microsoft.SqlServer.Types Version="1.1.0" Here are my .csproj files: Entities.csproj :
Burak
  • 467
  • 4
  • 16
1 2
3
…
10 11