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

點(diǎn)擊下載dbForge Studio for SQL Server最新試用版

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

SQL Server表變量概述

表變量是一種SQL Server數(shù)據(jù)類型，用于存儲(chǔ)與臨時(shí)表相似的臨時(shí)數(shù)據(jù)。

表變量的特性如下：

1. 表變量?jī)H在當(dāng)前批處理查詢中可用。
2. 表變量不能更改其定義。
3. 不必直接刪除表變量。
4. 如果某些事務(wù)將更改添加到表變量，則在事務(wù)回滾期間不會(huì)回滾這些更改。
5. 默認(rèn)情況下，不收集表變量的統(tǒng)計(jì)信息。
6. 以下語(yǔ)法描述了如何聲明表變量：

DECLARE @tbl TABLE…;

為了繼續(xù)，我們將舉例說(shuō)明如何創(chuàng)建一個(gè)臨時(shí)表，如何用測(cè)試數(shù)據(jù)填充它，以及重新編譯一個(gè)表變量。

創(chuàng)建一個(gè)SQL Server臨時(shí)表

現(xiàn)在，我們將創(chuàng)建一個(gè)MyLocalTempTable臨時(shí)表，該表具有一個(gè)ID字段的主鍵和兩個(gè)非聚集索引–分別用于InsertUTCDate和Ind字段的ix_InsertUTCDate和ix_Ind。然后，我們用測(cè)試數(shù)據(jù)填充該表變量。

要在屏幕上輸出內(nèi)容，請(qǐng)使用以下代碼片段：

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;

查詢的實(shí)際執(zhí)行計(jì)劃如下：

在實(shí)際的執(zhí)行計(jì)劃中，對(duì)聚集索引執(zhí)行掃描。注意：

• 實(shí)際讀取的行數(shù)和預(yù)計(jì)讀取的行數(shù)值不相同。
• 行的所有執(zhí)行的實(shí)際數(shù)量和行每個(gè)執(zhí)行的人數(shù)估計(jì)值不匹配兩種。

因此，缺少統(tǒng)計(jì)信息。更準(zhǔn)確地說(shuō)，在表變量中，總是只有一行。表變量的這種行為將不允許針對(duì)大量數(shù)據(jù)制定最佳執(zhí)行計(jì)劃。

但是，如果我們應(yīng)用RECOMPILE選項(xiàng)，它將計(jì)算統(tǒng)計(jì)信息，并且實(shí)際的執(zhí)行計(jì)劃將變得最佳：

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);

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

• 現(xiàn)在，“實(shí)際讀取的行數(shù)”和“預(yù)計(jì)讀取的行數(shù)”值匹配。
• 該行對(duì)所有執(zhí)行的實(shí)際結(jié)果數(shù)和每頁(yè)行數(shù)執(zhí)行的人數(shù)估計(jì)值接近匹配。

這意味著統(tǒng)計(jì)數(shù)據(jù)更相關(guān)。

默認(rèn)情況下，不為表變量創(chuàng)建統(tǒng)計(jì)信息。為了澄清這一點(diǎn)，請(qǐng)執(zhí)行以下代碼片段，并分析最新選擇的實(shí)際執(zhí)行計(jì)劃：

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);

如我們所見，實(shí)際計(jì)劃對(duì)Ind字段的ix_Ind非聚集索引使用Index Seek，而不是根據(jù)聚集索引進(jìn)行掃描。

由于重新編譯選項(xiàng)，該行讀取的實(shí)際數(shù)量和行的估計(jì)數(shù)讀值幾乎一致，還有對(duì)所有執(zhí)行行的實(shí)際結(jié)果數(shù)和每頁(yè)行數(shù)執(zhí)行的人數(shù)估計(jì)值。這表明統(tǒng)計(jì)數(shù)據(jù)更加相關(guān)。

但是，默認(rèn)情況下，表變量統(tǒng)計(jì)信息是不相關(guān)的。當(dāng)我們應(yīng)用RECOMPILE選項(xiàng)時(shí)，統(tǒng)計(jì)信息更接近于實(shí)際值，但仍然相差很大。結(jié)果，隨著表變量中數(shù)據(jù)的增加，執(zhí)行計(jì)劃將進(jìn)一步偏離最佳計(jì)劃。

讓我們執(zhí)行以下代碼片段：

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的行。然后，在事務(wù)中，所有這些行中的“值”字段都會(huì)更新。它們獲得NULL值，我們?cè)俅屋敵鏊鼈儭Ｖ螅聞?wù)將回滾，然后再次輸出Ind = 1的表變量的所有行。結(jié)果表明，事務(wù)回滾不會(huì)取消對(duì)表變量所做的更改。

因此，當(dāng)我們?cè)谑聞?wù)的表變量中實(shí)現(xiàn)更改并回滾該事務(wù)時(shí)，更改將保留。這將表變量與常規(guī)表和臨時(shí)表區(qū)分開。

從2019版本開始，SQL Server根據(jù)其實(shí)際執(zhí)行計(jì)劃存儲(chǔ)先前進(jìn)行的查詢的實(shí)際參數(shù)值。如果沒有RECOMPILE選項(xiàng)的第一個(gè)查詢?cè)趫?zhí)行中不是最佳選擇，則將優(yōu)化所有用于相同或相似查詢的后續(xù)執(zhí)行計(jì)劃。

dbForge Studio for SQL Server中的實(shí)際查詢執(zhí)行計(jì)劃概述

在dbForge Studio for SQL Server中，實(shí)際的查詢執(zhí)行計(jì)劃如下所示：

我們可以在左側(cè)看到實(shí)際的查詢執(zhí)行計(jì)劃。它是一個(gè)分層的樹結(jié)構(gòu)，每個(gè)塊中包含以下元素：

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

在右側(cè)，我們將詳細(xì)了解每個(gè)元素。

結(jié)論

總而言之，我們回顧了SQL Server表變量和臨時(shí)表的基礎(chǔ)知識(shí)，比較了本地臨時(shí)表，全局臨時(shí)表和表變量之間的差異，并以查詢執(zhí)行計(jì)劃為例進(jìn)行了舉例說(shuō)明。

要了解有關(guān)刪除本地臨時(shí)表的更多信息，請(qǐng)閱讀我們的下一篇文章。

點(diǎn)擊下載dbForge Studio for SQL Server，并通過(guò)30天免費(fèi)試用版自行檢查此功能！慧都限時(shí)活動(dòng)，現(xiàn)dbForge Studio SQL Sever直降3000，在線訂購(gòu)正版授權(quán)最低只要1710元！