A script I posted on another thread in this forum illustrates why I like the layout and readability of CTEs.

The thread is at http://www.sqlservercentral.com/Forums/Topic470379-338-1.aspx

The script is:

;with

CTE1 (ID, Position, Val) as

(select id, numbers.number, substring(reverse(rtrim(clean)), numbers.number, 1)

from dbo.numbercleanb

inner join dbo.numbers

on numbers.number between 1 and len(clean)),

CTE2 (ID, Pos, Val) as

(select id, row_number() over (partition by id order by position), val

from cte1

where val like '[0-9]'),

CTE3 (ID, Number) as

(select id, sum(power(cast(10 as bigint), cast(pos-1 as bigint)) * cast(val as bigint))

from cte2

group by id)

select count(*)

from cte3

inner join dbo.numbercleanb

on cte3.id = numbercleanb.id

and cte3.number != numbercleanb.number

A "derived tables" version might look like:

select count(*)

from

(select id,

sum(power(cast(10 as bigint), cast(pos-1 as bigint)) * cast(val as bigint)) as number

from

(select id, row_number() over (partition by id order by position) as pos, val

from

(select id, numbers.number as position,

substring(reverse(rtrim(clean)), numbers.number, 1) as val

from dbo.numbercleanb

inner join dbo.numbers

on numbers.number between 1 and len(clean)) Sub1

where val like '[0-9]') Sub2

group by id) Sub3

inner join dbo.numbercleanb

on sub3.id = numbercleanb.id

and sub3.number != numbercleanb.number

The reasons I find the CTEs more readable are:

A) I don't have to keep increasing the indention as I increase the number of levels of subquery. This means I'm less likely to have to scroll left and right on the screen.

B) The column names in the derived tables are less visible than those in the CTEs.

C) If I need to debug or modify part of the query, the CTEs are self-contained and can be modified more modularly.

D) Each query, including the final/outer query, is all in one place. In the derived tables version, part of each subquery is above and part below, the inner subs. This is forced because I have to Select, From, Where for each subquery, and From comes before Where. In the CTEs, this isn't neccessary and it keeps each in one place.