在SQL Server的性能調(diào)優(yōu)中，有一個(gè)不可比面的問題：那就是如何在一段需要長時(shí)間的代碼或被頻繁調(diào)用的代碼中處理臨時(shí)數(shù)據(jù)集？表變量和臨時(shí)表是兩種選擇。記得在給一家國內(nèi)首屈一指的海運(yùn)公司作SQL Server應(yīng)用性能評(píng)估和調(diào)優(yōu)的時(shí)候就看到過大量的臨時(shí)數(shù)據(jù)集處理需求，而他們的開發(fā)人員就無法確定什么時(shí)候用臨時(shí)表，什么時(shí)候用表變量，因此他們就簡(jiǎn)單的使用了臨時(shí)表。實(shí)際上臨時(shí)表和表變量都有特定的適用環(huán)境。先賣弄一些基礎(chǔ)的知識(shí)：表變量變量都以@或@@為前綴，表變量是變量的一種，另外一種變量被稱為標(biāo)量(可以理解為標(biāo)準(zhǔn)變量，就是標(biāo)準(zhǔn)數(shù)據(jù)類型的變量，例如整型int或者日期型DateTime)。以@前綴的表變量是本地的，因此只有在當(dāng)前用戶會(huì)話中才可以訪問，而@@前綴的表變量是全局的，通常都是系統(tǒng)變量，比如說@@error代表最近的一個(gè)T-SQL語句的報(bào)錯(cuò)號(hào)。當(dāng)然因?yàn)楸碜兞渴紫仁莻€(gè)變量，因此它只能在一個(gè)Batch中生存，也就是我們所說的邊界，超出了這個(gè)邊界，表變量也就消亡了。表變量存放在內(nèi)存中，正是因?yàn)檫@一點(diǎn)所有用戶訪問表變量的時(shí)候SQL Server是不需要生成日志。同時(shí)變量是不需要考慮其他會(huì)話訪問的問題，因此也不需要鎖機(jī)制，對(duì)于非常繁忙的系統(tǒng)來說，避免鎖的使用可以減少一部分系統(tǒng)負(fù)載。表變量另外還有一個(gè)限制就是不能創(chuàng)建索引，當(dāng)然也不存在統(tǒng)計(jì)數(shù)據(jù)的問題，因此在用戶訪問表變量的時(shí)候也就不存在執(zhí)行計(jì)劃選擇的問題了(也就是以為著編譯階段后就沒有優(yōu)化階段了)，這一特性有的時(shí)候是件好事，而有些時(shí)候卻會(huì)造成一些麻煩。臨時(shí)表臨時(shí)對(duì)象都以#或##為前綴，臨時(shí)表是臨時(shí)對(duì)象的一種，還有例如臨時(shí)存儲(chǔ)過程、臨時(shí)函數(shù)之類的臨時(shí)對(duì)象，臨時(shí)對(duì)象都存儲(chǔ)在tempdb中。以#前綴的臨時(shí)表為本地的，因此只有在當(dāng)前用戶會(huì)話中才可以訪問，而##前綴的臨時(shí)表是全局的，因此所有用戶會(huì)話都可以訪問。臨時(shí)表以會(huì)話為邊界，只要?jiǎng)?chuàng)建臨時(shí)表的會(huì)話沒有結(jié)束，臨時(shí)表就會(huì)持續(xù)存在，當(dāng)然用戶在會(huì)話中可以通過DROP TABLE命令提前銷毀臨時(shí)表。我們前面說過臨時(shí)表存儲(chǔ)在tempdb中，因此臨時(shí)表的訪問是有可能造成物理IO的，當(dāng)然在修改時(shí)也需要生成日志來確保一致性，同時(shí)鎖機(jī)制也是不可缺少的。跟表變量另外一個(gè)顯著去別就是臨時(shí)表可以創(chuàng)建索引，也可以定義統(tǒng)計(jì)數(shù)據(jù)，因此SQL Server在處理訪問臨時(shí)表的語句時(shí)需要考慮執(zhí)行計(jì)劃優(yōu)化的問題。表變量 vs. 臨時(shí)表結(jié)論綜上所述，大家會(huì)發(fā)現(xiàn)臨時(shí)表和表變量在底層處理機(jī)制上是有很多差別的。簡(jiǎn)單地總結(jié)，我們對(duì)于較小的臨時(shí)計(jì)算用數(shù)據(jù)集推薦使用表變量。如果數(shù)據(jù)集比較大，如果在代碼中用于臨時(shí)計(jì)算，同時(shí)這種臨時(shí)使用永遠(yuǎn)都是簡(jiǎn)單的全數(shù)據(jù)集掃描而不需要考慮什么優(yōu)化，比如說沒有分組或分組很少的聚合(比如說COUNT、SUM、AVERAGE、MAX等)，也可以考慮使用表變量。使用表變量另外一個(gè)考慮因素是應(yīng)用環(huán)境的內(nèi)存壓力，如果代碼的運(yùn)行實(shí)例很多，就要特別注意內(nèi)存變量對(duì)內(nèi)存的消耗。一般對(duì)于大的數(shù)據(jù)集我們推薦使用臨時(shí)表，同時(shí)創(chuàng)建索引，或者通過SQL Server的統(tǒng)計(jì)數(shù)據(jù)(Statisitcs)自動(dòng)創(chuàng)建和維護(hù)功能來提供訪問SQL語句的優(yōu)化。如果需要在多個(gè)用戶會(huì)話間交換數(shù)據(jù)，當(dāng)然臨時(shí)表就是唯一的選擇了。需要提及的是，由于臨時(shí)表存放在tempdb中，因此要注意tempdb的調(diào)優(yōu)。SQL中的臨時(shí)表和表變量

