Which one have better performance : Derived Tables or Temporary Tables

performance, sql, sql-server, t-sql

Solution

Derived table is a logical construct.

It may be stored in the `tempdb`, built at runtime by reevaluating the underlying statement each time it is accessed, or even optimized out at all.

Temporary table is a physical construct. It is a table in `tempdb` that is created and populated with the values.

Which one is better depends on the query they are used in, the statement that is used to derive a table, and many other factors.

For instance, `CTE` (common table expressions) in `SQL Server` can (and most probably will) be reevaluated each time they are used. This query:

WITH    q (uuid) AS
        (
        SELECT  NEWID()
        )
SELECT  *
FROM    q
UNION ALL
SELECT  *
FROM    q

will most probably yield two different `NEWID()`'s.

In this case, a temporary table should be used since it guarantees that its values persist.

On the other hand, this query:

SELECT  *
FROM    (
        SELECT  *, ROW_NUMBER() OVER (ORDER BY id) AS rn
        FROM    master
        ) q
WHERE   rn BETWEEN 80 AND 100

is better with a derived table, because using a temporary table will require fetching all values from `master`, while this solution will just scan the first `100` records using the index on `id`.

Problem

Original source