這篇有點難寫，因為找不到合適的參考資料，幾次打算寫，卻又罷手。參考鏈接：一、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、什么是表變量關于什么是SQLServer的表變量，我們分別從表變量的定義、表變量的作用、表變量的使用三個角度來闡述。1.1、表變量的定義表變量，本質是一個變量，只是它具有了正式表對象的很多屬性。比如：它有表字段、字段數據類型、字段寬度、主鍵、唯一約束、NULL、NOTNULL約束、CHECK和DEFAULT約束。但是，表變量不支持約束命名（注：不能對約束進行顯式命名），不支持索引（注：可以在定義時直接創(chuàng)建索引，定義后不可創(chuàng)建索引），不支持外鍵，不支持表變量定義后的任何表變量結構的修改（注：定義好表變量后，后續(xù)不允許“增刪改字段”這種改變表結構的操作），僅可做數據的DML操作（注：支持數據增刪改操作）。1.2、表變量的作用當我們需要在當前會話，臨時緩存少量的中間數據結果集，供當前會話多次使用，我們可以考慮使用表變量。表變量中的數據是緩存在內存中（大部分情況下如此，也有極少情況例外，當SQLServer內存空間不足時，表變量數據會寫入磁盤，在后面用示例驗證）。注意：這里是少量數據集，不是大量結果集，如果非要給一個參照經驗值的話，個人建議是最好不要超過10萬條數據記錄，所占的空間大小不要超過100MB。1.3、表變量的使用我們以示例來說明：1、定義一個表變量，暫時存放商品的基本屬性信息2、然后，INSERT了三條數據3、接著，對其中一條數據做UPDATE操作4、再接著，DELETE了一條數據5、最后，我們SELECT了整個表變量存放的數據。--1、定義一個表變量，暫時存放商品的基本屬性信息DECLARE@tmp1TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));--2、然后，INSERT了三條數據INSERTINTO@tmp1(FName,Width,Height)VALUES('A',0.1,0.2);INSERTINTO@tmp1(FName,Width,Height)VALUES('B',0.4,0.5);INSERTINTO@tmp1(FName,Width,Height)VALUES('C',0.7,0.8);--3、接著，對其中一條數據做UPDATE操作UPDATEt1SETWidth=2.5FROM@tmp1t1WHEREFID=1;--4、再接著，DELETE了一條數據DELETEFROM@tmp1WHEREFID=2;--5、最后，我們SELECT了整個表變量存放的數據。SELECT*FROM@tmp1;執(zhí)行結果如下：從這個例子，我們看到了表變量所具有的正式表對象的屬性，表變量是如何定義的，以及DML（增刪查改）操作，在當前會話結束后，表變量會被SQLServer自動回收。1.4、舉例說明：顯示約束命名會報錯表變量不允許我們像正式表對象那樣對約束進行顯示命名，SQLServer會報告錯誤。示例如下：--這里定義兩個約束，一個是DF_FDate的DEFAULT約束，另一個是CK_Width的CHECK約束DECLARE@tmp1TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULL,HeightDECIMAL(8,2)NOTNULL,FDateDATETIMENOTNULLCONSTRAINTDF_FDateDEFAULT(GETDATE()),CONSTRAINTCK_WidthCHECK(Width>0.0));執(zhí)行結果如下：（不支持顯示約束命名）2、什么是臨時表在看完什么是表變量以后，我們還是分別從臨時表定義、臨時表的作用、臨時表的使用三個角度來看看什么是SQLServer的臨時表。2.1、臨時表的定義SQLServer的臨時表是一種特殊的表，表名字是以#或者##開頭。無論臨時表在哪個數據庫下創(chuàng)建，SQLServer均把臨時表結構信息和數據存儲在Tempdb數據庫下。局部臨時表：以#開頭的臨時表稱為局部臨時表，這種類型的臨時表僅當前進程可見，其他進程不可訪問，生命周期會隨著當前連接進程的關閉而消亡。全局臨時表：以##打頭的臨時表稱為全局，此類型的臨時表對所有進程可見，當前進程和其他進程均可訪問，生命周期是所有使用到全局臨時表的連接完全關閉后，臨時表消亡。2.2、臨時表的作用臨時表的作用和表變量類似，均是用于暫時緩存數據。臨時表中的數據，會被儲存在Tempdb的物理文件磁盤上，當需要數據讀取時，SQLServer會將臨時表中數據，從磁盤文件讀入SQLServerBufferPool（緩存區(qū)域）中，然后返回給客戶端。因此，臨時表對數據的存儲和讀取會有物理的IOWrite和IORead（I/O讀寫）的。臨時表相較于表變量，可以存儲稍微大量一些的數據，比如數據量超過10萬條記錄數，數據空間占用量超過100MB。但是，如果經常有類似的臨時表使用場景時，建議對Tempdb數據庫做性能優(yōu)化相關的配置工作。2.3、臨時表的使用為了和表變量形成對比，這里示例將表結構和數據保持一致，不同的地方在于，我們可以對約束進行顯示指定命名，可以創(chuàng)建索引。在次，為了看清楚局部臨時表和全局臨時表的區(qū)別，我們也創(chuàng)建了一個全局臨時表。示例如下：--判斷是否存在已存在#tmp1臨時表，如果存在就刪除IFOBJECT_ID('tempdb..#tmp1','U')ISNOTNULLDROPTABLE#tmp1GO--1、創(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));--2、增加索引CREATEINDEXIX_FNameON#tmp1(FName);--3、插入數據INSERTINTO#tmp1(FName,Width,Height)VALUES('A',0.1,0.2);INSERTINTO#tmp1(FName,Width,Height)VALUES('B',0.4,0.5);INSERTINTO#tmp1(FName,Width,Height)VALUES('C',0.7,0.8);--4、更新數據UPDATEt1SETWidth=2.5FROM#tmp1t1WHEREFID=1;--5、刪除數據DELETEFROM#tmp1WHEREFID=2;--6、查詢局部臨時表數據SELECT*FROM#tmp1;--判斷是否存在已存在##tmp2全局臨時表，如果存在就刪除IFOBJECT_ID('tempdb..##tmp2','U')ISNOTNULLDROPTABLE##tmp2GO--1、創(chuàng)建全局臨時表SELECT*INTO##tmp2FROM#tmp1;--2、查詢全局臨時表SELECT*FROM##tmp2執(zhí)行結果如下：2.4、示例說明：局部臨時表和全局臨時表的作用域上面的測試執(zhí)行完以后，我們在SSMS（SQLServerManagementStudio）管理界面，打開一個新的查詢頁簽：執(zhí)行以下語句：SELECT*FROM##tmp2GOSELECT*FROM#tmp1執(zhí)行結果如下：從返回的結果分析可知：局部臨時表僅當前連接可以訪問，對其他進程不可見（訪問報告對象不存在的錯誤），而全局臨時表不僅當前連接可以訪問，對其他進程可見。3、認知誤區(qū)關于臨時表和表變量，很多SQLServer老司機都存在或多或少的認知誤區(qū)。指出一些常見的認知誤區(qū)，希望以此來找到一些常常被我們忽略的地方。SQLServer關于臨時表和表變量的常見的認知誤區(qū)包含以下六點：表變量不支持事務表變量不能創(chuàng)建索引表變量沒有統(tǒng)計信息表變量存駐在內存中表變量訪問比臨時表快局部臨時表不需要手動回收資源3.1、表變量不支持事務表變量不支持用戶顯式事務回滾，即有BEGINTRANSACTION類型的事務回滾，但是表變量還是支持DML語言操作的事務性的。(DML，簡單講就是增刪查改等數據操作；DDL，是修改表結構、數據類型等數據庫定義操作。)怎么理解呢？就是說，表變量數據操作的時候，是不可能存在一些數據成功，一些數據失敗的。比如：在一個批處理中，向表變量中插入兩條記錄，不可能存在只有一條記錄插入成功，而另一條記錄插入失敗的情況；只可能是兩條數據都插入成功或者都不成功的情況。這就是表變量支持事務原子性的一種表現。（說實話，這塊事務有點難以理解，但是加上事務知識的說明，就容易理解：）在SQLServer中事務被分為3類常見的事務：自動提交事務：是SQLServer默認的一種事務模式，每條Sql語句都被看成一個事務進行處理，你應該沒有見過，一條Update修改2個字段的語句，只修該了1個字段而另外一個字段沒有修改。顯式事務：T-sql標明，由BeginTransaction開啟事務開始，由CommitTransaction提交事務、RollbackTransaction回滾事務結束。隱式事務：使用SetIMPLICIT_TRANSACTIONSON將將隱式事務模式打開，不用BeginTransaction開啟事務，當一個事務結束，這個模式會自動啟用下一個事務，只用CommitTransaction提交事務、RollbackTransaction回滾事務即可。（總結下：表變量不支持事務回滾，但并不是不支持事務）示例如下：（insert操作就是一個事務處理，）--定義一個表變量DECLARE@tmp1TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE);--第一步驗證：插入成功數據2條INSERTINTO@tmp1SELECTNEWID()UNIONALLSELECTNEWID();--查詢數據結果SELECT*from@tmp1--刪除數據deletefrom@tmp1--插入重復數據，違反唯一約束，是只成功插入1條，另1條數據報錯？還是2條數據都沒插入？INSERTINTO@tmp1SELECT1UNIONALLSELECT1--查詢數據結果SELECT*from@tmp1執(zhí)行結果如下：（第二次插入報錯，是兩條數據插入都不成功）3.2、表變量不能創(chuàng)建索引表變量本質上是一種特殊的變量類型，只不過它具有了很多表的屬性。比如：字段、字段數據類型、字段寬度、唯一約束、NULL、NOTNULL約束、CHECK和DEFAULT約束等。由于表變量定義完畢以后，不支持對表變量結構的任何變更和索引創(chuàng)建，所以很多人會認為表變量不能創(chuàng)建索引。其實，表變量完全可以在定義的時候就直接創(chuàng)建索引，參見如下代碼，在定義表變量時候創(chuàng)建了非聚集索引IX_FDate:（在SQLSERVER2014及以上版本，表變量不能創(chuàng)建索引這個結論是錯誤的！在2014以下版本，表變量不能創(chuàng)建索引是正確的結論?。┦纠缦拢?-在SQLServer2014前不支持建表的腳本中創(chuàng)建索引DECLARE@tmp1TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()),INDEXIX_FDateNONCLUSTERED(FDate));在SQLSERVER2012中執(zhí)行結果如下：（有語法錯誤）在SQLSERVER2017中執(zhí)行結果如下：3.3、表變量沒有統(tǒng)計信息在關系型數據庫RDBMS系統(tǒng)中，統(tǒng)計信息是執(zhí)行計劃正確評估的基礎，和SQL執(zhí)行效率息息相關，對SQL語句的查詢性能起著至關重要的作用。(本例，林楓山測試結論和原作者測試結論不相符合，暫定認為原作者錯誤。保留大眾結論：表變量就是沒有統(tǒng)計信息！)那么，表變量到底有沒有統(tǒng)計信息呢？在過往的經驗中，很多SQLServer數據庫的同行都認為表變量沒有統(tǒng)計信息存在，其實這種認識是錯誤的。我們可以看下面的例子：DECLARE@tmp1TABLE(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,FNameNVARCHAR(50)NOTNULLUNIQUE,WidthDECIMAL(8,2)NOTNULLCHECK(Width>0.0),HeightDECIMAL(8,2)NOTNULLCHECK(Height>0.0),FDateDATETIMENOTNULLDEFAULT(GETDATE()));DECLARE@table_variable_idbigintSELECTTOP1@table_variable_id=object_idFROMtempdb.sys.all_objectsWHEREparent_object_id=0ORDERBYcreate_dateDESCSELECTstatistics_name=,table_name=OBJECT_NAME(t1.object_id),column_name=COL_NAME(t2.object_id,t2.column_id)FROMsys.statst1WITH(NOLOCK)INNERJOINsys.stats_columnst2WITH(NOLOCK)ONt1.object_id=t2.object_idANDt1.stats_id=t2.stats_idWHEREt1.object_id=@table_variable_id在SQLSERVER2012執(zhí)行如下：在SQLSERVER2017測試如下：3.4、表變量僅駐留在內存中當SQLServer認為數據庫內存不足時（不是物理機內存不足，是SQLOS的內存不足），表變量的數據是會寫入到Tempdb的數據文件，即寫入了磁盤文件中。（SQLServerOS是在Windows之上，用于服務SQLServer的一個用戶級別的操作系統(tǒng)層次。它將操作系統(tǒng)部分的功能從整個SQLServer引擎中抽象出來，單獨形成一層，以便為存儲引擎提供服務。SQLServerOS主要提供了任務調度、內存分配、死鎖檢測、資源檢測、鎖管理、BufferPool管理等多種功能。）我們可以參照下面的思路來證明SQLServer內存不足時，表變量的數據會寫入tempdb數據文件：修改MaxServerMemory為512MB重啟SQLService使得配置生效獲取測試前的內存使用量和Tempdb數據文件大小向表變量中存入10000條記錄（大概會占據780MB空間，超過512MB的最大內存大?。┰俅潍@取測試后的內存使用量和Tempdb數據文件大小代碼如下：USEtempdbGO--修改MaxServerMemory為512MBEXECsys.sp_configure'showadvancedoptions',1GORECONFIGUREWITHOVERRIDEGOEXECsys.sp_configure'maxservermemory(MB)',512GORECONFIGUREWITHOVERRIDEGO--要么手動重啟sqlservice--要么清除緩存區(qū)域CHECKPOINTGODBCCDROPCLEANBUFFERS--檢查測試前，內存使用情況SELECTMemoryUsedBefore=count(1)/128.FROMsys.dm_os_buffer_descriptorsbWHEREis_modified=1--檢查測試前，temp文件占用硬盤空間大小SELECT[DBName]=db_name(),[LogName]=,[SpaceUsedBefore]=CAST(FILEPROPERTY(,'SpaceUsed')ASfloat)*CONVERT(float,8)/1024FROMsys.database_filesASs--表變量中，插入10萬條測試數據DECLARE@saveToDiskTABLE(bigCharchar(8000));DECLARE@doint=1;WHILE@do<=100000BEGININSERTINTO@saveToDiskVALUES(REPLICATE('A',8000));SET@do=@do+1;END--檢查插入數據后，內存使用情況SELECTMemoryUsedAfter=count(1)/128.FROMsys.dm_os_buffer_descriptorsbWHEREis_modified=1--檢查插入數據后，temp文件占用硬盤空間大小SELECT[DBName]=db_name(),[LogName]=,[SpaceUsedAfter]=CAST(FILEPROPERTY(,'SpaceUsed')ASfloat)*CONVERT(float,8)/1024FROMsys.database_filesASs執(zhí)行結果如下：（測試前：內存4MB，tempdb4MB；測試后：內存340MBtempdb784MB）分析結果：從結果來看Tempdb數據文件從4MB增長到784MB，大約增加了780MB，這個數據增長量和表變量需要暫存的數據量大小是非常相近的；內存大小從4MB增長到340MB，大約增