Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 T-SQL Enhancements Part - III


SQL Server 2008 T-SQL Enhancements Part - III

Author
Message
Rob-1134588
Rob-1134588
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 90
I tend to roll-my-own when it comes to Hierarchies.

Here's how I do it.

Say I have a list of Employees with a Key on ID

I then make a Employee_Hierarchy table then an Employee_SuperHierarchy table

Employee_Hierarchy contains the parent and child Employee Primary Keys and is a compound unique index.

Employee_SuperHierarchy contains the parent and child Employee Primary key and the Gap.
For every Employee row there's an Row in with Gap=0
It contains the expanded hierarchy so if (A) is the boss of (B) and (B) is the boss of (C) then
A, A, 0
B, B, 0
C, C, 0
A, B, 1
B, C, 1
A, C, 2

You can then loop until no more are inserted to build the full hierarchy by
INSERT INTO Employee_SuperHierarchy
(Parent, Child, Gap)
SELECT Parent.ParentID
, Child.ChildID
, Child.Gap + 1
FROM Employee_SuperHierarchy Child
INNER JOIN Employee_Hierarchy Parent
ON Child.ParentID = Parent.ChildID

Then you can easily run a query to find all the employees (or Bosses) of a person. Do make sure to exclude Gap = 0 (employs self/Self boss)

E.g. To find all the employees
SELECT Employee.*
FROM Employee Boss
INNER JOIN Employee_SuperHierarchy
ON Employee_SuperHierarchy.ParentID = Boss.ID
INNER JOIN Employee
ON Employee.ID = Employee_SuperHierarchy.ChildID
WHERE Boss.Name = 'Stalin'
Toby White
Toby White
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 639
Good article :-)
I used recursion in in 2000 and CTE in 2005, and always had the thought that while my solution seemed clever, the aftermath of dealing with it was always a pain. Moving nodes and children and/or getting all children direct and indirect or all ancestors direct or indirect etc. The implementation in 2008/R2, while not perfect has come a long way. I didn't realize it was implemented via CLR until reading the article, but that makes sense as it is calling methods and such.

I rated the article 5 stars, but my one complaint is that you squeezed UDT's into the picture after an already full article. UDT's might not fit by themselves into an article, but I think the Hierarchy implementation was quite enough for this article. I know, picky, picky, but that just my 2 cents worth of constructive criticism.

Regards,

Toby
Tonci Korsano
Tonci Korsano
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
Points: 19 Visits: 46
hi arshad,

we are already using the same logic you explained for sql server 2005.
we don't have sql server 2000, so we don't have to call a stored procedure recursively.
we have several sorts of multilevel network marketing implementation.
some companies which use similar business logic are amwa_ and for_v_rlivin_
this can be viewed as layers or tiers.
the whole hierarchy can be called trees, stars or simply networks.
in one of our cases, we are limited to a table with columns parent, tier2, tier3, ..., tier10.
i guess, i would need to rewrite ddl for this table with hierarchyid, but later this change will imply changing a lot sql logic already written. this can be done because this client has sql server 2008.
it looks simple, but any tiers, including parent column, can have multiple rows, and of course some tiers are plain null, except by parent column.
we have successfully drawn trees from these tables, for which the hardest case is this table with columns parent, tier2, tier3, ..., tier10.
one of these trees is in a flex front-end, and the other is an asp.net treeview as gui.
your article has given me plenty of insight about tree structure business logic, and i stopped by to say thank you.
my guess is that hierachyid is in our short-term future for database programming.

keep up your good work!!

best regards,

tonci korsano
SQLRNNR
SQLRNNR
SSC-Insane
SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)SSC-Insane (23K reputation)

Group: General Forum Members
Points: 23736 Visits: 18286
Nice article thanks. I like the compare and contrast between 2k5 and 2k8 that you demonstrated.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

mischa sandberg
mischa sandberg
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 11
Trying to see how hierarchyId beats storing a "path"-style key, and it's not coming to me: a string of delimited (possibly tagged) local names, like a file path, can be searched using "LIKE" in ways that are much like XPath or LDAP queries. With prefix compression in indices, you don't pay a heavy price in Btree depth on that; and clustering works nicely, too. What am I missing, here?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search