行数较少的临时表导致聚集索引查找，而更多行导致聚集索引扫描 - SQL Server

我有下表和数据：

CREATE TABLE myTable (
    ID INT IDENTITY(1,1) PRIMARY KEY,
    Column1 VARCHAR(50),
    Column2 VARCHAR(50),
    Column3 VARCHAR(50),
    Column4 VARCHAR(50),
    Column5 VARCHAR(50),
    Column6 VARCHAR(50),
    Column7 VARCHAR(50),
    Column8 VARCHAR(50),
    Column9 VARCHAR(50),
    Column10 VARCHAR(50)
)

DECLARE @i INT = 1
DECLARE @j INT = 1
DECLARE @distinct_value_count INT = 20
DECLARE @distinct_value_count_with_more_rows INT = 3
DECLARE @rows_per_distinct_value INT = (20000 - (@distinct_value_count_with_more_rows * 2000)) / (@distinct_value_count - @distinct_value_count_with_more_rows)

WHILE @i <= @distinct_value_count
BEGIN
    DECLARE @current_rows_per_value INT = @rows_per_distinct_value
    IF @i <= @distinct_value_count_with_more_rows
    BEGIN
        SET @current_rows_per_value = @rows_per_distinct_value + 2000
    END
    
    SET @j = 1
    WHILE @j <= @current_rows_per_value
    BEGIN
        INSERT INTO myTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10)
        VALUES ('Value' + CAST(@i AS VARCHAR(2)),
                'Value' + CAST(@j AS VARCHAR(5)),
                'Value' + CAST(@j + 1 AS VARCHAR(5)),
                'Value' + CAST(@j + 2 AS VARCHAR(5)),
                'Value' + CAST(@j + 3 AS VARCHAR(5)),
                'Value' + CAST(@j + 4 AS VARCHAR(5)),
                'Value' + CAST(@j + 5 AS VARCHAR(5)),
                'Value' + CAST(@j + 6 AS VARCHAR(5)),
                'Value' + CAST(@j + 7 AS VARCHAR(5)),
                'Value' + CAST(@j + 8 AS VARCHAR(5)))
        SET @j = @j + 1
    END
    
    SET @i = @i + 1
END

Alter Table dbo.myTable
Add Column11 varchar(50), Column12 varchar(50)

Alter Table dbo.myTable
Add dateModified datetime

Update dbo.myTable
  set Column11 = Column1
     ,Column12 = Column1

Update Top (10) dbo.myTable
   Set Column11 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value7'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value6'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column11 = 'Value5'
  Where Column1 = 'Value1'

Update Top (10) dbo.myTable
   Set Column12 = 'Value5'
  Where Column1 = 'Value1'

Update dbo.myTable
  set dateModified = getdate() + ID

CREATE NONCLUSTERED INDEX [Idx_col] ON [dbo].[myTable]
(
    [Column1] ASC,
    [Column11] ASC,
    [Column12] ASC,
    [dateModified] ASC
)
INCLUDE([Column5],[Column6]) 

我必须根据几列进行过滤并返回表中的所有列。为此，我有一个索引涵盖需要过滤的列。我将查询分为两部分：

1. 获取所有满足过滤器的主键行并将它们存储在临时表中。此查询使用非聚集索引。

2. 将此临时表连接回主键列上的主表，以便聚集索引用于获取所有列。

但是，当我尝试这样做时，我遇到了一个问题。在第一个场景中，我将所有过滤的行放入一个临时表中，然后当我将它连接回主表时，它正在执行聚簇索引扫描。在第二种情况下，我只将前 50 行放入临时表，当我将其连接到主表时，它正在执行聚集索引查找。我很困惑为什么会这样。在这两种情况下，临时表上都没有索引。如果有人能帮助我了解发生了什么，我将不胜感激。谢谢你！

场景 1：

SELECT id
INTO #tmpId
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())

SELECT *
FROM myTable m
JOIN #tmpId t
ON m.id = t.id

drop table if exists #tmpId

执行计划场景 1： https://www.brentozar.com/pastetheplan/? id=rkDAD-aLh

场景 2：

SELECT id
INTO #tmpId
FROM myTable
WHERE Column1= 'Value1'
AND Column11 In( 'Value1','Value5','Value6', 'Value7')
And Column12 In ('Value1','Value6')
And dateModified > dateAdd(day,-5, getdate())
Order by dateModified desc offset 0 rows fetch next 50 rows only

SELECT *
FROM myTable m
JOIN #tmpId t
ON m.id = t.id

drop table if exists #tmpId

场景 2 执行计划： https://www.brentozar.com/pastetheplan/? id=rJVbuWaLh