Correct, an inlined view - which again if you are referencing the view multiple times in the query, I would again offer a temp table as a likely performance improvement.

edit: I say this from experience fixing hundreds of CTEs that came down to "I want to simplify the downstream query so I am going to package a heck ton of relations in this cte and combine it with this other one to hide the complexity as I do some other stuff!"

> Are CTEs an optimization barrier in MSSQL?

They are not. They are, however, in Postgres.

They are not (or at least less likely to be) in Postgres 12, as they are inlined in a wide variety of cases.

Not anymore since PostgreSQL 12 was released yesterday.