問題引入

“菜鳥啊，最近我看到阿里雲開發者論壇的資料庫RDS中有人在提SQL Server表變數和臨時表如何選擇的問題，你去深入探討下這個問題吧，解答解答他們的疑惑吧”，老鳥又開始為菜鳥找活幹了。
“鳥哥啊，關於表變數和臨時表使用選擇的問題啊，向來行業裡爭論不休，我比較擔心我們的觀點被人家拍磚啊”。
“鳥啊，有爭論才說明這個問題有價值啊，所以我們才更應該去弄清楚，道明白啊”。反正老鳥總會找到合適的理由。
“那好吧，要把這個問題要刨根問底，我們需要分四篇文章來把這個問題理清楚。”，菜鳥掰著手指頭就數了出來：
 表變數和臨時表基本概念
 表變數和臨時表的對比
 表變數和臨時表認知誤區
 表變數和臨時表的選擇

什麼是表變數

關於什麼是SQL Server的表變數，我們分別從表變數的定義、表變數的作用和表變數的使用三個角度來看看什麼是表變數。

表變數定義

表變數，是微軟至SQL Server 2000以來引入的概念，從名稱我們就可以很容易看出，表變數本質是一個變數，只是它具有了正式表物件的很多屬性。比如：它有表欄位、欄位資料型別、欄位寬度、主鍵、唯一約束、NULL、NOT NULL約束、CHECK和DEFAULT約束。但是，表變數不支援約束命名，不支援索引，不支援外來鍵，不支援表變數定義後的任何表變數結構的修改，僅可做資料的DML操作。

表變數的作用

當我們需要在當前會話臨時快取少量的中間資料結果集，供當前會話多次使用這同一資料集或者同一資料結果集的一部分時，我們可以考慮使用表變數，表變數中的資料是快取在記憶體中（大部分情況下如此，也有極少情況例外，我們後面的文章會講到）。注意這裡是少量資料集，不是大量結果集，如果非要給一個參照經驗值的話，個人建議是最好不要超過10萬條資料記錄，所佔的空間大小不要超過100MB。

表變數的使用

關於表變數作用，在此我們以一個例子來說明。在這個例子中，我們定義了一個表變數來暫時存放商品的基本屬性資訊，然後INSERT了三條資料，緊接著對其中一條資料做UPDATE操作，再接著DELETE了一條資料，最後我們SELECT了整個表變數存放的資料。

USE tempdb
GO

DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL CHECK(Windth>0.0)
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  DEFAULT(GETDATE())
);

INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES(`A`, 0.1, 0.2, 0.3);
INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES(`B`, 0.4, 0.5, 0.6);
INSERT INTO @tb_table(ProductName, Length, Windth, Height) VALUES(`C`, 0.7, 0.8, 0.9);

UPDATE A
SET Length = 2.5
FROM @tb_table AS A
WHERE RowID = 1
;

DELETE TOP(1) A
FROM @tb_table AS A
WHERE RowID = 2;

SELECT * FROM @tb_table;

從這個例子，我們看到了表變數所具有的正式表物件的屬性，表變數是如何定義的，以及DML操作，在當前會話結束後，表變數會被SQL Server自動回收。
這裡需要特別提醒下，SQL Server系統不允許我們像正式表物件那樣對約束進行顯示命名，SQL Server會報告錯誤。比如，定義表變數程式碼：

