一直以來大家對臨時表與表變量的孰優孰劣爭論頗多,一些技術群里的朋友甚至認為表變量幾乎一無是處,比如無統計信息,不支持事務等等.但事實并非如此.這里我就臨時表與表變量做個對比,對于大多數人不理解或是有歧義的地方進行詳細說明.

注:這里只討論一般臨時表,對全局臨時表不做闡述.

生命周期

臨時表:會話中,PRoc中,或使用顯式drop

表變量:batch中

這里用簡單的code說明表變量作用域

DECLARE @t TABLE(i int) ----定義表變量@tSELECT *FROM @t        -----訪問OKinsert into @t select 1 -----插入數據OKselect * from  @t      -------訪問OKgo                     -------結束批處理select * from @t       -------不在作用域出錯

注意:雖然說sqlserver在定義表變量完成前不允許你使用定義的變量.但注意下面情況仍然可正常運行!

if 'a'='b'beginDECLARE @t TABLE(i int)endSELECT *FROM @t        -----仍然可以訪問！

日志機制

臨時表與表變量都會記錄在tempdb中記錄日志

不同的是臨時表的活動日志在事務完成前是不能截斷的.

這里應注意的是由于表變量不支持truncate,所以完全清空對象結果集時臨時表有明顯優勢,而表變量只能delete

事務支持

臨時表:支持

表變量:不支持

我們通過簡單的實例加以說明

create table #t (i int)declare @t table(i int)BEGIN TRAN tttinsert into #t select 1insert into @t select 1SELECT * FROM #t  ------returns 1 rowsSELECT * FROM @t  ------returns 1 rowsROLLBACK tran tttSELECT * FROM #t    -------no rowsSELECT * FROM @t    -------still 1 rowsdrop table #t       ----no use drop @t in session

鎖機制(select)

臨時表 會對相關對象加IS(意向共享)鎖

表變量 會對相關對象加SCH-S(架構共享)鎖(相當于加了nolock hint) 

可以看出雖說鎖的影響范圍不同,但由于作用域都只是會話或是batch中,臨時表的IS鎖雖說兼容性不如表變量的SCH-S但絕大多數情況基本無影響.

感興趣的朋友可以用TF1200測試

索引支持

臨時表 支持

表變量 條件支持(僅SQL2014)

沒錯,在sql2014中你可以在創建表的同時創建索引 圖1-1

注:在sql2014之前表變量只支持創建一個默認的唯一性約束code

DECLARE @t TABLE (col1 int index inx_1 CLUSTERED, col2 int  index index_2 NONCLUSTERED,       index index_3 NONCLUSTERED(col1,col2))