• Sergiy (3/18/2013)


    Sean Lange (3/18/2013)


    This type of discussion comes up around here over and over. Should we use a subquery or a cte. In cases like this they almost always proven to produce 100% identical execution plans. So in the end it all boils down to preference. There is no right or wrong. It is simply two ways of saying the same thing.

    Exactly my point.

    Unlike Lynn said, using CTE has no preference comparing to using derived tables.

    +1.

    But CTE imposes a danger.

    CTE's are strictly in-memory objects. They do not use tempdb for storing excessive data.

    So, if you carelessly put too big data set into CTE and then use them in some hash join it could easily kill the server, even if tempdb has terabytes of free space to spare.

    Especially it's dangerous in multi-user environment.

    So a derived table in a FROM clause would have the same problem, wouldn't it.

    It would seem that a view would also have the same issue as well.