Good Lord! I've got more testing to do but I ran a simple "Tally CTE Function" test in 2017 and 2022 on my laptop. The results are REALLY disappointing.
I asked Ed Wagner to run similar tests on his servers and it turns out this rather serious downturn in performance started in SQL Server 2019. What the hell did Microsoft do?
And, just in case you're wondering, I turned off Query Store on the 2022 instance.
I've tried turning various things on the 2022 box off and on and no difference.
Between that and the GENERATE_SERIES() defeating Minimal Logging (article coming out on that on the 18th) and the DATETIME rounding issues into the next day that started in 2016 (just touched on that in the "Combined DATE and TIME Columns" article that came out about a month ago), you've just gotta wonder if MS actually has anyone that actually knows how to test for both accuracy and performance.
Just imagine all the function code that people have made over the that uses some form of sequential number generation within the function because it took MS more than 2 decades to finally see the light! Lordy!