在SQL Server的性能調(diào)優(yōu)中备蚓，有一個不可比面的問題：那就是如何在一段需要長時間的代碼或被頻繁調(diào)用的代碼中處理臨時數(shù)據(jù)集？表變量和臨時表是兩種選擇厚柳。

表變量

表變量是一種特殊的數(shù)據(jù)類型军熏，是變量的一種，可用于存儲結(jié)果集以進(jìn)行后續(xù)處理肿轨。表主要用于臨時存儲結(jié)果集返回的行。如果聲明函數(shù)和變量的類型為表蕊程，則表變量可在函數(shù)椒袍、存儲的過程和批處理。

創(chuàng)建表變量語法

DECLARE @<表變量名> 
  Table( { <column_definition> | <table_constraint> } [ ,...n ] )
<column_definition> ::=   
    column_name scalar_data_type   
    [ COLLATE <collation_definition> ]   
    [ [ DEFAULT constant_expression ] | IDENTITY [ ( seed , increment ) ] ]   
    [ ROWGUIDCOL ]   
    [ column_constraint ] [ ...n ]   
  
<column_constraint> ::=   
    { [ NULL | NOT NULL ]   
    | [ PRIMARY KEY | UNIQUE ]   
    | CHECK ( logical_expression )   
    }   
  
<table_constraint> ::=   
     { { PRIMARY KEY | UNIQUE } ( column_name [ ,...n ] )  
     | CHECK ( logical_expression )   
     }   

表變量一般備注

• 表可以像一般的 FROM 子句中按名稱引用變量藻茂，如下面的示例所示︰

SELECT Employee_ID, Department_ID FROM @MyTableVar; 

• 若有關(guān)聯(lián)查詢驹暑，則在FROM 子句，外部表必須使用一個別名辨赐，來引用變量优俘，如下面的示例中所示︰

SELECT EmployeeID, DepartmentID   
FROM @MyTableVar m  
JOIN Employee on (m.EmployeeID =Employee.EmployeeID AND  
   m.DepartmentID = Employee.DepartmentID);  

• 表變量行為類似于本地變量。 有明確定義的作用域掀序。 這就是在其中聲明該變量的函數(shù)帆焕、存儲過程或批處理。在其范圍內(nèi)森枪，表可像常規(guī)表中使用變量视搏。 該變量可應(yīng)用于 SELECT、INSERT县袱、UPDATE 和 DELETE 語句中用到表或表的表達(dá)式的任何地方浑娜。在定義表變量的函數(shù)、存儲過程或批處理結(jié)束時式散，會自動清除表變量筋遭；
• 表變量，使用系統(tǒng)內(nèi)存暴拄，讀寫速度快漓滔，但內(nèi)存是有一定限制，所有操作無日志乖篷。
• 表變量是不需要考慮其他會話訪問的問題响驴，因此也不需要鎖機(jī)制，對于非常繁忙的系統(tǒng)來說撕蔼，避免鎖的使用可以減少一部分系統(tǒng)負(fù)載豁鲤；
• 表變量并不是都存在于內(nèi)存中，表變量存放在內(nèi)存是有一定限制的鲸沮，如果表變量數(shù)據(jù)量超過閾值琳骡，會把內(nèi)存耗盡，然后使用TempDB的空間讼溺。
• 由于表變量不會寫日志楣号，不會造成鎖開銷，不能在Declare之外創(chuàng)建主鍵索引等怒坯，因此表變量不會造成架構(gòu)的變化炫狱，從而不會造成重編譯。該存儲過程的執(zhí)行計劃已經(jīng)在創(chuàng)建存儲過程的時候生成了剔猿，因此之后執(zhí)行的存儲過程使用表變量不會造成執(zhí)行計劃的重編譯毕荐。

表變量的限制：

• 不能對表變量執(zhí)行SELECT INTO語句，如：

SELECT select_list INTO table_variable;

