SQLSERVER 臨時表和表變數到底有什麼區別?

一線碼農發表於2023-02-16

一:背景

1. 講故事

今天和大家聊一套面試中經常被問到的高頻題,對,就是 臨時表表變數 這倆玩意,如果有朋友在面試中回答的不好,可以嘗試看下這篇能不能幫你成功邁過。

二:到底有什麼區別

1. 前置思考

不管是 臨時表 還是 表變數 都帶了 這個詞,既然提到了 ,按推理自然會落到某一個 資料庫 中,如果真在一個 資料庫 中,那自然就有它的儲存檔案 .mdf 和 .ldf,那是不是如我推理的那樣呢? 查閱 MSDN 的官方文件可以發現,臨時表表變數 確實都會使用 tempdb 這個臨時儲存資料庫,而且 tempdb 也有自己的 mdf,ndf,ldf 檔案,截圖如下:

有了這個大思想之後,接下來就可以進行驗證了。

2. 如何驗證都儲存在 tempdb 中 ?

要想驗證其實很簡單,sqlserver 提供了多種方式觀察。

  • 查詢的過程中觀察 tempdb 下是否存在 xxx 表。

  • 使用動態管理檢視 sys.dm_db_session_space_usage 查詢當前sql佔用tempdb下的資料頁個數。

為了讓測試效果明顯,我分別插入 10w 條記錄觀察 資料頁 佔用情況。

  1. 臨時表插入 10w 條記錄

CREATE TABLE #temp
(
    id INT,
	content CHAR(4000) DEFAULT 'aaaaaaaaaa'
);
GO
INSERT INTO #temp(id)
SELECT TOP 100000
       ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id
FROM sys.objects AS o1,sys.objects AS o2;
GO

SELECT * FROM sys.dm_db_session_space_usage
WHERE session_id=@@SPID;

從圖中的 user_objects_alloc_page_count=50456 看,當前的 insert 操作佔用了 50456 個資料頁。

接下來展開 tempdb 資料庫以及觀察到的 mdf 檔案大小,都驗證了儲存到 tempdb 這個結論。

  1. 表變數插入 10w 條記錄

因為表變數的特殊性,這裡我故意暫停 1min 讓查詢遲遲得不到結束,在這期間方便展開 tempdb,重啟 sqlserver 恢復初始狀態後,執行如下 sql:


DECLARE @temp TABLE
(
    id INT,
	content CHAR(4000) DEFAULT 'aaaaaaaaaa'
);
INSERT INTO @temp(id)
SELECT TOP 100000
       ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id
FROM sys.objects AS o1,sys.objects AS o2;

SELECT * FROM sys.dm_db_session_space_usage
WHERE session_id=@@SPID;
  
WAITFOR DELAY '00:01:00'

從圖中可以看到 表變數 也會佔用 5w+ 的資料頁並且資料檔案會膨脹。

3. 不同點在哪裡

對底層儲存有了瞭解之後,接下來按照重要度從高到低來了解一下區別吧。

  1. 臨時表有統計資訊,而表變數沒有

所謂的 統計資訊,就是對錶資料繪製一個 直方圖 來掌握資料的分佈情況,sqlserver 在擇取較優的執行計劃時會嚴重依賴於這個 直方圖,由於展開不了 Statistics 列,這裡就從執行計劃上觀察,如下圖所示:

  • 臨時表下的執行計劃

選中 SELECT * FROM #temp WHERE id > 10 AND id<20; 之後點選 SSMS 的評估執行計劃按鈕來觀察下評估執行計劃,可以清晰的看到 sqlserver 知道表中有多少條記錄,截圖如下:

  • 表變數下的執行計劃

由於表變數的批處理性,我們用 SET STATISTICS XML ON 把 xml 查詢出來,然後點選觀察視覺化檢視,參考sql 如下:


DECLARE @temp TABLE
(
    id INT,
	content CHAR(4000) DEFAULT 'aaaaaaaaaa'
);
INSERT INTO @temp(id)
SELECT TOP 100000
       ROW_NUMBER() OVER (ORDER BY o1.object_id) AS id
FROM sys.objects AS o1,sys.objects AS o2;

SET STATISTICS XML ON
SELECT * FROM @temp WHERE id > 10 AND id<20;
SET STATISTICS XML OFF

從圖中可以清晰的看到,雖然表變數有 10w 條記錄,但由於沒有統計資訊,sqlserver 也就無法知道這張表的資料分佈,所以就按照預設值 1 條來計算。

從這裡大家也能看得出來,如果 表記錄 的真實條數 和 預設的 1 嚴重偏移的話,會給生成執行計劃 造成重大失誤,這個大家一定要當心了。

  1. 其它使用上的區別

除了上一個本質上的不同,接下來就是一些使用上的不同了,比如:

  • 臨時表是 session 級的,表變數是 批處理 級

所謂的批處理,就是以 go 為界定,兩者就是作用域上的不同。

  • 臨時表可以後續修改,表變數不能後續修改。

這裡的修改涉及到 欄位,索引,整體上來說臨時表在使用上和普通表趨同,表變數不能進行後續修改。

三:總結

總的來說,表變數 沒有統計資訊,也不可以後續做 DDL 操作,這種情況下 表變數臨時表 更輕量級,不會有如下副作用:

  • DDL 修改導致執行計劃過期重建
  • sqlserver 對 統計資訊 的維護壓力

其實在這種作用域下高頻的建立和刪除表的操作中,表變數會讓系統壓力減輕很多。

但陽事總會有陰事來均衡它,一旦 表變數 的記錄條數嚴重偏移預設的 1條,會汙染sqlserver的執行計劃擇取,可能會讓你的 sql 遭受滅頂之災,所以一定要控制 表變數 的記錄條數,最好在百條內

最後的建議是:如果你是個小白可以無腦使用 臨時表 ,90%的情況下都可以做到通殺,如果你是個高手可以考慮一下 表變數

相關文章