為什麼表變數強制索引掃描而臨時表使用查找和書籤查找？

我試圖理解為什麼使用表變數會阻止優化器使用索引查找然後書籤查找與索引掃描。

填充表格：

CREATE TABLE dbo.Test 
(
   RowKey INT NOT NULL PRIMARY KEY, 
   SecondColumn CHAR(1) NOT NULL DEFAULT 'x',
   ForeignKey INT NOT NULL 
) 

INSERT dbo.Test 
(
   RowKey, 
   ForeignKey
) 
SELECT TOP 1000000 
   ROW_NUMBER() OVER (ORDER BY (SELECT 0)),
   ABS(CHECKSUM(NEWID()) % 10)     
FROM sys.all_objects s1
CROSS JOIN sys.all_objects s2 

CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey) 

使用單個記錄填充表變數，並嘗試通過搜尋外鍵列來查找主鍵和第二列：

DECLARE @Keys TABLE (RowKey INT NOT NULL) 

INSERT @Keys (RowKey) VALUES (10)

SELECT 
   t.RowKey,
   t.SecondColumn
FROM
   dbo.Test t 
INNER JOIN 
   @Keys k
ON
   t.ForeignKey = k.RowKey

下面是執行計劃：

現在使用臨時表進行相同的查詢：

CREATE TABLE #Keys (RowKey INT NOT NULL) 

INSERT #Keys (RowKey) VALUES (10) 

SELECT 
   t.RowKey,
   t.SecondColumn
FROM
   dbo.Test t 
INNER JOIN 
   #Keys k
ON
   t.ForeignKey = k.RowKey

此查詢計劃使用搜尋和書籤查找：

為什麼優化器願意使用臨時表而不是表變數進行書籤查找？

此範例中使用 table 變數來表示來自儲存過程中使用者定義的表類型的數據。

我意識到如果外鍵值出現數十萬次，則索引搜尋可能不合適。在這種情況下，掃描可能是更好的選擇。對於我創建的場景，沒有值為 10 的行。我仍然認為該行為很有趣，並想知道是否有原因。

SQL小提琴

添加OPTION (RECOMPILE)並沒有改變行為。UDDT 有一個主鍵。

@@VERSION是 SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64)（內部版本 7601：Service Pack 1）（管理程序）

該行為的原因是 SQL Server 無法確定與 ForeignKey 匹配的行數，因為沒有以 RowKey 作為前導列的索引（它可以從 #temp 表的統計資訊中推斷出這一點，但那些不表變數/UDTTs 存在），因此它估計有 100,000 行，掃描比查找+查找更好地處理。當 SQL Server 意識到只有一行時，為時已晚。

您可能能夠以不同的方式建構您的 UDTT；在更現代的 SQL Server 版本中，您可以在表變數上創建二級索引，但此語法在 2008 R2 中不可用。

順便說一句，如果您嘗試通過提示嵌套循環連接來避免點陣圖/探針，您可以獲得搜尋行為（至少在我有限的試驗中）：

DECLARE @Keys TABLE (RowKey INT PRIMARY KEY); -- can't hurt

INSERT @Keys (RowKey) VALUES (10);

SELECT 
    t.RowKey
   ,t.SecondColumn
FROM
   dbo.Test t 
INNER JOIN 
   @Keys k
ON
   t.ForeignKey = k.RowKey
   OPTION (LOOP JOIN);

幾年前，我從 Paul White 那裡學到了這個技巧。當然，您應該小心在生產程式碼中放置任何類型的連接提示 - 如果人們對底層對象進行更改並且特定類型的連接不再可能或不再是最佳連接，這可能會失敗。

對於更複雜的查詢，並且當您遷移到 SQL Server 2012 或更高版本時，跟踪標誌 2453可能會有所幫助。不過，那個標誌對這個簡單的連接沒有幫助。同樣的免責聲明也適用——如果沒有大量的文件和嚴格的回歸測試程序，這只是你通常不應該做的另一種事情。

此外，Service Pack 1 已不再受支持，您應該使用Service Pack 3 + MS15-058。

引用自：https://dba.stackexchange.com/questions/108352