Just a reminder about something that...

In the future, please provide the data in a readily consumable format so that we can test the code we provide as an answer.  See the first link in my...

There is no question that the trigger is adding time to the problem.  When you do a join of a million rows between two logical tables that probably spilled to...

With all the reading and analysis' that I've been doing based on Paul Randal's Wait Stats articles, I come to the conclusion that the issue is an IO subsystem...

The trigger is already in place and I can't just change the logic of inserting the deleted records when We already started inserting the inserted records.

The Diff is necessary...

I'll have to disagree with the stance you took (or at least the stance it looks like you took, I could be wrong) on "matching code".  I'm not talking about...

Unfortunately, the actual table has 200 columns and I can't change the structure 🙁

But regardless of the number of columns, I'm able to reproduce the issue with the example...

One of the problem with the function is this bit of code...

  (select * from inner_recur_cte for json path, root('downlines')) json_downlines
from
  inner_recur_cte;

A lot of people...

Hi Jeff Thanks for the article, very good and functional, as I understand every time that an employee changes parentId or there is a new one in the adjacent...

If you'd like to short-sheet Phil's good solution a bit, try this...

DECLARE  @Day TINYINT            =...

@JohnG69

How many columns are actually in the table we're talking about?

I could be wrong but I'm pretty sure that's not what the OP is talking about.  I believe he's talking about the front-end Developers creating the tables on the fly...

That's freaking amazing.  It's awesome to see real in depth analysis.  Thank you for doing this.

Shifting gears a bit, since the Nested Sets method has most of the...

Ok, so I commented the columns we spoke of out and ran your code.  I had to make minor repairs because I already had an Employee table with a PK...

• This reply was modified 6 years ago by Jeff Moden.

It's not going to work against the million node test table because of the following code...

  sum(iif(Hlevel=1,1,0)) lvl_1_count,
  sum(iif(Hlevel=2,1,0)) lvl_2_count,
  sum(iif(Hlevel=3,1,0)) lvl_3_count,
  sum(iif(Hlevel=4,1,0))...