我們經(jīng)常使用臨時(shí)表和表變量,那現(xiàn)在我們就對(duì)臨時(shí)表和表變量進(jìn)行一下討論.

臨時(shí)表局部臨時(shí)表全局臨時(shí)表表變量臨時(shí)表臨時(shí)表存儲(chǔ)在TempDB數(shù)據(jù)庫中,所有的使用此SQL Server 實(shí)例的用戶都共享這個(gè)TempDB,因?yàn)槲覀儜?yīng)該確保用來存儲(chǔ)TempDB數(shù)據(jù)庫的硬盤有足夠的空間,以使之能夠自己的增長.最好能夠存儲(chǔ)在一個(gè)擁有獨(dú)立硬盤控制器上.因?yàn)檫@樣不存在和其它的硬盤I/O進(jìn)行爭(zhēng)用.我們很多程序員認(rèn)為臨時(shí)表非常危險(xiǎn),因?yàn)榕R時(shí)表有可能被多個(gè)連接所共享.其實(shí)在SQL Server中存在兩種臨時(shí)表:局部臨時(shí)表和全局臨時(shí)表,局部臨時(shí)表(Local temp table)以#前綴來標(biāo)識(shí),并且只能被創(chuàng)建它的連接所使用.全局臨時(shí)表(Global temp table)以##前綴來進(jìn)行標(biāo)識(shí),并且可以和其它連接所共享.局部臨時(shí)表局部臨時(shí)表不能夠被其它連接所共享的原因其實(shí)是在SQL Server 2000中自動(dòng)為局部臨時(shí)表的表名后面加上了一個(gè)唯一字符來標(biāo)識(shí).如: CREATE TABLE [#DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) )現(xiàn)在我們來查看一下TempDB中 sysobjects表,我們會(huì)發(fā)現(xiàn)我們新創(chuàng)建的臨時(shí)表#DimCustomer_test已經(jīng)被加上了后綴:USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’the Result is:name#DimCustomer_test___________________________________________________________________________________________________000000000005全局臨時(shí)表下面我們來看一下全局臨時(shí)表: CREATE TABLE [##DimCustomer_test] ( [CustomerKey] [int] , [FirstName] [nvarchar](50) ,[MiddleName] [nvarchar](50) ,[LastName] [nvarchar](50) )現(xiàn)在我們來查看一下TempDB中 sysobjects表,我們會(huì)發(fā)現(xiàn)我們新創(chuàng)建的臨時(shí)表##DimCustomer_test沒有被加上了后綴:USE TempDB GO SELECT name FROM sysobjects WHERE name LIKE ’%DimCustomer%’The Result are:#DimCustomer_test___________________________________________________________________________________________________000000000005##DimCustomer_test--Drop test temp tables DROP TABLE [##DimCustomer_test] DROP TABLE [#DimCustomer_test]可以看到我們剛才創(chuàng)建的全局臨時(shí)表名字并沒有被加上標(biāo)識(shí).表變量表變量和臨時(shí)表針對(duì)我們使用人員來說并沒有什么不同,但是在存儲(chǔ)方面來說,他們是不同的,表變量存儲(chǔ)在內(nèi)存中.所以在性能上和臨時(shí)表相比會(huì)更好些!另一個(gè)不同的地方是在表連接中使用表變量時(shí),要為此表變量指定別名.如:USE AdventureWorksDW GO DECLARE @DimCustomer_test TABLE ( [CustomerKey] [int] , [FirstName] [nvarchar](50),[MiddleName] [nvarchar](50),[LastName] [nvarchar](50) ) ---insert data to @DimCustomer_test INSERT @DimCustomer_test ( [CustomerKey] , [FirstName],[MiddleName],[LastName] ) SELECT [CustomerKey] , [FirstName],[MiddleName],[LastName] FROM DimCustomer SELECT [@DimCustomer_test].CustomerKey,SUM(FactInternetSales.OrderQuantity)FROM @DimCustomer_test INNER JOIN FactInternetSales ON@DimCustomer_test.CustomerKey = FactInternetSales.CustomerKeyGroup BY CustomerKeyResult:Server: Msg 137, Level 15, State 2, Line 32Must declare the variable ’@DimCustomer_test’.如果我們對(duì)上面的查詢進(jìn)行更改,對(duì)查詢使用別名(并且找開IO):-----in the follow script,we used the table alias.DECLARE @DimCustomer_test TABLE( [CustomerKey] [int] , [FirstName] [nvarchar](50),[MiddleName] [nvarchar](50),[LastName] [nvarchar](50) )INSERT @DimCustomer_test( [CustomerKey] , [FirstName],[MiddleName],[LastName] )SELECT [CustomerKey] , [FirstName],[MiddleName],[LastName]FROM DimCustomerSELECT t.CustomerKey,f.OrderQuantityFROM @DimCustomer_test t INNER JOIN FactInternetSales f ONt.CustomerKey = f.CustomerKeywhere t.CustomerKey=13513表變量在批處理結(jié)束時(shí)自動(dòng)被系統(tǒng)刪除,所以你不必要像使用臨時(shí)表表一樣顯示的對(duì)它進(jìn)行刪除.----------------------------------------另外在今天幫同事Tuning SQL 腳本地時(shí)候，發(fā)現(xiàn)對(duì)于大數(shù)據(jù)量表的查詢（10w-100W），用變量的方式比用select 的方式居然執(zhí)行時(shí)間減少了100倍??！似懂非懂，但從來沒有想到差別如此大，驚訝ing，記錄一筆，研究一下M1：declare @tempID intset @tempID =(select lots_id from qs_notes where id='CVT20080321')select * from ls_Qs_notes where id = @tempID---返回記錄998，行執(zhí)行時(shí)間6589M2：select * from ls_Qs_notes where id =(select lots_id from qs_notes where id='CVT20080321')---返回記錄998 ，行執(zhí)行時(shí)間60