在 SQL Server 中使用臨時表比派生表有優勢嗎？

我讀到派生表比臨時表具有更好的性能，但無論如何許多 SQL Server 開發人員更喜歡第二個。為什麼？我必須對大數據（數百萬條記錄）進行查詢，並且我想確保我使用的是最佳選擇。

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*/
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*/
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;

@user16484 已經將您定向到哪個性能更好：評論中的派生表或臨時表。

另請參閱臨時表“vs”表變數“vs”CTE。這也涵蓋了派生表。

快速總結：#temp 表可以被索引，可以有 UNIQUE 索引/約束，可以在同一個查詢中被多次引用，可以被多個查詢引用（FROM 或 JOIN）。派生表可以在一次查詢中被引用（FROM 或 JOIN）一次。

性能方面，為 SQL:BatchCompleted 和 RPC:Completed 拉出 Profiler，觀察 Read、Write、CPU 和 Duration 列，並查看派生表與 #temp 表與索引 #temp 表的幾次執行每個特定的查詢。

一般來說 - 如果您要多次使用它，#temp 表會獲勝。如果您要加入很多表，#temp table 可能會贏。如果您只加入幾張牌桌，派生牌桌就有合理的獲勝機會。對其進行基準測試！

通常，這取決於您的特定查詢和臨時結果的大小。

對於給定的特定場景，即分頁，臨時表是完全沒有必要的。為什麼您只想將 1000 行保存到臨時表中，然後返回第 200 行？在這種情況下使用“派生”表或 CTE 效率更高，因為不必將完整的結果集儲存在任何地方，或者在大多數情況下甚至不需要生成。例如，當請求 200 行的第一頁時，只需從基表中檢索前 200 行（假設現有索引可以支持查詢中請求的排序順序）。

引用自：https://dba.stackexchange.com/questions/58659