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

表變量

表變量是一種特殊的數(shù)據(jù)類型，是變量的一種，可用于存儲結果集以進行后續(xù)處理。表主要用于臨時存儲結果集返回的行。如果聲明函數(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; 

• 若有關聯(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ī)表中使用變量。 該變量可應用于 SELECT、INSERT、UPDATE 和 DELETE 語句中用到表或表的表達式的任何地方。在定義表變量的函數(shù)、存儲過程或批處理結束時，會自動清除表變量；
• 表變量，使用系統(tǒng)內(nèi)存，讀寫速度快，但內(nèi)存是有一定限制，所有操作無日志。
• 表變量是不需要考慮其他會話訪問的問題，因此也不需要鎖機制，對于非常繁忙的系統(tǒng)來說，避免鎖的使用可以減少一部分系統(tǒng)負載；
• 表變量并不是都存在于內(nèi)存中，表變量存放在內(nèi)存是有一定限制的，如果表變量數(shù)據(jù)量超過閾值，會把內(nèi)存耗盡，然后使用TempDB的空間。
• 由于表變量不會寫日志，不會造成鎖開銷，不能在Declare之外創(chuàng)建主鍵索引等，因此表變量不會造成架構的變化，從而不會造成重編譯。該存儲過程的執(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后，不能再對表變量進行更改，即無法進行DDL操作；
• 不能直接在表變量上創(chuàng)建索引(因為一旦你創(chuàng)建一個表變量之后，就不能對其進行DDL語句了，這包括Create Index語句)，但可以通過創(chuàng)建約束（主鍵、唯一）來建立索引；
• 表變量聲明中的檢查約束、默認值以及計算所得的列不能調用用戶定義的函數(shù)。
• 表變量不支持變量之間的賦值操作；
• 因為表變量具有有限的范圍，并不是持久的數(shù)據(jù)庫的一部分，它們不受事務回滾；
• 表變量存在于內(nèi)存，當大數(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 存儲過程來運行引用該表變量的動態(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
    --建一個表變量，結構與sp_GetBorrowRecord查詢出的結果集相同
     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 ] )   

• 臨時對象都以#或##為前綴，以#前綴的臨時表為本地的，因此只有在當前用戶會話中才可以訪問，而##前綴的臨時表是全局的，因此所有用戶會話都可以訪問；
• 創(chuàng)建臨時表的方法和創(chuà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)建了多個臨時表，則它們必須有不同的名稱；
• 如果使用到了臨時表，在存儲過程的最后務必將所有的臨時表顯式刪除，先truncate table，然后drop table，這樣可以避免系統(tǒng)表的較長時間鎖定；
• 如果本地臨時表由存儲過程創(chuàng)建或由多個用戶同時執(zhí)行的應用程序創(chuàng)建，則數(shù)據(jù)庫引擎必須能夠區(qū)分由不同用戶創(chuàng)建的表。 為此，數(shù)據(jù)庫引擎在內(nèi)部為每個本地臨時表的表名追加一個數(shù)字后綴；
• 臨時表以會話為邊界，除非使用 DROP TABLE 顯式刪除臨時表，否則臨時表將在退出其作用域時由系統(tǒng)自動刪除；

局部臨時表會在下列情況下被Drop：
a、顯式調用Drop Table語句；
b、當存儲過程完成時，將自動刪除在存儲過程中創(chuàng)建的本地臨時表。
c、當前會話結束，在會話內(nèi)創(chuàng)建的所有局部臨時表都會被Drop；
全局臨時表會在下列情況下被Drop：
a、全局臨時表在創(chuàng)建此表的會話結束且其他所有任務停止對其引用時將被自動刪除。 換言之，當創(chuàng)建全局臨時表的會話結束時，最后一條引用此表的 Transact-SQL 語句完成后，將自動刪除此表。

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

臨時表的約束

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

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

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  

下面是結果集：

(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)存調入調出的機會，反而降低速度。所以數(shù)據(jù)量比較少的時候可以使用表變量，數(shù)據(jù)量大時一般推薦使用臨時表。
• 表變量缺省放在內(nèi)存，速度快，因此建議觸發(fā)器、自定義函數(shù)用表變量；存儲過程看情況，大部分用表變量；特殊的應用，大數(shù)據(jù)量的場合用臨時表。
• 無表關聯(lián)操作，只作為中間集進行數(shù)據(jù)處理，建議用表變量；有表關聯(lián)，且不能確定數(shù)據(jù)量大小的情況下，建議用臨時表。
• 表變量需要事先知道表結構，普通臨時表，只在當前會話中可用與表變量相同into一下就可以了，方便。
• 全局臨時表的功能是表變量沒法達到的。全局臨時表可在多個會話中使用。
• 表變量不必刪除，也就不會有命名沖突，臨時表特別是全局臨時表用的時候必須解決命名沖突。
• 在存儲過程中使用表變量與使用臨時表相比，減少了存儲過程的重新編譯量。
• 事務支持：臨時表:支持，表變量:不支持