Erik Darling makes a comparison:
Perhaps one of the most exhausting parts of my job is disabusing developers of the notion that common table expressions hold some weight in gold over any other abstraction layer in SQL Server.
Think of it like this:
- Views are like a permanent home
- Common table expressions are like a mobile home
You can put equally terrible queries in either one and expect equally terrible results.
Read the whole thing for additional spicy analogies and similes.
I would say that I certainly do not disdain views, so much as I see them as a yellow flag. It’s really easy to go from helpful views to views nested in views nested in views like malevolent Matryoshka dolls. It’s harder to do that with common table expressions before that reptilian part of your brain kicks in and says that “hey, maybe this isn’t the greatest idea I’ve ever had.” Not that this will stop some people, admittedly…