Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Hierarchies on Steroids #1: Convert an Adjacency List to Nested Sets Rate Topic Display Mode Topic Options
Author
 Message
 Posted Saturday, August 24, 2013 11:57 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:13 AM Points: 42,042, Visits: 39,421
Post #1488139
 Posted Saturday, August 24, 2013 12:00 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:13 AM Points: 42,042, Visits: 39,421
 L' Eomot Inversé (8/24/2013)Hi Jeff, great article.I have one quibble: terminology; even an old relic like me knows that "best bower" and "small bower" have been superseded by "starboard bower" and "port bower" but I've never before heard or seen "right bower" and "left bower". BWAA-HAAA!!!! I always knew you were a salty old dog! Thanks for the feedback, Tom. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1488140
 Posted Monday, November 11, 2013 7:55 AM
 SSC Rookie Group: General Forum Members Last Login: Tuesday, December 17, 2013 8:15 AM Points: 31, Visits: 194
 this is my stab at nested set. i generate on every set of the table.i am looking to refactor and trim it down at some point.if this is any help to anyone enjoyor anyone wants to refactor it a bit more i would appreciate it`CREATE TABLE [dbo].[Organisation]( [OrganisationId] [int] IDENTITY(1,1) primary key NOT NULL, [ParentId] [int] NULL, [MinNest] [int] NULL, [MaxNest] [int] NULL, [Name] [varchar](50) NULL) ON [PRIMARY]GO/****** Object: StoredProcedure [dbo].[UpdateOrgHierarchy] Script Date: 11/11/2013 14:17:12 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[UpdateOrgHierarchy]ASBEGINbegin transactionSELECT top 1 *into #tmpfrom dbo.Organisation with (TABLOCKX);WITH orgCTE2 AS (SELECT OrganisationId, OrganisationId as CurrentOrgId, 1 as TotalFROM dbo.Organisation ewhere ParentId IS NULLUNION ALLSELECT parent.OrganisationId, e.OrganisationId as CurrentOrgId, 1 as TotalFROM dbo.Organisation eINNER JOIN orgCTE2 parent ON parent.CurrentOrgId = e.ParentId UNION ALLSELECT e.OrganisationId, e.OrganisationId as CurrentOrgId, 1 as TotalFROM dbo.Organisation eINNER JOIN orgCTE2 parent ON parent.CurrentOrgId = e.ParentId) , orgCTE3 AS (select OrganisationId, count(*) as [count]from (select distinct OrganisationId, CurrentOrgId from orgCTE2) awhere Organisationid != CurrentOrgIdgroup by OrganisationId) , orgCTE AS (SELECT OrganisationId as TopOrgId, OrganisationId as OrganisationId, ParentId as ParentId, 1 as depthFROM dbo.OrganisationUNION ALLSELECT child.TopOrgId, e.OrganisationId as OrganisationId, e.ParentId, depth + 1 as depthFROM dbo.Organisation eINNER JOIN orgCTE child ON child.parentId = e.OrganisationId), orgCTE4 AS (SELECT OrganisationId, ParentId, cast(null as INT) as parentOrganisationId, OrganisationId as superparentOrganisationId, ',' + cast(OrganisationId as varchar(max)) + ',' as idstack, 1 as depthFROM dbo.OrganisationWHERE parentid IS NULLUNION ALLSELECT e.OrganisationId, e.ParentId, parent.organisationid as parentOrganisationId, superparentOrganisationId, idstack + cast(e.OrganisationId as varchar(max)) + ',' as idstack, depth + 1 as depthFROM dbo.Organisation eINNER JOIN orgCTE4 parent ON parent.organisationid = e.parentId)UPDATE orgset minNest = ((row_num-1) * 2) + 1 - (d-1), maxNest = ((row_num-1) * 2) + 1 - (d-1)+(cnn*2)-1 from dbo.Organisation orgjoin (select c.*, e.maxdepth - c.depth + 1 as dep, isnull(par.[count] + 1,1) cnn, st.depth as d, row_number() OVER (PARTITION BY 1 ORDER BY idstack) as row_numfrom orgCTE cjoin orgCTE4 ston c.OrganisationId = st.OrganisationIdLEFT JOIN orgCTE3 paron c.OrganisationId = par.OrganisationIdJOIN (select max(depth) maxdepth, toporgidfrom orgCTE c group by toporgid ) eon c.toporgid = e.toporgidwhere c.depth = 1) lnon org.OrganisationId = ln.OrganisationIdCOMMIT transactionEND`
Post #1513144
 Posted Tuesday, January 21, 2014 2:07 AM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, January 22, 2014 11:01 PM Points: 1, Visits: 2
Post #1532907
 Posted Thursday, January 23, 2014 8:05 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:13 AM Points: 42,042, Visits: 39,421
 @Dinson,If done correctly, there won't be any slowdown in the DB. The example I used was a million row example and it all worked in under a minute.The key is to establish the rules early. For example, can a document exist under more than one category? To be honest, I'd make category (or categories) an attribute (or attributes) of the document rather than storing categories in a hierarchy of locations. In all honesty, I can see two hierarchies here... one for location and one for categorization. Document IDs shouldn't be stored as a subset of categories. I'd have a document table and a bridging table that contains document IDs and the IDs of the categories that a document belonged to and there would be nothing hierarchical about that. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1534320
 Posted Wednesday, January 29, 2014 11:07 PM
 Forum Newbie Group: General Forum Members Last Login: Wednesday, January 29, 2014 11:02 PM Points: 1, Visits: 2
 thnx jeff.OK, that makes sense. So, i leave category out of the document table.I will try to set the bower calculations as a trigger on insert/update...Does that sound ok?
Post #1536169
 Posted Friday, July 18, 2014 6:02 PM
 Forum Newbie Group: General Forum Members Last Login: Tuesday, August 12, 2014 11:48 AM Points: 5, Visits: 44
Post #1594276
 Posted Saturday, July 19, 2014 12:45 PM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:13 AM Points: 42,042, Visits: 39,421
 @Jeztagab,You're problem doesn't appear to be hierarchical in nature. For that reason, I'd prefer to not dilute the discussion on this thread to something off topic. Would you repost your question on one of the T-SQL related forums on this site, please. I'm sure a ton of people would be happy to help especially if you take the time to read the first link in my signature line below.Thanks. --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." Helpful Links:How to post code problemsHow to post performance problems
Post #1594335
 Posted Friday, September 19, 2014 7:26 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, April 10, 2015 1:21 PM Points: 3, Visits: 45
 Jeff,This was timely as we are working on a tree just like this. I'm curious what you think about replacing the SortPath with a hierarchyid data type?Glenn
Post #1615316
 Posted Friday, September 19, 2014 8:32 AM
 SSC-Forever Group: General Forum Members Last Login: Today @ 9:13 AM Points: 42,042, Visits: 39,421