• John Corkett MBCS (1/26/2015)


    Hi,

    I was wondering, would you say that this is the same as using a view in a query? The view is in effect a subquery. If you have tables that you need to join frequently in queries, is it better to make a view out of them and then use the view in the query. Wouln't this make the query more readable (if the view has a sensible name)?

    The consensus here seems to be that CTEs are better than using subqueries as tables. Please can someone explain why?

    Thanks,

    John

    John,

    The use of a view should rightly be reserved for their proper use, which is generally to take a complex table relationship and allow it to be used like a table, and/or present a limited subset of things to help protect a set of base tables, as part of either a reporting set up, or as a participating method in an overall security mechanism. The use of CTEs is generally a good idea when separate, complex elements must be JOINed. A CTE is, effectively, an in-line view. The optimizer often benefits from this kind of query construction because it can look across the final query's JOINs and determine a better query plan by limiting each CTE element based on those JOINs, whereas a sequence of sub-queries rarely benefits from this kind of optimization.

    EDIT: No element of the original query was sufficiently complex to justify the use of a CTE, as even the sub-queries weren't necessary.

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)