Is derived table executed once or three times?

Question

Every time you make use of a derived table, that query is going to be executed. When using a CTE, that result set is pulled back once and only once within a single query.

Does the quote suggest that the following query will cause derived table to be executed three times ( once for each aggregate function’s call ):

  SELECT 
    AVG(OrdersPlaced),MAX(OrdersPlaced),MIN(OrdersPlaced)
  FROM (
    SELECT
        v.VendorID,
        v.[Name] AS VendorName,
        COUNT(*) AS OrdersPlaced
    FROM Purchasing.PurchaseOrderHeader AS poh
    INNER JOIN Purchasing.Vendor AS v ON poh.VendorID = v.VendorID
    GROUP BY v.VendorID, v.[Name]
  ) AS x

thanx

Answer 1

No that should be one pass, take a look at the execution plan

here is an example where something will run for every row in table table2

    select *,(select COUNT(*) from table1 t1 where t1.id <= t2.id) as Bla
     from table2 t2

Stuff like this with a running counts will fire for each row in the table2 table

Answer 2

Probably not, but it may spool the derived results so it only needs to access it once.

In this case, there should be no difference between a CTE and derived table.

Where is the quote from?

Answer 3

CTE or a nested (uncorrelated) subquery will generally have no different execution plan. Whether a CTE or a subquery is used has never had an effect on my intermediate queries being spooled.

With regard to the Tony Rogerson link - the explicit temp table performs better than the self-join to the CTE because it's indexed better - many times when you go beyond declarative SQL and start to anticipate the work process for the engine, you can get better results.

Sometimes, the benefit of a simpler and more maintainable query with many layered CTEs instead of a complex multi-temp-table process outweighs the performance benefits of a multi-table process. A CTE-based approach is a single SQL statement, which cannot be as quietly broken by a step being accidentally commented out or a schema changing.