I've lost the old link that I had about this and, if I still had it, it probably wouldn't work anymore anyway. A lot of people don't know that Temp Tables start off in memory and "spill to disk" only if they reach a certain size. I have no idea what that size is but suspect it's related to the size of TempDB and the amount of memory available to SQL Server.
I also agree with Greg... there was a whole lot of bad information that were based on MS "recommendations".
And I believe Sean hit the nail quite squarely on the head. I was looking at the new feedback site where people suggested the addition of an "ordinal position" of the split elements being returned (like they should have done from the git but there's that half-baked thing again). They've implemented the change is Azure and decided to close the related "tickets" on the feedback site saying that they'll get to the on-prem stuff. My feeling is that it'll be a long time coming before we see it on-prem. 🙁 I won't get into the rest of the half-baked stuff released in the last decade nor the stuff that needs a fix (REORGANIZE, REBUILD, etc, etc) and has needed a fix for more than 2 decades.
Also, I don't know if anyone has seen the following...
I really feel bad for any early adopters of that future missing functionality. In "The Path Forward" section of that article, they explain how they're adding new capabilities in 2022... yeah.... based on current and previous history, that scares the crap out of me. I can just envision a whole lot of people heading for the new shiny objects and then maybe "poof" those will disappear in a similar fashion. Heh... long live CROSSTABs (PIVOT was another half-baked feature that's never been brought up to snuff. The PIVOT in ACCESS is better!). 😀