SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations


Hierarchies on Steroids #2: A Replacement for Nested Sets Calculations

Author
Message
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 41128
Michael Meierruth (11/16/2012)
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.


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. :-D

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 41128
Jason-299789 (11/16/2012)
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.


"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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
lnoland
lnoland
Old Hand
Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)Old Hand (326 reputation)

Group: General Forum Members
Points: 326 Visits: 821
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 41128
CELKO (11/18/2012)

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.


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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40288 Visits: 38567
Jeff Moden (11/18/2012)
CELKO (11/18/2012)

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.


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.


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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)SSC Guru (88K reputation)

Group: General Forum Members
Points: 88288 Visits: 41128
lnoland (11/16/2012)
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


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.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Neha05
Neha05
Mr or Mrs. 500
Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)Mr or Mrs. 500 (524 reputation)

Group: General Forum Members
Points: 524 Visits: 60
Nice article.
SwePeso
SwePeso
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4147 Visits: 3433
Excellent 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.

//Peter


N 56°04'39.16"
E 12°55'05.25"
kathy.m.davis.ctr
kathy.m.davis.ctr
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 115
Thank you for the excellent information! Is there a way to order the personnel in each level by something besides EmployeeID? My user wants to specify the order of the personnel in each level. For example, in level 2, instead of ordering Lynne, Bob, Ken and Marge, he wants Marge, Bob, Lynne, Ken. He wants to do this for each level. Our table has a column UICOrder (int) where the user enters the order of how he wants the items in each level to appear. Your solution in this article works for me except for the ordering of the levels. Any assistance is greatly appreciated!

Thank you,

Kathy Davis

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