Thanks for the detailed response. Agreed, could have made this example more performant following some of the guidance you mention.
Regarding why there is no index, the row set in my case was small (5 rows only). This is why I chose a table variable instead of temp table. The optimizer won't even use the index if it were added. I agree if the list of companies was larger, it would be better to use a temporary table with a clustered index.
It's amazing what the optimizer might chose to do if you give it a choice. A triangular join (or lookup) is still 15 rows on 5 rows. Why waste clock cycles? The other thing is that you actually have zero control over how other might use your code especially if they in a pinch and can't figure something out. Since it's so easy to do the right thing even when RBAR is required, why not do it all the time? 😉
As for people that rail against the use of GOTO, I agree that it goes against the nature of "object oriented programming" and "functional programming", etc, etc. Of course, many people will still make the mistake of using DELETE on this type of thing and still others will make stored procedure calls 8 freakin' levels deep and somehow think that's better than using GOTO. 😀
My take on GOTO is that, just like While Loop, Incremental rCTEs, function recursion through self reference of a scalar function, real or implied triangular or full square joins, everything has a purpose. Well, except for that damned PIVOT operator and the awful FORMAT function. 😀 And I can guarantee that almost everyone railing against GOTO thinks that REORGANIZE is useful and resource friendly.
I have to admit that I disagree with Dijkstra. He says it should be eliminated from all "higher-level languages" and makes the exception for machine language. He also states that GOTO is just "too primitive" and I think that's probably the real reason why others take exception... it "feels" too primitive to them. While I certainly don't embrace the regular use of GOTO even as a possible replacement for WHILE loops, it does have a place when you DO need that "primitive" functionality. That means that Dijkstra was entirely correct in saying that you can judge the quality of a Developer as an inversely proportional function of the number of GOTOs in their code... but so it is true of a great many other things. 😀
Of course, the same is frequently true for While loops, incremental rCTEs, recursive function calls, the number of levels of stored procedures called, and several dozen other things. 😀 People also say to "go procedural if you have to". I find they don't spend enough time understanding that most of the time they say they "have to", they really don't.
An extreme example of that is when converting Adjacency List Hierarchies to Nested Sets. And the While loops that most people wrote for the old "push stack method" isn't any better than someone using GOTOs. It's just a different way of committing the same sin. 😀
As with all else, "It Depends".
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)