• Tao Klerks (3/18/2008)


    ...My instinct would be: Try to rewrite your statement to use joins and derived tables rather than subqueries, before moving to CTEs as a way of simplifying your subqueries.

    Does anyone know better one way or the other?

    CTEs work pretty much the same way as derived tables. They have the advantage of "build once, reference many", but in cases where you just use the derived table once, a CTE and a derived table are essentially the same, and will resolve the same way in the query engine.

    In those cases, I currently use CTEs instead of derived tables, because I find they make for more readable code. The final select statement isn't as cluttered. No performance reason, just easier to read.

    Also, since I am using CTEs in the places where they have significant advantages over derived tables (self-reference, multi-reference, query-of-query), using them in other places is more consistent, and that helps me to set and maintain a standard.

    - 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