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.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon