參考鏈接：一、SQLServer表變量和臨時(shí)表系列之概念篇/articles/68288?spm=a2c4e.11153940.blogcont68898.20.11b5722bQUdorM二、SQLServer臨時(shí)表和變量系列之對(duì)比篇/articles/68898?spm=a2c4e.11153940.blogcont69098.20.15f251cfX3amSn三、SQLServer臨時(shí)表和表變量系列之認(rèn)知誤區(qū)篇/articles/69098?spm=a2c4e.11153940.blogcont68898.21.11b5722bQUdorM四、SQLServer臨時(shí)表和表變量系列之選擇篇/articles/69187五、SQLServer臨時(shí)表和表變量系列之踢館篇/articles/69217?spm=a2c4e.11153940.blogcont69187.23.7ad16a7dQw81OQ幾種典型場(chǎng)景今天要討論的話題，即當(dāng)我們面對(duì)具體的業(yè)務(wù)場(chǎng)景的時(shí)候，該選擇臨時(shí)表還是表變量？以下是幾種典型的場(chǎng)景，讓我們看看到底該作何選擇，以及做出最終選擇的具體原因和考量。1.存儲(chǔ)過(guò)程嵌套（只能用臨時(shí)表，不支持表變量）在SQLServer中，使用存儲(chǔ)過(guò)程的好處顯而易見(jiàn)，往往會(huì)節(jié)約存儲(chǔ)過(guò)程執(zhí)行計(jì)劃編譯時(shí)間，提高查詢語(yǔ)句的執(zhí)行效率。有時(shí)候，我們?cè)跇?gòu)建存儲(chǔ)過(guò)程多層次嵌套場(chǎng)景中，會(huì)有內(nèi)層存儲(chǔ)過(guò)程需要臨時(shí)使用外層存儲(chǔ)過(guò)程的“暫存”數(shù)據(jù)。在SQLServer暫存臨時(shí)數(shù)據(jù)的方法可以使用臨時(shí)表或者表變量，但是在這種場(chǎng)景中，僅臨時(shí)表適合。比如，下面的例子：（林楓山：說(shuō)實(shí)話，這是我第一次看到這種用法，因?yàn)槲覐膩?lái)沒(méi)用過(guò)嵌套存儲(chǔ)過(guò)程。）USEtempdbGO--構(gòu)建內(nèi)層調(diào)用的存儲(chǔ)過(guò)程IFOBJECT_ID('tempdb..Proc_Inner','P')ISNOTNULLDROPPROCProc_InnerGOCREATEPROCEDUREProc_InnerASBEGINSETNOCOUNTON --更新臨時(shí)表第一條記錄（該臨時(shí)表是外層存儲(chǔ)過(guò)程創(chuàng)建的）UPDATEt1SETComment='INNER'FROM#tmp1t1WHEREFID=1;ENDGO--構(gòu)建外層調(diào)用的存儲(chǔ)過(guò)程IFOBJECT_ID('tempdb..Proc_Outer','P')ISNOTNULLDROPPROCProc_OuterGOCREATEPROCProc_OuterASBEGINSETNOCOUNTONIFOBJECT_ID('tempdb..#tmp1','U')ISNOTNULLDROPPROC#tmp1--外層存儲(chǔ)過(guò)程創(chuàng)建臨時(shí)表CREATETABLE#tmp1(FIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,CommentVARCHAR(100)NULL) --臨時(shí)表插入兩條記錄INSERTINTO#tmp1(Comment)VALUES(''),('OUTER')--調(diào)用內(nèi)層存儲(chǔ)過(guò)程前，查看臨時(shí)表數(shù)據(jù)SELECT*FROM#tmp1--調(diào)用內(nèi)層的存儲(chǔ)過(guò)程EXECProc_Inner--調(diào)用內(nèi)層存儲(chǔ)過(guò)程后，查看臨時(shí)表數(shù)據(jù)SELECT*FROM#tmp1ENDGO--執(zhí)行外層調(diào)用存儲(chǔ)過(guò)程EXECProc_Outer執(zhí)行結(jié)果如下：我們?cè)趦?nèi)層存儲(chǔ)過(guò)程將臨時(shí)表#tmp1字段Comment更新為“INNER”；外層存儲(chǔ)過(guò)程在調(diào)用內(nèi)層存儲(chǔ)過(guò)程前、后，分別查詢臨時(shí)表的數(shù)據(jù)。從這個(gè)結(jié)果來(lái)看，內(nèi)層存儲(chǔ)過(guò)程完全可以使用外層存儲(chǔ)過(guò)程創(chuàng)建的臨時(shí)表。這種存儲(chǔ)過(guò)程嵌套的場(chǎng)景無(wú)法使用表變量，因?yàn)閮?nèi)層存儲(chǔ)過(guò)程因?yàn)闊o(wú)法識(shí)別外層存儲(chǔ)過(guò)程創(chuàng)建的表變量，會(huì)提示沒(méi)有定義而報(bào)錯(cuò)。2.服務(wù)啟動(dòng)自動(dòng)執(zhí)行存儲(chǔ)過(guò)程（選用全局臨時(shí)表）有時(shí)候，我們需要在SQLServerService啟動(dòng)完畢后，立馬自動(dòng)執(zhí)行某個(gè)存儲(chǔ)過(guò)程以獲取某些重要的數(shù)據(jù)信息。比如：我們想知道SQLServer服務(wù)啟動(dòng)后，到底有哪些用戶連接到了SQLServer服務(wù)器。我們可以選擇使用全局臨時(shí)表來(lái)暫存用戶信息，而且其他進(jìn)程也可以查看相應(yīng)的數(shù)據(jù)信息。方法如下：USEmasterGO/*showadvancedoptions選項(xiàng)用來(lái)顯示sp_configure系統(tǒng)存儲(chǔ)過(guò)程高級(jí)選項(xiàng)。當(dāng)showadvancedoptions設(shè)置為1時(shí)，可以使用sp_configure列出高級(jí)選項(xiàng)。默認(rèn)值為0。該設(shè)置將立即生效，無(wú)需重新啟動(dòng)服務(wù)器。*/EXECsys.sp_configure'showadvancedoptions',1GO/*使用scanforstartupprocs選項(xiàng)掃描在SQLServer啟動(dòng)時(shí)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。如果將此選項(xiàng)設(shè)置為1，則SQLServer將掃描服務(wù)器上定義的所有自動(dòng)運(yùn)行的存儲(chǔ)過(guò)程，并運(yùn)行這些過(guò)程。scanforstarupprocs的默認(rèn)值為0（不掃描）。*/EXECsys.sp_configure'scanforstartupprocs',1GORECONFIGUREWITHOVERRIDEGO--創(chuàng)建服務(wù)啟動(dòng)自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程CREATEPROCProc_GetLoginUserWhenStartupASBEGINSETNOCOUNTONIFOBJECT_ID('tempdb..##temp','U')ISNOTNULLDROPTABLE##tempCREATETABLE##temp(RowIdINTIDENTITY(1,1)NOTNULL,LoginNamevarchar(200)NOTNULL)INSERTINTO##tempSELECTDISTINCTloginameFROMsys.sysprocessesENDGO/*設(shè)置或清除自動(dòng)執(zhí)行的存儲(chǔ)過(guò)程。設(shè)置為自動(dòng)執(zhí)行運(yùn)行每次的實(shí)例的存儲(chǔ)的過(guò)程SQLServer已啟動(dòng)。*/EXECsys.sp_procoption'Proc_GetLoginUserWhenStartup','startup','on'GO代碼執(zhí)行成功后，重啟SQLService服務(wù)。在開(kāi)始菜單的文件目錄，找到SQLServer配置管理器，然后重啟SQLService服務(wù)。重啟SQLServerService，然后新開(kāi)一個(gè)連接執(zhí)行下面的語(yǔ)句：select*from##temp執(zhí)行結(jié)果如下：SQLServerService重啟完畢后，系統(tǒng)會(huì)自動(dòng)執(zhí)行Master數(shù)據(jù)庫(kù)下的存儲(chǔ)過(guò)程dbo.Proc_GetLoginUserWhenStartup以獲取到哪些用戶連接到SQLServer。這個(gè)過(guò)程和Linux開(kāi)機(jī)自動(dòng)執(zhí)行自定義腳本或者服務(wù)非常類(lèi)似。由于其他進(jìn)程需要查看抓取到的信息，在此使用全局臨時(shí)表而不是表變量。附：如果在代碼執(zhí)行時(shí)，提示：“配置選項(xiàng)'scanforstartupprocs'不存在,也可能是高級(jí)選項(xiàng)?！卑严旅娼貓D的這段語(yǔ)句單獨(dú)執(zhí)行，就不會(huì)報(bào)錯(cuò)。3.暫存大量數(shù)據(jù)（選用臨時(shí)表）在很多場(chǎng)景我們需要暫存大量數(shù)據(jù)或者根本無(wú)法預(yù)估需要暫存的數(shù)據(jù)量。這里需要首先明確的一個(gè)問(wèn)題是，到底暫存多大的數(shù)據(jù)量算大量？我們可以認(rèn)為需要暫存的數(shù)據(jù)量大小接近或者超過(guò)SQLServer最大可以使用內(nèi)存一半的時(shí)候，這個(gè)數(shù)據(jù)量就是大量數(shù)據(jù)。比如：當(dāng)SQLServer的MaxServerMemory設(shè)置為1GB，需要暫存的數(shù)據(jù)量接近或者超過(guò)512MB時(shí)，512MB就是大量數(shù)據(jù)；但是，當(dāng)SQLServerMaxServerMemory為10GB甚至更高，需要暫存512MB時(shí)，這個(gè)數(shù)據(jù)量又不算是大量數(shù)據(jù)。在大量數(shù)據(jù)需要暫存時(shí)，無(wú)論使用臨時(shí)表或者表變量，SQLServer系統(tǒng)最終會(huì)將數(shù)據(jù)存在Tempdb的數(shù)據(jù)文件磁盤(pán)上（因?yàn)楸碜兞靠赡馨l(fā)生內(nèi)存不足時(shí)，數(shù)據(jù)會(huì)存放在磁盤(pán)上）。所以這個(gè)時(shí)候，請(qǐng)選擇使用臨時(shí)表來(lái)暫存數(shù)據(jù)，最好是能夠根據(jù)業(yè)務(wù)場(chǎng)景為臨時(shí)表創(chuàng)建合適的索引，以提高后續(xù)臨時(shí)表查詢語(yǔ)句的執(zhí)行效率。4.需要支持事務(wù)回滾（只能用臨時(shí)表）有時(shí)候業(yè)務(wù)場(chǎng)景需要暫存的數(shù)據(jù)結(jié)構(gòu)支持事務(wù)回滾，在這種場(chǎng)景下，我們應(yīng)該選擇臨時(shí)表。我們知道，表變量不支持事務(wù)回滾，而臨時(shí)表對(duì)事務(wù)回滾的支持和正式表沒(méi)有任何差異。所以，在這種場(chǎng)景下，我們需要選擇臨時(shí)表作為暫存數(shù)據(jù)結(jié)構(gòu)。5.表值函數(shù)的返回值（只能用表變量）表值函數(shù)，就是函數(shù)的返回值是數(shù)據(jù)表格式。表值函數(shù)的返回值為表變量，這個(gè)場(chǎng)景中，是無(wú)法使用臨時(shí)表來(lái)替換的。比如：我想要找出當(dāng)前數(shù)據(jù)庫(kù)下表名稱中含有某個(gè)特定字符串的所有表信息，用表值函數(shù)來(lái)實(shí)現(xiàn)的方式代碼如下：--這里你可以在任何數(shù)據(jù)庫(kù)測(cè)試，查詢數(shù)據(jù)庫(kù)里面數(shù)據(jù)表名稱的表信息UseAIS20140120174606go--這里創(chuàng)建一個(gè)函數(shù)，返回?cái)?shù)據(jù)表結(jié)果集，返回值只能是表變量IFOBJECT_ID('dbo.Func_FindTables','TF')ISNOTNULLDROPFUNCTIONdbo.Func_FindTablesGOCREATEFUNCTIONdbo.Func_FindTables(@partnersysname)RETURNS@TablesTABLE(RowIDINTIDENTITY(1,1)NOTNULLPRIMARYKEY,tb_Object_IDBIGINTNULL,[database_name]SYSNAMENULL,[schema_name]SYSNAMENULL,[object_Name]SYSNAMENULL)ASBEGININSERTINTO@TablesSELECTobject_id,DB_NAME(),SCHEMA_NAME(schema_id),nameFROMsys.tablesWHEREnamelike'%'+@partner+'%';RETURNENDGO--調(diào)用表值函數(shù)示例--這里在金蝶K3WISE賬套里面做例子，查詢銷(xiāo)售訂單表的表信息SELECT*FROMdbo.Func_FindTables('SEORDER')執(zhí)行結(jié)果如下：查找當(dāng)前數(shù)據(jù)庫(kù)下表名字中含有SEORDER關(guān)鍵字的表詳情。由于表值函數(shù)返回值僅支持表變量，所以這種場(chǎng)景中是無(wú)法使用臨時(shí)表的。6.高并發(fā)場(chǎng)景選擇表變量（選用表變量）在SQLServer數(shù)據(jù)庫(kù)高并發(fā)場(chǎng)景中，請(qǐng)慎重選擇臨時(shí)表的使用，建議使用表變量。對(duì)于這個(gè)場(chǎng)景的測(cè)試，我們會(huì)使用到SQLTest這個(gè)測(cè)試工具。SQLTest是一款簡(jiǎn)單易用，非常容易上手的SQLServer性能、壓力和單元測(cè)試工具。它既可以測(cè)試本地環(huán)境的SQLServer工作負(fù)載，也可以測(cè)試云環(huán)境的SQLServer服務(wù)。下載地址：/Download下載后直接打開(kāi)，在聯(lián)網(wǎng)的情況下，自動(dòng)激活，無(wú)需輸入激活碼：接下來(lái)進(jìn)入測(cè)試正題：首先，我們創(chuàng)建兩個(gè)測(cè)試存儲(chǔ)過(guò)程，它們的邏輯一模一樣，唯一不同的是一個(gè)使用表變量，一個(gè)使用臨時(shí)表。--本次測(cè)試，你可以隨便選個(gè)數(shù)據(jù)庫(kù)做測(cè)試USEAIS20140120174606GO--創(chuàng)建表變量測(cè)試的存儲(chǔ)過(guò)程IFOBJECT_ID('dbo.Proc_表變量','P')ISNOTNULLDROPPROCdbo.Proc_表變量GOCREATEPROCdbo.Proc_表變量ASBEGINSETNOCOUNTONDECLARE@t1TABLE(c1INT,c2INT,c3CHAR(2000));WITHDATAAS(SELECT*FROM(VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))AST(C))INSERTINTO@t1SELECTa.C,b.C,REPLICATE('A',2000)FROMDATAasa,dataasbENDGO--創(chuàng)建臨時(shí)表測(cè)試的存儲(chǔ)過(guò)程IFOBJECT_ID('dbo.Proc_臨時(shí)表','P')ISNOTNULLDROPPROCdbo.Proc_臨時(shí)表GOCREATEPROCdbo.Proc_臨時(shí)表ASBEGINSETNOCOUNTONCREATETABLE#t1(c1INT,c2INT,c3CHAR(2000));WITHDATAAS(SELECT*FROM(VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9))AST(C))INSERTINTO#t1SELECTa.C,b.C,REPLICATE('A',2000)FROMDATAasa,dataasbENDGO執(zhí)行代碼成功后，打開(kāi)SQLTest測(cè)試工具，接下來(lái)，我們使用兩個(gè)Workload，每個(gè)Workload開(kāi)啟20個(gè)進(jìn)程，測(cè)試時(shí)間為120秒，測(cè)試三次（多次測(cè)試是為了測(cè)試嚴(yán)謹(jǐn)性）。Workload1執(zhí)行“EXECProc_表變量”100次；Workload2執(zhí)行“EXECProc_臨時(shí)表”100次。Workload1測(cè)試代碼如下：=====================USEAIS20140120174606SETNOCOUNTONDECLARE@iINTSET@i=1WHILE@i<=100BEGINEXECProc_表變量SET@i=@i+1ENDGOWorkload2測(cè)試代碼如下：======================USEAIS20140120174606SET