Actually, this is not quite correct. Yes, CTEs are nothing more than references to teh source, much as are unindexed views. But it isn't always possible to change them - the restrictions are much the same as for inserting, deleting or updating through views without triggers, so it can't even be done in many cases where it is absolutely unambiguous what must be done to the base tables to achieve the required operation. It's annoying that these failures are not detected at parse time, as it seems perfectly possible to do that; but in fact they are all deteced at run time. And even when it's perfectly easy to see what has to be done, SQL Server may decide it will refuse to do it.

For example

create table t1 (a int primary key, b int not null);

create table t2 (a int primary key, b int not null);

go

--insert some stuff into the tables

with cte3 as (select t1.a from t1 LEFT JOIN t2 on t1.a=t2.a)

/*an amazingly pointlesscte definition! */

delete cte3 where a=3;is accepted happily by the parser but gets ann error message if you run it, claiming that the delete affects more than one base table; as only one base table contributes to the CTE that's clearly false, but the query will fail anyway for that imaginary reason. Of course it isn't possible to compute precisely what is in principle updatable and what is not, but it's certainly trivial to detect some of the obvious cases including a lot of useful ones, as well as useless esoterica like the simple example above, and a decent RDBMS would make the effort to (a) reject things that easily be seen to be invalid at parse time instead of at run time and (b) invest some effort in analysis to enable it to do obviously doable operations before rejecting them despite the ease of doing them.