• 在SQL Server2000中艳馒，表變量也不能用于INSERT INTO table_variable EXEC stored_procedure這樣的語句中憎亚，而在2005之后的版本就開始支持這個用法了。
• 在DECLARE后弄慰，不能再對表變量進(jìn)行更改第美，即無法進(jìn)行DDL操作；
• 不能直接在表變量上創(chuàng)建索引(因為一旦你創(chuàng)建一個表變量之后陆爽，就不能對其進(jìn)行DDL語句了什往，這包括Create Index語句)，但可以通過創(chuàng)建約束（主鍵慌闭、唯一）來建立索引别威；
• 表變量聲明中的檢查約束躯舔、默認(rèn)值以及計算所得的列不能調(diào)用用戶定義的函數(shù)。
• 表變量不支持變量之間的賦值操作省古；
• 因為表變量具有有限的范圍粥庄，并不是持久的數(shù)據(jù)庫的一部分，它們不受事務(wù)回滾豺妓；
• 表變量存在于內(nèi)存惜互，當(dāng)大數(shù)據(jù)量時，使用表變量的話就太耗內(nèi)存了琳拭；
• 在表變量上不能創(chuàng)建非聚集索引（為 PRIMARY 或 UNIQUE 約束創(chuàng)建的系統(tǒng)索引除外）训堆。與具有非聚集索引的臨時表相比，這可能會影響查詢性能白嘁；
• 表變量不具有數(shù)據(jù)分布的統(tǒng)計信息坑鱼，它們不會觸發(fā)重新編譯。在許多情況下絮缅，優(yōu)化器會在假定 table 變量沒有行的前提下生成查詢計劃姑躲。這樣不利于優(yōu)化器做出正確的執(zhí)行計劃，不適合數(shù)據(jù)量較大的情況盟蚣。
• 如果表變量是在 EXEC 語句或 sp_executesql 存儲過程外創(chuàng)建的黍析，則不能使用 EXEC 語句或sp_executesql 存儲過程來運(yùn)行引用該表變量的動態(tài) SQL Server 查詢。由于表變量只能在它們的本地作用域中引用

【示例】
(1)表變量的創(chuàng)建與查詢

-- Create the table variable.  
DECLARE @MyTableVar table(  
    LocationID int NOT NULL,  
    CostRate smallmoney NOT NULL,  
    NewCostRate AS CostRate * 1.5,  
    ModifiedDate datetime);  
  
-- Insert values into the table variable.  
INSERT INTO @MyTableVar (LocationID, CostRate, ModifiedDate)  
    SELECT LocationID, CostRate, GETDATE() FROM Production.Location  
    WHERE CostRate > 0;  
  
-- View the table variable result set.  
SELECT * FROM @MyTableVar;  
GO  

(2)在INSERT..EXEC中使用表變量

--獲取借書記錄分析情況
create proc sp_GetBorrowAnalysis
    @BeginTime    datetime,
    @EndTime    datetime 
as
    --建一個表變量屎开，結(jié)構(gòu)與sp_GetBorrowRecord查詢出的結(jié)果集相同
     declare @Record table 
     (
      BookID int,                --書籍ID
      BookName varchar(100),    --書籍名稱
      TypeID int,                --書籍類別ID
      CardID int                --借書卡ID
      CardName varchar(100)        --借書人姓名
     )

    --獲取這段時間內(nèi)的借書記錄阐枣，并存入@Record表變量中
    insert into @Record exec sp_GetBorrowRecord @BeginTime,@EndTime

臨時表

臨時表是臨時對象的一種，還有例如臨時存儲過程奄抽、臨時函數(shù)之類的臨時對象蔼两，臨時對象都存儲在tempdb中。

創(chuàng)建臨時表格式的兩種方式
①CREATE TABLE

CREATE TABLE  #|##
    [ database_name . [ schema_name ] . | schema_name . ] table_name   
    ( { <column_definition> } [ ,...n ] )   

