关于临时表和表变量的差别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：有时候如果使用表变量，中间缓存的诗句比较多(可能超过几百条的话，有可能影响后续的查询效率，这个时候可以试下使用临时表来替换，说不定效率马上上去了~)

       说得不好的地方，还请各位拍砖指导啊！