表變量,臨時表 - laoyumi－IT工程師數位筆記本

文章出處

9.2 表變量、臨時表

臨時表分為局部臨時表和全局臨時表，分別以#以及##為表名前綴。局部臨時表在會話間不能共享，在會話結束后，臨時表會被刪除；全局臨時表在會話間是可共享的，當創建的會話關閉時，全局臨時表也會被刪除。

表變量，發現過許多誤解，以為表變量是內存表，是存儲在內存中的，其實表變量與臨時表一樣，都是存儲在Tempdb中的，表變量是在SQL Server 2000時加入的，當初是為了解決臨時表造成重編譯的問題而添加的。做個簡單的測試，如下面的語句代碼清單9-1中所示，可以獲取當前會話使用Tempdb的空間數量，新建一個會話1，首先執行一次查詢，發現user_objects_alloc_page_count字段對應的值是0。創建一個臨時表，并插入一條數據。如圖9-1中所示，使用了1個數據頁。

SELECT *

FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)

WHERE ddssu.session_id=@@SPID

CREATE TABLE #tempSpace1(c1 CHAR(8000))

INSERT INTO #tempSpace1 (c1)

VALUES ('a')

SELECT *

FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)

WHERE ddssu.session_id=@@SPID

代碼清單9-1 查看臨時表Tempdb使用情況

圖9-1 查看臨時表Tempdb使用結果

相同的，創建一個具有相同類型字段的表變量，查看會話對表空間的作用情況，如圖9-2中所示，也使用了一個數據頁。表變量確實使用了Tempdb作為存儲。既然兩者都是存儲在Tempdb中的，那么為何還會有兩種類型的數據呢，對比一下圖9-1與圖9-2中所顯示的user_objects_dealloc_page_count字段（該字段表示回收的用戶空間的數據頁個數），會發現，圖9-1中字段的值是0，而圖9-2中的值是1，也就是說，表變量已經被回收了。它們的區別，第一個，就是作用域不同。表變量是批處理級的，當批處理結束后，表變量就會被回收，而臨時表是會話級的，只能顯式地刪除，或者會話關閉才會回收。

SELECT *

FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)

WHERE ddssu.session_id=@@SPID

DECLARE @tempSpace TABLE(c1 CHAR(8000))

INSERT INTO @tempSpace (c1)

VALUES ('a')

SELECT *

FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)

WHERE ddssu.session_id=@@SPID

SELECT *

FROM sys.dm_db_session_space_usage AS ddssu WITH(NOLOCK)

WHERE ddssu.session_id=@@SPID

代碼清單9-2 查看表變量Tempdb使用情況

圖9-2 查看表變量Tempdb使用結果

表變量與臨時表，存在著幾個較為明顯的差異，前面提到了，表變量是為了處理臨時表造成的執行計劃頻繁重新編譯而引進的新的功能。接下來，研究一下，表變量如何避免重編譯。

9.2.1 統計信息

表變量與臨時最大的差異，同時也需要極為注意的是表變量是，沒有辦法創建統計信息。所以，如前面章節中所介紹的，查詢優化器在分析執行計劃的時候，對表變量使用"猜測"的方式來分析。這其中的好處就是，沒有統計信息的更新，就不會造成執行計劃的重編譯的問題。

使用下面的語句（直接點擊XML格式的數據行，可以打開執行計劃的可視化界面），新建臨時表，并插入200條數據，執行查詢，并查看其執行計劃關于表操作的詳細信息，如圖9-3中所示，在臨時表的預估數據行一行信息中，是200條，它的統計信息是準確的。

CREATE TABLE #tempTable1(c1 CHAR(8000));

DECLARE @cnt INT=0;

WHILE(@cnt<200)

BEGIN

INSERT INTO #tempTable1 (c1)VALUES('a');

SET @cnt+=1;

END;

SET STATISTICS XML ON

SELECT *

FROM #tempTable1 AS ts

SET STATISTICS XML OFF

代碼清單9-3 臨時表統計信息

圖9-3 臨時表統計信息

下面的語句，可以查看表變量的詳細情況，如圖9-4中，預估行的數量只有1。對于SQL Server來說，表變量是沒有統計信息的，所以SQL Server只能用"猜測"的方式來預估表變量的數據行，SQL Server會認為表變量都是小表，執行計劃通常也只會用NESTED LOOP來處理表變量的聯接查詢。因此，在使用表變量的時候，建議只應用在小數據量的查詢中。

DECLARE @tempTable1 TABLE(c1 CHAR(8000));

DECLARE @cnt INT=0;

WHILE(@cnt<200)

BEGIN

INSERT INTO @tempTable1 (c1)VALUES ('a');

SET @cnt+=1;

END;

SET STATISTICS XML ON

SELECT * FROM @tempTable1 AS ts;

SET STATISTICS XML OFF

代碼清單9-4 表變量統計信息