Sql-server – Are there advantages to using temporary tables over derived tables in SQL Server

derived-tablesperformancequery-performancesql serversql-server-2008temporary-tables

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;

Best Answer

@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!

Related Question