參考鏈接：一、SQLServer表變量和臨時表系列之概念篇/articles/68288?spm=a2c4e.11153940.blogcont68898.20.11b5722bQUdorM二、SQLServer臨時表和變量系列之對比篇/articles/68898?spm=a2c4e.11153940.blogcont69098.20.15f251cfX3amSn三、SQLServer臨時表和表變量系列之認知誤區(qū)篇/articles/69098?spm=a2c4e.11153940.blogcont68898.21.11b5722bQUdorM四、SQLServer臨時表和表變量系列之選擇篇/articles/69187五、SQLServer臨時表和表變量系列之踢館篇/articles/69217?spm=a2c4e.11153940.blogcont69187.23.7ad16a7dQw81OQ1、臨時表和表變量對比我們從以下幾個方面來對臨時表和表變量進行對比：創(chuàng)建和析構(gòu)方式存儲方式作用域?qū)κ聞盏闹С中阅苡绊?.1、創(chuàng)建和析構(gòu)方式臨時表和表變量在創(chuàng)建和析構(gòu)方式上是完全不一樣的，在這一節(jié)，我們會從以下幾點來看看他們的不同。結(jié)構(gòu)定義索引創(chuàng)建DDL析構(gòu)方式1.1.1、結(jié)構(gòu)定義臨時表的定義方式是CREATETABLE，而表變量的定義方式是DECLARETABLE，在此我們不再過多的累述。讓我們重溫代碼既可：IFOBJECT_ID('tempdb..#tmp1','U')ISNOTNULLDROPTABLE#tmp1GO--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULL,HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLCONSTRAINTDF_FDateDEFAULT(GETDATE()),CONSTRAINTCK_WidthCHECK(Width>0.0));--創(chuàng)建表變量DECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));1.1.2、索引創(chuàng)建臨時表和表變量關于索引的創(chuàng)建方式是完全不一樣的。臨時表可以定義表結(jié)構(gòu)之中或之后創(chuàng)建索引，而表變量只能在定義的過程中創(chuàng)建（只適用于SQLSERVER2014及以上版本，以下版本不支持創(chuàng)建索引）。如果在表變量定義的語句之后，創(chuàng)建索引會報錯。代碼如下：--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULL,HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLCONSTRAINTDF_FDateDEFAULT(GETDATE()),CONSTRAINTCK_WidthCHECK(Width>0.0));--創(chuàng)建表變量DECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));--臨時表定義索引CREATEINDEXIX_FNameON#tmp1(FName);GO--表變量定義索引CREATEINDEXIX_FNameON@tmp2(FName);執(zhí)行結(jié)果如下：臨時表索引創(chuàng)建成功，表變量創(chuàng)建索引語法錯誤（不支持該語法）1.1.3、DDL（數(shù)據(jù)庫結(jié)構(gòu)定義操作）關于臨時表和表變量的DDL操作，與索引創(chuàng)建類似。臨時表可以在定義以后，進行任何的DDL操作。而表變量在完成定義以后，是完全禁止DDL操作的。假設我們需要為臨時表加上一個新的字段ModifiedDate時間字段，同時我們也嘗試為表變量添加這個字段定義。代碼如下：--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(4,2)NOTNULL,HeightDECIMAL(4,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLCONSTRAINTDF_FDateDEFAULT(GETDATE()),CONSTRAINTCK_WidthCHECK(Width>0.0));--創(chuàng)建表變量DECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(4,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(4,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));--臨時表加字段ALTERTABLE#tmp1ADDModifiedDateDATETIMENULLGO--表變量加字段ALTERTABLE@tmp2ADDModifiedDateDATETIMENULLGO執(zhí)行結(jié)果如下：臨時表加字段成功，表變量報語法錯誤1.1.4、析構(gòu)方式臨時表和表變量創(chuàng)建成功，使用完畢以后，系統(tǒng)需要回收這部分資源。在析構(gòu)方式上也存在差異。針對表變量，SQLServer析構(gòu)的方式比較簡單，在表變量所在的批處理結(jié)束后，系統(tǒng)會自動回收資源，無需任何的人為干預。而對于臨時表相對比較復雜一些，這里需要分為全局臨時表和局部臨時表:全局臨時表：以##開頭的臨時表稱為全局臨時表，此類型的臨時表對所有進程可見，此類型的臨時表生命周期是所有使用到全局臨時表的連接完全斷開后，臨時表資源被系統(tǒng)自動回收；當然我們也可以手動析構(gòu)臨時表，方法是使用DROPTABLE語句。局部臨時表：以#開頭的臨時表稱為局部臨時表，這種類型的臨時表僅當前進程可見，其他進程不可訪問，生命周期是隨著當前連接進程的關閉而消亡，臨時表資源被系統(tǒng)自動回收；相同道理，我們同樣可以使用DROPTABLE語句手動回收。注意：如果臨時表（無論全局還是局部臨時表）中存放有大量記錄數(shù)（比如超過10萬條記錄數(shù)），手動回收臨時表資源前，強烈建議使用TRUNCATETABLE不記錄日志的方式刪除臨時表所有數(shù)據(jù)，以防止臨時表直接DROP操作給tempdb日志文件帶來寫入壓力。代碼如下：IFOBJECT_ID('tempdb..#tmp1','U')ISNOTNULLBEGIN --加truncate不記錄日志，給tempdb日志文件減少寫入壓力TRUNCATETABLE#tmp1DROPTABLE#tmp1END1.2、存儲方式時常在一些圖書和網(wǎng)絡上的文章看到很多人都會說臨時表是存儲在磁盤上，而表變量是存儲在內(nèi)存中。這種說法太過武斷，并且是錯誤的。這一節(jié)，我們會從兩個方面來討論臨時表和表變量的存儲方式，通過這一節(jié)，你就知道這個觀點錯在那里了。結(jié)構(gòu)定義信息數(shù)據(jù)存儲1.2.1、結(jié)構(gòu)定義信息結(jié)構(gòu)定義信息是指創(chuàng)建臨時表或表變量結(jié)構(gòu)的定義信息，比如：約束、索引、表結(jié)構(gòu)等。到底這些定義信息存放在哪里？或者說我們?nèi)绾潍@取臨時表和表變量的結(jié)構(gòu)定義信息呢？臨時表：在臨時表創(chuàng)建完畢后，我們使用下面的語句可以查看：--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULL,HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULL);go--查詢tempdb數(shù)據(jù)庫USEtempdbGO;WITHDATAAS(SELECT*FROMsys.objectsWHEREobject_id=object_id('#tmp1')UNIONALLSELECT*FROMsys.objectsWHEREparent_object_id=object_id('#tmp1'))SELECTparent_object=OBJECT_NAME(parent_object_id),name,type_desc,create_dateFROMDATAORDERBYcreate_dateDESC執(zhí)行結(jié)果如下：這里有一個非常有趣的問題需要思考下：在創(chuàng)建臨時表的定義語句中，我們的臨時表名字明明是#tmp1，為什么這里卻變成了#tmp1__0000000000F4呢？答案會在“作用域”這一節(jié)來揭曉。表變量：那讓我們來看看如何獲取表變量的定義，為了看到測試效果，這里特意將當前數(shù)據(jù)修改到master數(shù)據(jù)庫下，然后從Tempdb下去獲取表變量的定義信息（因為在其他數(shù)據(jù)庫創(chuàng)建表變量，Tempdb無法獲取到表變量定義信息，具體原因我也不知道）：代碼如下：USEmasterGODECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));SELECTTOP7current_db=db_name(),name,parent_name=(SELECTTOP1nameFROMtempdb.sys.all_objectsWHEREobject_id=A.parent_object_id),type_desc,create_date,[current_date]=getdate()FROMtempdb.sys.all_objectsASAORDERBYcreate_dateDESC從執(zhí)行結(jié)果來看，我們成功的從Tempdb下獲取到了表變量的定義信息如下：1.2.2、數(shù)據(jù)存儲臨時表和表變量的數(shù)據(jù)又是存放在哪里的呢？這一小節(jié)，我們要探討這個問題。臨時表：tempdb數(shù)據(jù)增長：首先我們來看看臨時表中的數(shù)據(jù)的存儲位置。我們在SSMS中開啟一個連接，執(zhí)行以下語句：USEtempdbGOSELECTdatabase_name=db_name(),name,type_desc,size=size/128.FROMsys.database_files執(zhí)行結(jié)果如下：然后我們重新開啟另外一個連接，執(zhí)行下面的語句：IFOBJECT_ID('tempdb..#tb_dataLocation','U')ISNOTNULLDROPTABLE#tb_dataLocationGOCREATETABLE#tb_dataLocation(bigCharchar(8000));DECLARE@doint=1;WHILE@do<=10000BEGININSERTINTO#tb_dataLocationVALUES(REPLICATE('A',8000));SET@do=@do+1;ENDUSEtempdbGOSELECTOBJECT_NAME=object_name(object_id),reserved_size=SUM(reserved_page_count)/128.,used_pages=SUM(used_page_count),pages=SUM(CASEWHENindex_id<2THENin_row_data_page_count+lob_used_page_count+row_overflow_used_page_countELSElob_used_page_count+row_overflow_used_page_countEND),row_count=SUM(CASEWHENindex_id<2THENrow_countELSE0END)FROMtempdb.sys.dm_db_partition_statsWITH(NOLOCK)WHEREobject_id=object_id('#tb_dataLocation','U')GROUPBYobject_id從第二個連接執(zhí)行結(jié)果來看，我們往臨時表#tb_dataLocation中插入了10000條數(shù)據(jù)，總共占用了78.19MB空間。然后，我們再查下數(shù)據(jù)庫文件增長情況，執(zhí)行語句如下：USEtempdbGOSELECTdatabase_name=db_name(),name,type_desc,size=size/128.FROMsys.database_files執(zhí)行結(jié)果如下：tempdb數(shù)據(jù)庫數(shù)據(jù)文件增長了136MB-8MB=128MB，這個數(shù)字大小也符合我們的設想（因為我的Tempdb的Filegrowth設置為64MB，數(shù)據(jù)文件因為臨時表數(shù)據(jù)的插入增長了2次）。緩存空間增長：執(zhí)行代碼如下：usemasterGO--清除緩沖區(qū)DBCCDROPCLEANBUFFERSsetnocounton--臨時表測試前，緩存空間占用大小selecttotal_cached_pages=count(1)/128.fromsys.dm_os_buffer_descriptorsbwhereb.database_id=db_id('tempdb')--臨時表測試IFOBJECT_ID('tempdb..#tb_dataLocation','U')ISNOTNULLDROPTABLE#tb_dataLocationGOCREATETABLE#tb_dataLocation(bigCharchar(8000));DECLARE@doint=1;WHILE@do<=10000BEGININSERTINTO#tb_dataLocationVALUES(REPLICATE('A',8000));SET@do=@do+1;END--臨時表測試后，緩存空間大小selecttotal_cached_pages=count(1)/128.fromsys.dm_os_buffer_descriptorsbwhereb.database_id=db_id('tempdb')執(zhí)行結(jié)果如下：因此我們可以得出結(jié)論，臨時表中的數(shù)據(jù)是存放在Tempdb的磁盤上數(shù)據(jù)文件中，同時也存放在內(nèi)存中。表變量：接下來，我們看看表變量中的數(shù)據(jù)到底是存放在哪里的？tempdb數(shù)據(jù)增長：執(zhí)行代碼如下：USEtempdbGO--表變量測試前，tempdb數(shù)據(jù)空間占用大小SELECTdatabase_name=db_name(),name,type_desc,size=size/128.FROMsys.database_files--表變量測試DECLARE@tb_dataLocationTABLE(bigCharchar(8000));DECLARE@doint=1;WHILE@do<=10000BEGININSERTINTO@tb_dataLocationVALUES(REPLICATE('A',8000));SET@do=@do+1;ENDGO--表變量測試后，tempdb數(shù)據(jù)空間占用大小SELECTdatabase_name=db_name(),name,type_desc,size=size/128.FROMsys.database_files執(zhí)行結(jié)果如下：表變量測試前后，在內(nèi)存足夠的情況，tempdb數(shù)據(jù)空間大小沒有明顯變化。緩存空間增長：我們還是新開一個SSMS連接，執(zhí)行如下語句。這段代碼是統(tǒng)計SQLServer緩存（即內(nèi)存）中BufferPool空間變化情況。usemasterGO--清除緩存區(qū)域DBCCDROPCLEANBUFFERSsetnocounton--表變量測試前，緩存占用空間大小selecttotal_cached_pages=count(1)/128.fromsys.dm_os_buffer_descriptorsbwhereb.database_id=db_id('tempdb')--表變量測試DECLARE@tb_dataLocationTABLE(bigCharchar(8000));DECLARE@doint=1;WHILE@do<=10000BEGININSERTINTO@tb_dataLocationVALUES(REPLICATE('A',8000));SET@do=@do+1;END--表變量測試后，緩存占用空間大小selecttotal_cached_pages=count(1)/128.fromsys.dm_os_buffer_descriptorsbwhereb.database_id=db_id('tempdb')執(zhí)行結(jié)果如下：因此我們可以得出結(jié)論，在內(nèi)存充足的情況下，表變量存放在內(nèi)存中。只有當SQLServer內(nèi)存空間不足時，表變量數(shù)據(jù)會寫入磁盤。1.3、作用域臨時表和表變量的另一個不同是作用域不同。局部臨時表局部臨時表是以#開頭的臨時表，局部臨時表僅當前進程可見，其他進程不可訪問，生命周期會隨著當前連接進程的關閉而消亡。那么，局部臨時表是如何做到僅當前會話可見呢？在局部臨時表創(chuàng)建的時候，SQLServer會將局部臨時表名后添加一串隨機字符來作為局部臨時表在系統(tǒng)中的唯一標識符，比如：#tmp1__0000000000F4（這里以兩個下劃線來代替多個下劃線），這樣可以避免其他進程在創(chuàng)建相同臨時表名字的時候，導致的命名沖突（比如：兩個進程同時執(zhí)行了使用臨時表的存儲過程），這個也是“1.2.1、結(jié)構(gòu)定義信息”小節(jié)問題的答案。我們怎么來確定這兩者是同一個臨時表呢？請使用下面的方法：我們先往臨時表中插入一條記錄，然后分別查詢這兩個表名字不一樣的臨時表（其實是同一個表），看看數(shù)據(jù)是否一樣即可。執(zhí)行代碼如下：--在同一個SSMS連接，執(zhí)行第一次代碼--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULL,HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0));--插入臨時表數(shù)據(jù)INSERTINTO#tmp1(FName,Width,Height)VALUES(NEWID(),0.1,0.2);--查詢tempdb數(shù)據(jù)庫USEtempdbGO;WITHDATAAS(SELECT*FROMsys.objectsWHEREobject_id=object_id('#tmp1')UNIONALLSELECT*FROMsys.objectsWHEREparent_object_id=object_id('#tmp1'))SELECTparent_object=OBJECT_NAME(parent_object_id),name,type_desc,create_dateFROMDATAORDERBYcreate_dateDESC執(zhí)行結(jié)果如下：該SSMS連接產(chǎn)生帶有標識符的局部臨時表命名為：#tmp1_______________________________________________________________________________________________________________000000000059然后，我們不要關閉該SSMS連接（這是為了保證測試還是在同一個作用域）執(zhí)行第二次代碼如下：--在同一個SSMS連接，執(zhí)行第二次代碼select*from#tmp1select*from#tmp1_______________________________________________________________________________________________________________000000000059從結(jié)果上，我們可以看到這兩條記錄是一模一樣的，所以這兩個表是指同一個對象。全局臨時表以##開頭的臨時表稱為全局，此類型的臨時表對所有進程可見，當前進程和其他進程均可訪問，生命周期是所有使用到全局臨時表的連接完全關閉后，臨時表消亡。關于全局臨時表作用域的實例演示在“進度20-臨時表和表變量概念和誤區(qū)”中“2.4、示例說明：局部臨時表和全局臨時表的作用域”涉及到了，在此不再累述。表變量表變量的作用域和變量作用域是一致的，都是當前批處理，因為從根本上來講表變量就是一種特殊的本地變量。在當前連接進程關閉以后，表變量會被SQLServer數(shù)據(jù)庫系統(tǒng)自動回收而無需用戶干預。1.4、對事務的支持臨時表對事務的支持和普通表對象保持一致；而表變量僅在更新表的時候有事務，也就是說表變量僅在操作這張表的DML操作時候支持事務，其他情況不支持事務。讓我們來看下面的例子，代碼如下：IFOBJECT_ID('tempdb..#tmp1','U')ISNOTNULLDROPTABLE#tmp1GO--創(chuàng)建臨時表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE);--創(chuàng)建表變量DECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE);--開始事務BEGINTRAN--插入數(shù)據(jù)INSERTINTO#tmp1OUTPUTINSERTED.FNameINTO@tmp2(FName)SELECTNEWID();--回滾前，查詢數(shù)據(jù)SELECTcomment='temptable',*FROM#tmp1WITH(NOLOCK)SELECTcomment='tablevariable',*FROM@tmp2--回滾事務ROLLBACK--回滾后，查詢數(shù)據(jù)SELECTcomment='temptable',*FROM#tmp1WITH(NOLOCK)SELECTcomment='tablevariable',*FROM@tmp2執(zhí)行結(jié)果如下：從這個結(jié)果截圖，我們可以得出：臨時表支持用戶事務，表變量不支持用戶事務。1.5、性能影響臨時表和表變量均可以用作臨時數(shù)據(jù)暫存媒介，具有相同的功效。但是，性能有時會有天壤之別。究其原因，我的分析是下面兩個主要原因：統(tǒng)計信息動態(tài)SQL1.5.1、統(tǒng)計信息根據(jù)之前的經(jīng)驗，我們不止一次遇到過用戶反饋，使用表變量的存儲過程性能遠遠不及使用臨時表的存儲過程（當然其他代碼保持一致的，僅將表變量替換為臨時表）。按理講，表變量存放在內(nèi)存中，應該比臨時表存放在磁盤讀寫效果高，而得出這樣的結(jié)論和大多數(shù)人的認識相違背。究其原因就在于本節(jié)要討論的主題-統(tǒng)計信息。按照一般常理，大部分人是不會對表變量創(chuàng)建主鍵、索引的，這個有可能是因為表變量只能在定義時創(chuàng)建主鍵和索引導致很多人忽略了，又或者是很多人根本沒有意識到要為表變量建立索引（只適用于SQLSERVER2014及以上版本，以下版本不支持創(chuàng)建索引）。但是，對于臨時表，大家習慣于創(chuàng)建主鍵、索引的，這就導致了表變量不存在任何的統(tǒng)計信息，而臨時表有完整的統(tǒng)計信息。統(tǒng)計信息會左右SQLServer的執(zhí)行計劃評估，和SQL執(zhí)行效率息息相關，對SQL語句的查詢性能起著至關重要的作用。查看表變量的統(tǒng)計信息：DECLARE@tmp2TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDate