關於臨時表和表變數的差別1

臨時表，表變數，這2個兄弟在平時使用的時候並不會陌生。很多時候我們都借用這2兄弟來進行一下中間結果集的緩存之類的功能。那我就簡單說下這2兄弟在查詢時候的一些小區別

1、首先我建立了一個表，存放100W的數據

--數據簡單，但是每一行都飽滿~
CREATE TABLE Tmp (ID INT PRIMARY KEY,Col1 CHAR(8000))


INSERT INTO dbo.Tmp
        ( ID, Col1 )
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ROW,'a'
FROM sys.objects a,sys.objects b,sys.objects c

2、然後創建臨時表和表變數分別查詢，不多，就查1000條

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @Tb AS TABLE(ID INT)

IF OBJECT_ID('tempdb..#Tmp') IS NOT NULL
    DROP TABLE #Tmp

;WITH CTE AS 
(
    SELECT 1 AS row
    UNION ALL
    SELECT row + 1
        FROM CTE
    WHERE row < 100
)
SELECT TOP 1000 CHECKSUM(NEWID())%1000000 AS row
    INTO #Tmp
    FROM CTE , CTE v

INSERT INTO @Tb
        ( ID )
SELECT TOP 1000 CHECKSUM(NEWID())%1000000
    FROM #Tmp

DECLARE @DT DATETIME=GETDATE()
SELECT *
    FROM dbo.Tmp a
        WHERE EXISTS(SELECT * FROM @Tb WHERE ID = a.ID)
SELECT DATEDIFF(ms,@DT,GETDATE())
SELECT *
    FROM dbo.Tmp a
        WHERE EXISTS(SELECT * FROM #Tmp WHERE row = a.ID)
SELECT DATEDIFF(ms,@DT,GETDATE())

然後結果是使用臨時表執行時間大概是使用表變數的短了一半！！

我就看了一下執行計劃發現執行計劃裡面顯示查詢開銷表變數占用的比例更少啊！！！

明明執行計劃是這樣紙的，為什麼我看到的執行時間和評估的計劃不一致呢！！這就是我和明明之間的差距_(:з」∠)_

然後我又看了一下IO輸出，這才恍然大悟，表變數的讀取引起了900多次的物理讀！而臨時表的寫法物理讀取只有1次，所以執行時間上面就不一樣了！

--表變數方式
表 'Tmp'。掃描計數 0，邏輯讀取 4000 次，物理讀取 968 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 '#A5842A1D'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。


--臨時表方式
表 'Tmp'。掃描計數 0，邏輯讀取 6402 次，物理讀取 1 次，預讀 8040 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。
表 '#Tmp________________________________________________________________________________________________________________000000000018'。掃描計數 1，邏輯讀取 2 次，物理讀取 0 次，預讀 0 次，lob 邏輯讀取 0 次，lob 物理讀取 0 次，lob 預讀 0 次。

PS：有時候如果使用表變數，中間緩存的詩句比較多(可能超過幾百條的話，有可能影響後續的查詢效率，這個時候可以試下使用臨時表來替換，說不定效率馬上上去了~)

       說得不好的地方，還請各位拍磚指導啊！