InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSun, 19 May 2013 01:37:37 GMT20http://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxExcellent article(s) Jeff! I emailed you to your ameritech adress last week wondering about when your "nested set killer" will be published. Sorry to say the email bounced so you will have to send me your new email.Otherwise, the only thing I missed was the heads up about the article being published! Kudos.//PeterSun, 20 Jan 2013 05:13:29 GMTSwePesohttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxNice article.Mon, 24 Dec 2012 12:03:19 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]lnoland (11/16/2012)[/b][hr]Jeff -Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.- Les[/quote]Thanks for the geat feedback, Les. I really appreciate it.If you do get a chance to apply some of these things to some of the items you have at work, I'd really be interested in finding out what you did and any problems you may have had to overcome. These post-article discussions are, many times, where the real innovation occurs.Sun, 18 Nov 2012 11:15:52 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Jeff Moden (11/18/2012)[/b][hr][quote][b]CELKO (11/18/2012)[/b][hr]When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all. Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.[/quote]There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. ;-) Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.[/quote]Jeff, the problem is Mr. Celko. He thrives on the unprofessional discourse that his on-line persona causes. I really wish we could just get him to go away.Sun, 18 Nov 2012 10:49:51 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]CELKO (11/18/2012)[/b][hr]When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all. Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port.[/quote]There you go again with the passive-agressive name calling, Joe. Please learn how to carry on in a conversation without resorting to such a thing especially since the "hillbilly dilect {sic}" you speak of just made Nested Sets mostly unnecessary in any "relational" database engine. ;-) Also, the reference to kids being able to "immediately see" suggests that anyone that doesn't is somehow less intelligent than a kid. Just stop it. It's not necessary and, although I can't speak for anyone else, it diminishes the high regard that I would otherwise have for your intelligence.Sun, 18 Nov 2012 10:03:40 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote]I'd read about nested sets some time ago but they looked too unwieldy to maintain. [/quote]It is funny, but old farts like me want to mimic the pointer chains we had in proceudral languages. So we use cursors (the recursive CTE is really a loop and hidden cursor) and other tricks to get us back to a familar mindset.When I teach kids who grew up with HTML, XML, etc, they immediately see the (lft, rgt) pairs as tags and have no trouble at all. Since the tree structure is separated from the nodes and the rows are short and clustered on (lft, rgt) in a covering index, performance is quite good. You use procedures to do the basic tree operations so you only code it once. If you do the DDL correctly, you will not get orphans and the other problems that plague adjacency list models. Here is what Jeff was doing in one statement, without any "hillbilly dilect" so it will port. The roll up total, tree level (root =1) and subtree size with simple aggregates and self-join. SELECT O1.emp_name, SUM(CASE WHEN O2.lft BETWEEN O1.lft AND O1.rgt THEN O2.sale_amt ELSE 0.00 END) AS sale_amt_tot, SUM(CASE WHEN O2.lft BETWEEN O1.lft AND O1.rgt THEN 1 ELSE 0 END) AS subtree_size, SUM(CASE WHEN O1.lft BETWEEN O2.lft AND O2.rgt THEN 1 ELSE 0 END) AS lvl FROM OrgChart AS O1, OrgChart AS O2 GROUP BY O1.emp_name;Sun, 18 Nov 2012 09:28:15 GMTCELKOhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxJeff -Thanks so much for the articles. I'd read about nested sets some time ago but they looked too unwieldy to maintain. The techniques you have used here look very useful and innovative. I have some items at work on which I would love to experiment using them if I can find the time.I have learned a great deal from your articles and comments since joining SQL Server Central. Thanks for sharing.- LesFri, 16 Nov 2012 10:17:43 GMTlnolandhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Jason-299789 (11/16/2012)[/b][hr]Jeff,As with the first article its top draw, though I'm still reading through them both and making the various links to previous experiences, I like the use of the rollup in the aggregation, a function that often gets overlooked. I'm going to try and use this to redesign the hierarchy builder I have to see if I can optimise the code.[/quote]"Impending usage" because of an article is one of the highest compliments someone like me could hope to have. Thank you very much, Jason. Let us know how it turns out.Fri, 16 Nov 2012 07:08:00 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Michael Meierruth (11/16/2012)[/b][hr]Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.[/quote]2:48 to be precise. :-) And Dwain has it spot on. I live in the area of Detroit. I like to get on the forums to relax a bit after work and work frequently has me up quite late.We ran into a major problem with a 3rd party upgrade to their software and I had to fix that, which took a good portion of the day. Then we ran into a privs problem with another 3rd party bit of software and trying to figure out what they really needed took a good while. Of course, they wanted "SA" privs and, of course, I'm hell bent on giving them only what I think they really need. I'll never give a 3rd party "SA" privs even on a dev box. After that, I had to fix the point-in-time backups on the dev and staging boxes (seems like overkill but I protect my developers' work as much as I can). We have more disk space on order but it's not in time for the sudden growth we experiennced because of several new clients and all the major DB backups simply ran out of room.Then, there are the normal daily questions and special requests that need to be handled. For example, I peer review 100% of the code that moves from dev to staging and do a final check when it moves from staging to production. The peer reviews are also time to "mentor" the developers in the art of writing good, high performance SQL and time for me to keep up with all the projects so I know what's coming up for the production databases.None of that means that I can avoid the work that I need to do on another time-sensitive project and I didn't get started on that until quite late in the day.Heh... so, yes... to answer the underlying question, I have a hell of a sleep deficit going on most weeks and I'm slightly insane. I need to learn from my own joke about the difference between being "loyal" to a company and being "dedicated". You have to think about "Ham'n'Eggs".... the chicken was loyal... the pig was dedicated. :-DFri, 16 Nov 2012 07:04:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxJeff,As with the first article its top draw, though I'm still reading through them both and making the various links to previous experiences, I like the use of the rollup in the aggregation, a function that often gets overlooked. I'm going to try and use this to redesign the hierarchy builder I have to see if I can optimise the code.Fri, 16 Nov 2012 01:27:55 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Michael Meierruth (11/16/2012)[/b][hr]Jeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.[/quote]I'm guessing he's somewhere around Detroit but we'll see.Fri, 16 Nov 2012 00:56:27 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxJeff, I was always under the impression that you are based somewhere in the New York area or at least the EDT time zone. Have you moved? Because there it would be 3 in the morning.Fri, 16 Nov 2012 00:53:15 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Michael Meierruth (11/16/2012)[/b][hr][quote][b]Jeff Moden (11/16/2012)[/b]... it was the only way I could teach myself how it would all work. :hehe: [/quote]Jeff, this is the first time I see someone use this 'terminology' as a motivating factor for writing comments. I wish more people would do it - especially in production code. Sometimes SQL code gets so convoluted when solving a hard problem that coming back to the code a year later makes you faint. And that's when you soak up the comments - and relax. I write a lot of comments like that (and not just in SQL code). Maybe I don't format it nicely like you do :-P[/quote]I'm right the with you, Michael. I comment even simple code. There's nothing worse than opening up, say, someone's "simple" bit of code only to find that they didn't even write a single line about what the overall purpose of the code is. Some folks say that the name of the code (proc, function, view, etc) should tell you what the code does but between some terrible naming and the need for speed when troubleshooting, well formed comments can really speed up the troubleshooting process.Same goes for the code itself. Some say that all you need to know can be found by reading the code. I've never seen code that explains what the business reason for each SELECT, INSERT, UPDATE, DELETE, or MERGE is. It can only be done with some simple but well formed comments. It's especially helpful when you're troubleshooting someone's "simple" 2,000 line stored procedure.Fri, 16 Nov 2012 00:48:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Jeff Moden (11/16/2012)[/b]... it was the only way I could teach myself how it would all work. :hehe: [/quote]Jeff, this is the first time I see someone use this 'terminology' as a motivating factor for writing comments. I wish more people would do it - especially in production code. Sometimes SQL code gets so convoluted when solving a hard problem that coming back to the code a year later makes you faint. And that's when you soak up the comments - and relax. I write a lot of comments like that (and not just in SQL code). Maybe I don't format it nicely like you do :-PFri, 16 Nov 2012 00:38:48 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]dwain.c (11/16/2012)[/b][hr]In any event, don't expect much from me this weekend as I'm going fishing. Need to update my avatar. [/quote]Break a rod and haul that baby in! :-D Get the gaff! Get the Gaff! {looks over the side at whats at the other end of the line}... Get the Gun! Get the Gun!Fri, 16 Nov 2012 00:07:42 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]SQLRNNR (11/15/2012)[/b][hr]Another fine piece Jeff.[/quote]Thanks, Jason. I really appreciate it.Fri, 16 Nov 2012 00:05:36 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Jeff Moden (11/15/2012)[/b][hr][quote][b]dwain.c (11/15/2012)[/b][hr]Jeff - Working through it now but its pretty deep and I expect it will take me awhile.Great explanations though!I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-D[/quote]Thanks, Dwain. I'd be interested in your "not quite a hierarchy" problem. Sounds interesting.Heh... nah. Not going to change my name. Too much paper work for us all. :-D[/quote]Seems you're up might late (or real early) today...Did I say "I'm going to?" I meant to say "I may." In any event, don't expect much from me this weekend as I'm going fishing. Need to update my avatar. :-DFri, 16 Nov 2012 00:03:40 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]JJ B (11/15/2012)[/b][hr]Wow. Both articles are awesome. Not only is your code well documented (as usual), but the charts were also extremely useful in helping to explain the text. Well done and thanks for taking the time to really explain the methods.[/quote]Long time no "see", JJ B. Thanks for stopping by and I appreciate the feedback.I have a secret to tell about the documentation and the charts... it was the only way I could teach myself how it would all work. :hehe: I also wanted to absolutely understand Adam's formulas so I could figure out the best way to use them and the only way I could do that was to draw some pretty pictures for myself. :-DFri, 16 Nov 2012 00:03:22 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]adrian.facio (11/15/2012)[/b][hr]Learned a lot.[/quote]Thanks, Adrian. That's actually the best kind of feedback. Glad I could help.Thu, 15 Nov 2012 23:59:18 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]dwain.c (11/15/2012)[/b][hr]Jeff - Working through it now but its pretty deep and I expect it will take me awhile.Great explanations though!I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-D[/quote]Thanks, Dwain. I'd be interested in your "not quite a hierarchy" problem. Sounds interesting.Heh... nah. Not going to change my name. Too much paper work for us all. :-DThu, 15 Nov 2012 23:57:56 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Michael Meierruth (11/15/2012)[/b][hr]They both start with J. He was close.:-)[/quote]4 Letters, too! :-PThu, 15 Nov 2012 23:54:11 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxJeff - Working through it now but its pretty deep and I expect it will take me awhile.Great explanations though!I'm going to try to see if I can adapt the approach to another problem I encountered recently that is "not quite" a hiearchy.Let me know if you decide to change your name to John. I have several article attributions to you I'll need to change.:-DThu, 15 Nov 2012 19:28:30 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxLearned a lot.Thu, 15 Nov 2012 15:36:20 GMTadrian.faciohttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxWow. Both articles are awesome. Not only is your code well documented (as usual), but the charts were also extremely useful in helping to explain the text. Well done and thanks for taking the time to really explain the methods.Thu, 15 Nov 2012 14:44:00 GMTJJ Bhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxAnother fine piece Jeff.Thu, 15 Nov 2012 14:06:00 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxThey both start with J. He was close.:-)Thu, 15 Nov 2012 11:21:09 GMTMichael Meierruthhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspx[quote][b]Theo Ekelmans (11/15/2012)[/b][hr]Another excellent article, John ! This is really usefull.Keep e'm coming :-)Theo (NL)[/quote]Thanks, Theo. (my name isn't "John", though ;-)).Thu, 15 Nov 2012 07:21:48 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxAnother excellent article, John ! This is really usefull.Keep e'm coming :-)Theo (NL)Thu, 15 Nov 2012 02:16:54 GMTTheo Ekelmanshttp://www.sqlservercentral.com/Forums/Topic1384987-203-1.aspxComments posted to this topic are about the item [B]<A HREF="/articles/T-SQL/94570/">Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations</A>[/B]Thu, 15 Nov 2012 00:03:44 GMTJeff Moden