USE tempdb
GO
DECLARE
    @tb_table TABLE(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(8,2) NOT NULL
,Windth DECIMAL(8,2) NOT NULL
,Height DECIMAL(8,2) NOT NULL
,Indate DATETIME NOT NULL  CONSTRAINT DF_tbTable DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

報錯資訊如下：

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword `CONSTRAINT`.

什麼是臨時表

在看完什麼是表變數以後，我們還是分別從臨時表定義、臨時表的作用和臨時表的使用三個角度來看看什麼是SQL Server的臨時表。

臨時表定義

SQL Server的臨時表是一種特殊的表，表名字是以#或者##打頭。無論臨時表在哪個資料庫下建立，SQL Server均把臨時表結構資訊和資料儲存在Tempdb資料庫下。
以#打頭的臨時表稱為區域性臨時表，這種型別的臨時表僅當前程式可見，其他程式不可訪問，生命週期會隨著當前連線程式的關閉而消亡。
以##打頭的臨時表稱為全域性，此型別的臨時表對所有程式可見，當前程式和其他程式均可訪問，生命週期是所有使用到全域性臨時表的連線完全關閉後，臨時表消亡。

臨時表的作用

臨時表的作用和表變數類似，均是用於暫時快取資料。臨時表中的資料會被儲存在Tempdb的物理檔案磁碟上，當需要資料讀取時，SQL Server會將臨時表中資料從磁碟檔案讀入SQL Server Buffer Pool中，然後返回給客戶端。因此，臨時表對資料的儲存和讀取會有物理的IO Write和IO Read的。臨時表相較於表變數可以儲存稍微大量一些的資料，比如資料量超過10萬條記錄數，資料空間佔用量超過100MB。但是，如果經常有類似的臨時表使用場景時，建議對Tempdb資料庫做效能優化相關的配置工作。

臨時表的使用

為了和表變數形成對比，我特意將表結構和資料保持一致，不同的地方在於，我們可以對約束進行顯示指定命名，可以建立索引。在次，為了看清楚區域性臨時表和全域性臨時表的區別，我們也建立了一個全域性臨時表。

USE tempdb
GO
IF OBJECT_ID(`tempdb..#tb_table`,`U`) IS NOT NULL
    DROP TABLE #tb_table
GO
CREATE TABLE #tb_table(
RowID INT IDENTITY(1,1) NOT NULL PRIMARY KEY
,ProductName NVARCHAR(50) NOT NULL UNIQUE
,Length DECIMAL(4,2) NOT NULL CHECK(Length>0.0)
,Windth DECIMAL(4,2) NOT NULL
,Height DECIMAL(4,2) NOT NULL CHECK(Height>0.0)
,Dimension AS (Length * Windth * Height)
,Indate DATETIME NOT NULL  CONSTRAINT DF_tbTable DEFAULT(GETDATE())
,CONSTRAINT CK_Windth CHECK(Windth>0.0)
);

CREATE INDEX IX_ProductName
ON #tb_table(ProductName);
GO

INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES(`A`, 0.1, 0.2, 0.3);
INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES(`B`, 0.4, 0.5, 0.6);
INSERT INTO #tb_table(ProductName, Length, Windth, Height) VALUES(`C`, 0.7, 0.8, 0.9);

IF OBJECT_ID(`tempdb..##tb_table`,`U`) IS NOT NULL
    DROP TABLE ##tb_table
GO
SELECT * 
    INTO ##tb_table
FROM #tb_table;

UPDATE A
SET Length = 2.5
FROM #tb_table AS A
WHERE RowID = 1
;

DELETE TOP(1) A
FROM #tb_table AS A
WHERE RowID = 2;

SELECT * FROM #tb_table;

SELECT *
FROM ##tb_table

執行上面的區域性臨時表和全域性臨時表建立語句之後，我們在SSMS中新開啟一個連線，執行下面的語句：

SELECT *
FROM ##tb_table
GO

SELECT *
FROM #tb_table

返回執行結果如下：

返回執行訊息如下：

從返回的結果分析可知：區域性臨時表僅當前連線可以訪問，對其他程式不可見（訪問報告物件不存在的錯誤），而全域性臨時表不僅當前連線可以訪問，對其他程式可見。

寫在最後

關於SQL Server表變數和臨時表的使用規則是一個仁者見仁智者見智的話題，所以我們希望能夠把這個話題儘可能的剖析清楚，讓讀者對兩者有非常清楚的認識。