Are there advantages to using temporary tables over derived tables in SQL Server?

Question

I read the derived tables have better performance than temporary tables, but anyway many SQL Server developers prefer the second ones. Why? I must do queries with large data (millions records) and I want to be sure I am using the best choice.

CREATE TABLE A(
    id BIGINT IDENTITY(1,1) NOT NULL,
    field1 INT NOT NULL,
    field2 VARCHAR(50) NULL,
);

CREATE TABLE B(
    id INT IDENTITY(1,1) NOT NULL,
    field1 VARCHAR(10) NULL,
    field2 INT NULL
);

INSERT INTO A 
    (field1,field2)
VALUES 
    (1,'a'),(2,'b'),(3,'c'),(2,'d'),(5,'e'),
    (6,'f'),(7,'g'),(8,'h'),(9,'i'),(2,'j');

INSERT INTO B 
    (field1,field2)
VALUES 
    ('a',1),('b',2),('c',3),('d',4),('e',5),
    ('f',6),('g',7),('h',8),('i',9),('j',2),('k',3);

DECLARE @begin INT=0,@end INT=200;

Derived tables

/*derived tables*/
SELECT 
    C.id,C.field1,C.field2,C.field3 
FROM
(
    SELECT
        A.id,A.field1,A.field2,B.field2 AS field3, 
        ROW_NUMBER() OVER (ORDER BY A.id) AS iRow
    FROM 
        A INNER JOIN B ON A.field1=B.id
) C
WHERE iRow BETWEEN @begin AND @end;

Temporary tables

/*temporary tables*/
CREATE TABLE #C (
    iRow INT IDENTITY(1,1),
    id bigint,
    field1 INT,
    field2 VARCHAR(50),
    field3 INT );

INSERT INTO #C 
    (id,field1,field2,field3)
SELECT TOP 1000 
    A.id,A.field1,A.field2,B.field2 
FROM  
    A INNER JOIN B ON A.field1=B.id
ORDER BY 
    A.id;

SELECT id,field1,field2,field3 
FROM #C 
WHERE iRow BETWEEN @begin AND @end;

DROP TABLE #C;

Answer 1

@user16484 already directed you to Which one have better performance : Derived Tables or Temporary Tables in the comment.

Also see Temp Table 'vs' Table Variable 'vs' CTE. which also covers derived tables.

A quick summary: #temp tables can be indexed, can have UNIQUE indexes/constraints, can be references more than one time in the same query, can be referenced (FROM or JOIN) by more than one query. Derived tables can be referenced (FROM or JOIN) once in one query.

Performance-wise, pull out Profiler for SQL:BatchCompleted and RPC:Completed, watch the Read, Write, CPU, and Duration columns, and see what a few runs of derived tables vs. #temp tables vs. indexed #temp tables does for each particular query.

In general - if you're going to use it more than once, #temp table wins. If you're joining a lot of tables, #temp table probably wins. If you're joining only a few tables, derived table has a reasonable chance of winning. Benchmark it!

Answer 2

In general, it depends on your particular queries and the size of the temporary results.

For the specific scenario given, which is paging, temp tables are totally unnecessary. Why would you want to save 1000 rows into a temp table only to then return the 1st 200? Using a 'derived' table or a CTE in this scenario is much more efficient, because the full result set does not have to be stored anywhere, or in most cases even produced. For example when requesting the 1st page of 200 rows only the first 200 rows will have to be retrieved from the base tables (assuming existing indexes can support the sort order requested in the query).