一：背景

1. 講故事

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

二：到底有什么區(qū)別

1. 前置思考

不管是 臨時(shí)表 還是 表變量 都帶了 表 這個(gè)詞，既然提到了 表 ，按推理自然會(huì)落到某一個(gè) 數(shù)據(jù)庫 中，如果真在一個(gè) 數(shù)據(jù)庫 中，那自然就有它的存儲(chǔ)文件 .mdf 和 .ldf，那是不是如我推理的那樣呢？ 查閱 MSDN 的官方文檔可以發(fā)現(xiàn)，臨時(shí)表 和 表變量 確實(shí)都會(huì)使用 tempdb 這個(gè)臨時(shí)存儲(chǔ)數(shù)據(jù)庫，而且 tempdb 也有自己的 mdf，ndf，ldf 文件，截圖如下：

有了這個(gè)大思想之后，接下來就可以進(jìn)行驗(yàn)證了。

2. 如何驗(yàn)證都存儲(chǔ)在 tempdb 中 ?

要想驗(yàn)證其實(shí)很簡單，sqlserver 提供了多種方式觀察。

• 查詢的過程中觀察 tempdb 下是否存在 xxx 表。
• 使用動(dòng)態(tài)管理視圖 sys.dm_db_session_space_usage 查詢當(dāng)前sql占用tempdb下的數(shù)據(jù)頁個(gè)數(shù)。

為了讓測試效果明顯，我分別插入 10w 條記錄觀察 數(shù)據(jù)頁 占用情況。

1.臨時(shí)表插入 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 看，當(dāng)前的 insert 操作占用了 50456 個(gè)數(shù)據(jù)頁。

接下來展開 tempdb 數(shù)據(jù)庫以及觀察到的 mdf 文件大小，都驗(yàn)證了存儲(chǔ)到 tempdb 這個(gè)結(jié)論。

2.表變量插入 10w 條記錄

因?yàn)楸碜兞康奶厥庑?，這里我故意暫停 1min 讓查詢遲遲得不到結(jié)束，在這期間方便展開 tempdb，重啟 sqlserver 恢復(fù)初始狀態(tài)后，執(zhí)行如下 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'

從圖中可以看到 表變量 也會(huì)占用 5w+ 的數(shù)據(jù)頁并且數(shù)據(jù)文件會(huì)膨脹。

3. 不同點(diǎn)在哪里

對(duì)底層存儲(chǔ)有了了解之后，接下來按照重要度從高到低來了解一下區(qū)別吧。

1.臨時(shí)表有統(tǒng)計(jì)信息，而表變量沒有

所謂的 統(tǒng)計(jì)信息，就是對(duì)表數(shù)據(jù)繪制一個(gè) 直方圖 來掌握數(shù)據(jù)的分布情況，sqlserver 在擇取較優(yōu)的執(zhí)行計(jì)劃時(shí)會(huì)嚴(yán)重依賴于這個(gè) 直方圖，由于展開不了 Statistics 列，這里就從執(zhí)行計(jì)劃上觀察，如下圖所示：

• 臨時(shí)表下的執(zhí)行計(jì)劃

選中 SELECT * FROM #temp WHERE id > 10 AND id<20; 之后點(diǎn)擊 SSMS 的評(píng)估執(zhí)行計(jì)劃按鈕來觀察下評(píng)估執(zhí)行計(jì)劃，可以清晰的看到 sqlserver 知道表中有多少條記錄，截圖如下：

• 表變量下的執(zhí)行計(jì)劃

由于表變量的批處理性，我們用 SET STATISTICS XML ON 把 xml 查詢出來，然后點(diǎn)擊觀察可視化視圖，參考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 條記錄，但由于沒有統(tǒng)計(jì)信息，sqlserver 也就無法知道這張表的數(shù)據(jù)分布，所以就按照默認(rèn)值 1 條來計(jì)算。

從這里大家也能看得出來，如果 表記錄 的真實(shí)條數(shù) 和 默認(rèn)的 1 嚴(yán)重偏移的話，會(huì)給生成執(zhí)行計(jì)劃 造成重大失誤，這個(gè)大家一定要當(dāng)心了。

2.其它使用上的區(qū)別

除了上一個(gè)本質(zhì)上的不同，接下來就是一些使用上的不同了，比如：

• 臨時(shí)表是 session 級(jí)的，表變量是 批處理 級(jí)

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

• 臨時(shí)表可以后續(xù)修改，表變量不能后續(xù)修改。

這里的修改涉及到 字段，索引，整體上來說臨時(shí)表在使用上和普通表趨同，表變量不能進(jìn)行后續(xù)修改。

三：總結(jié)

總的來說，表變量 沒有統(tǒng)計(jì)信息，也不可以后續(xù)做 DDL 操作，這種情況下 表變量 比 臨時(shí)表 更輕量級(jí)，不會(huì)有如下副作用：

• DDL 修改導(dǎo)致執(zhí)行計(jì)劃過期重建
• sqlserver 對(duì) 統(tǒng)計(jì)信息 的維護(hù)壓力

其實(shí)在這種作用域下高頻的創(chuàng)建和刪除表的操作中，表變量會(huì)讓系統(tǒng)壓力減輕很多。

但陽事總會(huì)有陰事來均衡它，一旦 表變量 的記錄條數(shù)嚴(yán)重偏移默認(rèn)的 1條，會(huì)污染sqlserver的執(zhí)行計(jì)劃擇取，可能會(huì)讓你的 sql 遭受滅頂之災(zāi)，所以一定要控制 表變量 的記錄條數(shù)，最好在百條內(nèi) 。

最后的建議是：如果你是個(gè)小白可以無腦使用 臨時(shí)表 ，90%的情況下都可以做到通殺，如果你是個(gè)高手可以考慮一下 表變量。

到此這篇關(guān)于SQLSERVER 臨時(shí)表和表變量到底有什么區(qū)別的文章就介紹到這了,更多相關(guān)SQLSERVER 臨時(shí)表和表變量區(qū)別內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論