All geek questions in one place

What is the best way to set up a hierarchy in SQL Server 2005?

I am trying to set up a hierarchical structure for a company. I used SQL Server 2008 and used the hierarchy identifier to configure it. But now I need to go back to SQL Server 2005 ... and "just do it" ... Anyway, I thought about installing this simple way -

Id | ParentId | CompanyName | Desc

where ParentIdis the int field that will store the identifier of the parent. I suppose the root will have ParentIdas zero. Are there any better ways to customize the hierarchy structure?

I really do not have such complex requirements for the hierarchy ... I want to know what I would do, believing that moving around the hierarchy is easier and more efficient working with it.

+3
source share
3 answers

The "easy way" is good and works well with CTE (Common Table Expression). However, as Kev suggested, there are other ways that have their pros and cons.

Thus, ultimately, it depends on your exact requirements and how many nested and hierarchical data queries will be executed, since the performance of different approaches can vary greatly in this regard.

+1
source

The method of using nested sets by Joe Celko, in which the column "left" and "right" refers to the hierarchy, I usually saw how this is done

Joe Celco will probably explain it better than I can Nested sets

+1
source

, , , . , GetAncestor GetDescendant. - CTE GetAncestor GetDescendant .

Here is an example (using the menu hierarchy):

WITH MenuCTE(MenuKey, ParentMenuKey, MenuName) AS
(
-- Anchor Query
SELECT MenuKey, ParentMenuKey, MenuName FROM Menu WHERE MenuKey = 1
UNION ALL
-- Recursive Query
SELECT m.MenuKey, m.ParentMenuKey, m.MenuName FROM Menu m
INNER JOIN MenuCTE r ON m.ParentMenuKey = r.MenuKey
)
SELECT MenuKey, ParentMenuKey, MenuName FROM MenuCTE

This article should help (example from here):

http://www.infoq.com/news/2007/10/CTE

+1
source

Source: https://habr.com/ru/post/1768637/

More articles:


All Articles