Replace index scan on index seek in SQL Server SELECT query

Question

The first query works fine with index seek (https://www.brentozar.com/pastetheplan/?id=B1XMM_YeW):

SELECT *
FROM dbo.MessageLast c
    WHERE c.Stat_Date >= '2017-04-25' 
                AND c.Stat_Date <= '2017-04- 26' 
                AND c.partner_id IN (111294, 100001, 111295, 111435, 111296, 118155,111498, 118397);

Second query bad with idex scan (https://www.brentozar.com/pastetheplan/?id=H1uvEdFgZ):

CREATE TABLE #t (partner_id Int primary key clustered);
INSERT INTO  #t(partner_id) 
values  (111294), (100001), (111295), (111435), (111296), (118155),(111498), (118397);

SELECT *
FROM dbo.MessageLast c
    where  c.Stat_Date >= '2017-04-25' 
                and c.Stat_Date <= '2017-04- 26' 
                and c.partner_id in (select partner_id from #t);

Third query with EXISTS also is bad (https://www.brentozar.com/pastetheplan/?id=ryk3IntgZ):

 SELECT *
 FROM   dbo.MessageLast c with (forceseek)

 WHERE  c.Stat_Date >= '2017-04-25'
        and c.Stat_Date <= '2017-04-26'
        and exists(select
                       * 
                   from  #d as d where  c.partner_id = d.partner_id)

Changing temp table #t on table variable or standard table doesn't help - same plan like the second query.

Simple demo script:

create table _tBig (
  dt Date
, id1 Int
, id2 Int
, id3 Int
, id4 Int
, txt Varchar(500)
) 
create clustered index PK_tBig  on _tBig (id4,dt)



--truncate table _tBig

declare @i Int = 1
set nocount on
while @i < 10000
begin
    insert  into _tBig with (tablock)
            (dt
           , id1
           , id2
           , id3
           , id4
           , txt
            )
    select top (1000)
            DateAdd(day, Rand() * 365 + 1, GetDate())
          , Row_Number() over (order by O.[object_id])
          , Rand() * 10000000 + 1
          , Rand() * 10000000 + 1
          , Rand() * 10000000 + 1
          , NewId()
    from    sys.all_objects O

    set @i += 1

end 


create table _tId (id1 Int)



--truncate table _tId

insert  into _tId
        (id1
        )
select top (5)
        Row_Number() over (order by O.[object_id])
from    sys.all_objects O



select  *
from    _tBig as tb
where   tb.dt >= '20170517'
        and tb.dt < '20170519'
        and tb.id1 in (1, 2, 3, 4, 5)

create index IX_Big on _tBig (dt,id1)

select  *
from    _tBig as tb
inner join _tId as ti on ti.id1 = tb.id1
where   tb.dt >= '20170517'
        and tb.dt < '20170519'


create index IX_Big2 on _tBig (id1,dt)

select  *
from    _tBig as tb
inner join _tId as ti on ti.id1 = tb.id1
where   tb.dt >= '20170517'
        and tb.dt < '20170519'

--drop table dbo._tBig
--drop table dbo._tId

Answer 1

This happens because index is not covering: you are selecting ALL fields in table.

When index does not cover the query sql server shoud do 2 steps:

1) Index seek to find PK values (and included columnsd) that mathed predicate

2) Key lookup (seek in clustered index) for each row to find values of column which was not included in index.

Therefore in some cases query optimizer decides that full table scan is faster than these two steps.

You can replace SELECT * with SELECT dt (make index covering) and you receive an index seek. Here is more info: What is a Covered Index?