Usman actually seems to be right. The avg row size of the intermediate CTE tables seems to have no real bearing on the plan. THe conversion to smallint/int of row_number...

Yes, I'm aware as I've already read that article and I rarely use rCTEs for that reason.

While you can use the few blessed aggregate function that support an over...

That's rather odd. I found it best not to mess with the CteStart derived table. Changing that seems to slow things down. For my use case which is comma delimited...

The collate binary, short row optimization seems to really only help on 2014. My guess is that the new estimator is much more clever (and faster). It seems...

That makes sense.

After playing with the new Lead variant, my conclusion is that it seems to be slower than the mister.magoo collate latin1_general_bin version. That seems to be the best...

Have you tried switching the memory optimized tally table durability to just schema? I read that a table will perform best in this mode and its pretty easy to add...

That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly 🙁

Sure understood. Here is an example script that shows what I want.

Basically, I want to only create an audit when a user changes data for the table "tableName". I...

Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries 🙁

I have a store procedure that is called with a...

While this article is a bit old I did want to comment on a trick that I've discovered to build an itvf for a xml splitter. This will perform better...