Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by Jeff Moden » Hierarchies on Steroids #1: Convert an...

62 posts, Page 7 of 7

«««3 4 5 6 7

Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets

Rate Topic

Display Mode

Topic Options

Author

Message

Jeff Moden

Posted Thursday, March 14, 2013 5:19 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 32,903, Visits: 26,784

Thanks for the feedback. Basically, great minds think alike because all I did was make a "mapping" table. You could certainly incorporate that into your original employee table but I didn't want to lock the whole table with the update/table scan that would occur. I didn't know how big your table would be.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1431301

LordHz

Posted Thursday, March 14, 2013 6:59 PM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, March 14, 2013 6:51 PM
Points: 4, Visits: 12

Jeff,

Not putting that back in the original table (mines not employee), I'm just maintaining the mapping in your hierarchy table. My thought is to make your stored procedure more generic, so I can simply pass in the table name, Id/ParentId and the sort field(s) and use dynamic SQL to create a the sorted mapping table. That would then update the rgt/lft extends (your bowers) back into the original table. That way the less knowledgeable developers could implement without having to modify the stored procedure. At least that's my thought, haven't implemented it yet. Most of our hierarchies are in the under 100k row range, most under 10k. We do a lot of hierarchies, product, geo, object, person, etc. This update has provided some great brain food. BigGrin

Thanks again,
Matthew

Post #1431317

« Prev Topic | Next Topic »

62 posts, Page 7 of 7

«««3 4 5 6 7

Permissions