This is a good read:

https://devblogs.microsoft.com/azure...r-temp-tables/

And again - I'm not sure how CTE's materialize - if that's a memory object or a disk based stored object. And even with TEMP TABLES, which are TEMPDB "stored" - they can still reside in memory alone if small enough.