• 臨時對象都以#或##為前綴逞度，以#前綴的臨時表為本地的额划，因此只有在當(dāng)前用戶會話中才可以訪問，而##前綴的臨時表是全局的档泽，因此所有用戶會話都可以訪問俊戳；
• 創(chuàng)建臨時表的方法和創(chuàng)建普通表一樣，除了有以下不同：
• 多了#|##前綴馆匿；
• 當(dāng)創(chuàng)建本地或全局臨時表時抑胎，CREATE TABLE 語法支持除 FOREIGN KEY 約束以外的其他所有約束定義；
• 如果臨時表中指定了 FOREIGN KEY 約束渐北，則該語句將返回一條表明已跳過此約束的警告消息阿逃。 此表仍將創(chuàng)建，但不使用 FOREIGN KEY 約束。 在 FOREIGN KEY 約束中不能引用臨時表恃锉；

②SELECT INTO
??使用SELECT INTO會自動生成臨時表搀菩，不需要事先創(chuàng)建

SELECT <select_list>
INTO  #|##<臨時表名>
FROM <table_source>
WHERE <search_condition>

臨時表一般備注

• 臨時表的用法(如INSERT、UPDATE破托、DELETE)和一般的表一樣肪跋；
• 臨時表不能分區(qū)莲绰；
• 如果在單個存儲過程或批處理中創(chuàng)建了多個臨時表引镊，則它們必須有不同的名稱来屠；
• 如果使用到了臨時表，在存儲過程的最后務(wù)必將所有的臨時表顯式刪除瘟芝，先truncate table，然后drop table褥琐，這樣可以避免系統(tǒng)表的較長時間鎖定锌俱；
• 如果本地臨時表由存儲過程創(chuàng)建或由多個用戶同時執(zhí)行的應(yīng)用程序創(chuàng)建，則數(shù)據(jù)庫引擎必須能夠區(qū)分由不同用戶創(chuàng)建的表敌呈。 為此贸宏，數(shù)據(jù)庫引擎在內(nèi)部為每個本地臨時表的表名追加一個數(shù)字后綴；
• 臨時表以會話為邊界磕洪，除非使用 DROP TABLE 顯式刪除臨時表吭练，否則臨時表將在退出其作用域時由系統(tǒng)自動刪除；

局部臨時表會在下列情況下被Drop：
a析显、顯式調(diào)用Drop Table語句鲫咽；
b、當(dāng)存儲過程完成時谷异，將自動刪除在存儲過程中創(chuàng)建的本地臨時表分尸。
c、當(dāng)前會話結(jié)束歹嘹，在會話內(nèi)創(chuàng)建的所有局部臨時表都會被Drop箩绍；
全局臨時表會在下列情況下被Drop：
a、全局臨時表在創(chuàng)建此表的會話結(jié)束且其他所有任務(wù)停止對其引用時將被自動刪除尺上。 換言之材蛛，當(dāng)創(chuàng)建全局臨時表的會話結(jié)束時，最后一條引用此表的 Transact-SQL 語句完成后怎抛，將自動刪除此表仰税。

• 臨時表存儲在TempDb中，因此臨時表的訪問是有可能造成物理IO的抽诉，當(dāng)然在修改時也需要生成日志來確保一致性陨簇，同時鎖機(jī)制也是不可缺少的；

臨時表的約束

• 不能對臨時表進(jìn)行分區(qū)；
• 不能對臨時表加外鍵約束河绽；
• 臨時表內(nèi)列的數(shù)據(jù)類型不能定義成沒有在TempDb中沒有定義自定義數(shù)據(jù)類型(自定義數(shù)據(jù)類型是數(shù)據(jù)庫級別的對象己单，而臨時表屬于TempDb)

