dbForge Studio for SQL Server為有效的探索、分析SQL Server數據庫中的大型數據集提供全面的解決方案，并設計各種報表以幫助作出合理的決策。

點擊下載dbForge Studio for SQL Server最新試用版

在本文中，我們將探索SQL Server表變量的基礎，比較本地臨時表與全局臨時表與表變量，并檢查使用dbForge Studio for SQL Server執行的查詢執行計劃。

SQL Server表變量概述

表變量是一種SQL Server數據類型，用于存儲與臨時表相似的臨時數據。

表變量的特性如下：

1. 表變量僅在當前批處理查詢中可用。
2. 表變量不能更改其定義。
3. 不必直接刪除表變量。
4. 如果某些事務將更改添加到表變量，則在事務回滾期間不會回滾這些更改。
5. 默認情況下，不收集表變量的統計信息。
6. 以下語法描述了如何聲明表變量：

DECLARE @tbl TABLE…;

為了繼續，我們將舉例說明如何創建一個臨時表，如何用測試數據填充它，以及重新編譯一個表變量。

創建一個SQL Server臨時表

現在，我們將創建一個MyLocalTempTable臨時表，該表具有一個ID字段的主鍵和兩個非聚集索引–分別用于InsertUTCDate和Ind字段的ix_InsertUTCDate和ix_Ind。然后，我們用測試數據填充該表變量。

要在屏幕上輸出內容，請使用以下代碼片段：

DECLARE @MyLocalTempTable TABLE (    [ID] INT PRIMARY KEY,    [Value] NVARCHAR(255),    [Ind] INT,    [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),    INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),    INDEX ix_Ind NONCLUSTERED ([Ind])  );  sri  INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])  SELECT 1, N'177', 1  UNION ALL  SELECT 2, N'355', 1  UNION ALL  SELECT 3, N'777 ID', 2;    SELECT *  FROM @MyLocalTempTable  WHERE [Ind]=1;

查詢的實際執行計劃如下：

在實際的執行計劃中，對聚集索引執行掃描。注意：

• 實際讀取的行數和預計讀取的行數值不相同。
• 行的所有執行的實際數量和行每個執行的人數估計值不匹配兩種。

因此，缺少統計信息。更準確地說，在表變量中，總是只有一行。表變量的這種行為將不允許針對大量數據制定最佳執行計劃。

但是，如果我們應用RECOMPILE選項，它將計算統計信息，并且實際的執行計劃將變得最佳：

DECLARE @MyLocalTempTable TABLE (    [ID] INT PRIMARY KEY,    [Value] NVARCHAR(255),    [Ind] INT,    [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),    INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),    INDEX ix_Ind NONCLUSTERED ([Ind])  );    INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])  SELECT 1, N'177', 1  UNION ALL  SELECT 2, N'355', 1  UNION ALL  SELECT 3, N'777 ID', 2;    SELECT *  FROM @MyLocalTempTable  WHERE [Ind]=1  OPTION (RECOMPILE);

和以前一樣，我們可以看到實際計劃中對聚集索引的掃描。注意：

• 現在，“實際讀取的行數”和“預計讀取的行數”值匹配。
• 該行對所有執行的實際結果數和每頁行數執行的人數估計值接近匹配。

這意味著統計數據更相關。

默認情況下，不為表變量創建統計信息。為了澄清這一點，請執行以下代碼片段，并分析最新選擇的實際執行計劃：

DECLARE @MyLocalTempTable TABLE (    [ID] INT PRIMARY KEY,    [Value] NVARCHAR(255),    [Ind] INT,    [InsertUTCDate] DATE DEFAULT(GETUTCDATE()),    INDEX ix_InsertUTCDate NONCLUSTERED ([InsertUTCDate]),    INDEX ix_Ind NONCLUSTERED ([Ind])    );    INSERT INTO @MyLocalTempTable ([ID], [Value], [Ind])  SELECT 1, N'177', 1  UNION ALL  SELECT 2, N'355', 1  UNION ALL  SELECT 3, N'777 ID', 2;    SELECT [Ind]  FROM @MyLocalTempTable  WHERE [Ind]=1  OPTION (RECOMPILE);

如我們所見，實際計劃對Ind字段的ix_Ind非聚集索引使用Index Seek，而不是根據聚集索引進行掃描。

由于重新編譯選項，該行讀取的實際數量和行的估計數讀值幾乎一致，還有對所有執行行的實際結果數和每頁行數執行的人數估計值。這表明統計數據更加相關。

但是，默認情況下，表變量統計信息是不相關的。當我們應用RECOMPILE選項時，統計信息更接近于實際值，但仍然相差很大。結果，隨著表變量中數據的增加，執行計劃將進一步偏離最佳計劃。

讓我們執行以下代碼片段：

SELECT *  FROM @MyLocalTempTable  WHERE [Ind]=1  OPTION (RECOMPILE);    BEGIN TRAN  	UPDATE @MyLocalTempTable  	SET [Value]=NULL  	WHERE [Ind]=1;    	SELECT *  	FROM @MyLocalTempTable  	WHERE [Ind]=1  	OPTION (RECOMPILE);  ROLLBACK TRAN    SELECT *  FROM @MyLocalTempTable  WHERE [Ind]=1  OPTION (RECOMPILE);

輸出如下：

該腳本輸出表變量的所有具有Ind = 1的行。然后，在事務中，所有這些行中的“值”字段都會更新。它們獲得NULL值，我們再次輸出它們。之后，事務將回滾，然后再次輸出Ind = 1的表變量的所有行。結果表明，事務回滾不會取消對表變量所做的更改。

因此，當我們在事務的表變量中實現更改并回滾該事務時，更改將保留。這將表變量與常規表和臨時表區分開。

從2019版本開始，SQL Server根據其實際執行計劃存儲先前進行的查詢的實際參數值。如果沒有RECOMPILE選項的第一個查詢在執行中不是最佳選擇，則將優化所有用于相同或相似查詢的后續執行計劃。

dbForge Studio for SQL Server中的實際查詢執行計劃概述

在dbForge Studio for SQL Server中，實際的查詢執行計劃如下所示：

我們可以在左側看到實際的查詢執行計劃。它是一個分層的樹結構，每個塊中包含以下元素：

1. 計劃圖–執行計劃的圖形視圖
2. 計劃樹–執行計劃的樹狀圖
3. 頂級操作-基于負載（包括CPU負載）的頂級操作
4. 表I / O –表的輸入/輸出操作
5. 計劃XML –執行計劃的XML視圖

在右側，我們將詳細了解每個元素。

結論

總而言之，我們回顧了SQL Server表變量和臨時表的基礎知識，比較了本地臨時表，全局臨時表和表變量之間的差異，并以查詢執行計劃為例進行了舉例說明。

要了解有關刪除本地臨時表的更多信息，請閱讀我們的下一篇文章。

點擊下載dbForge Studio for SQL Server，并通過30天免費試用版自行檢查此功能！慧都限時活動，現dbForge Studio SQL Sever直降3000，在線訂購正版授權最低只要1710元！