同名臨時表
??從一般備注我們知道，如果本地臨時表由存儲過程創(chuàng)建耙饰，數(shù)據(jù)庫引擎在內(nèi)部為每個本地臨時表的表名追加一個數(shù)字后綴纹笼。當(dāng)存儲過程完成時,將自動除去在存儲過程中創(chuàng)建的本地臨時表。所以即使在存儲過程或觸發(fā)器中創(chuàng)建的本地臨時表的名稱可以與在調(diào)用存儲過程或觸發(fā)器之前創(chuàng)建的臨時表名稱相同苟跪，但是臨時表在會話中只是一個代號廷痘，在實際的系統(tǒng)臨時庫中，真實表名會自動處理件已，所以它們也是不同的笋额。
??在存儲過程或觸發(fā)器中創(chuàng)建的本地臨時表的名稱可以與在調(diào)用存儲過程或觸發(fā)器之前創(chuàng)建的臨時表名稱相同。 但是篷扩，如果查詢引用臨時表兄猩，而同時有兩個同名的臨時表，則不定義針對哪個表解析該查詢鉴未。
??嵌套存儲過程同樣可以創(chuàng)建與調(diào)用它的存儲過程所創(chuàng)建的臨時表同名的臨時表枢冤，套存儲過程中對表名的所有引用都被解釋為是針對該嵌套過程所創(chuàng)建的表。但是铜秆，為了對其進(jìn)行修改以解析為在嵌套過程中創(chuàng)建的表淹真，此表必須與調(diào)用過程創(chuàng)建的表具有相同的結(jié)構(gòu)和列名。

CREATE PROCEDURE dbo.Test2  
AS  
    CREATE TABLE #t(x INT PRIMARY KEY);  
    INSERT INTO #t VALUES (2);  
    SELECT Test2Col = x FROM #t;  
GO  
  
CREATE PROCEDURE dbo.Test1  
AS  
    CREATE TABLE #t(x INT PRIMARY KEY);  
    INSERT INTO #t VALUES (1);  
    SELECT Test1Col = x FROM #t;  
EXEC Test2;  
GO  
  
CREATE TABLE #t(x INT PRIMARY KEY);  
INSERT INTO #t VALUES (99);  
GO  
  
EXEC Test1;  
GO  

下面是結(jié)果集：

(1 row(s) affected)
Test1Col
-----------
1
(1 row(s) affected)
Test2Col
-----------
2

【示例】

drop table #Tmp  --刪除臨時表#Tmp
create table #Tmp --創(chuàng)建臨時表#Tmp
(
  ID  int IDENTITY (1,1)   not null, --創(chuàng)建列ID,并且每次新增一條記錄就會加1
  WokNo        varchar(50),  
  primary key (ID)   --定義ID為臨時表#Tmp的主鍵   
);
Select * from #Tmp  --查詢臨時表的數(shù)據(jù)
truncate table #Tmp --清空臨時表的所有數(shù)據(jù)和約束

表變量與臨時表的對比

• 臨時表是利用了硬盤(tempdb數(shù)據(jù)庫) 连茧，表名變量是占用內(nèi)存趟咆。在數(shù)據(jù)量比較大的時候，如果使用表變量梅屉，會把內(nèi)存耗盡值纱，然后使用TEMPDB的空間，這樣主要還是使用硬盤空間坯汤，但同時把內(nèi)存基本耗盡虐唠，增加了內(nèi)存調(diào)入調(diào)出的機(jī)會，反而降低速度惰聂。所以數(shù)據(jù)量比較少的時候可以使用表變量疆偿，數(shù)據(jù)量大時一般推薦使用臨時表。
• 表變量缺省放在內(nèi)存搓幌，速度快杆故，因此建議觸發(fā)器、自定義函數(shù)用表變量溉愁；存儲過程看情況处铛，大部分用表變量；特殊的應(yīng)用，大數(shù)據(jù)量的場合用臨時表撤蟆。
• 無表關(guān)聯(lián)操作奕塑，只作為中間集進(jìn)行數(shù)據(jù)處理，建議用表變量家肯；有表關(guān)聯(lián)龄砰，且不能確定數(shù)據(jù)量大小的情況下，建議用臨時表讨衣。
• 表變量需要事先知道表結(jié)構(gòu)换棚，普通臨時表，只在當(dāng)前會話中可用與表變量相同into一下就可以了反镇，方便固蚤。
• 全局臨時表的功能是表變量沒法達(dá)到的。全局臨時表可在多個會話中使用愿险。
• 表變量不必刪除颇蜡，也就不會有命名沖突价说，臨時表特別是全局臨時表用的時候必須解決命名沖突辆亏。
• 在存儲過程中使用表變量與使用臨時表相比，減少了存儲過程的重新編譯量鳖目。
• 事務(wù)支持：臨時表:支持扮叨，表變量:不支持