WHERE IN performs much better with Table Type than with hardcoded values in SQL Server

I have 2 queries that are essentially the same (at least if I didn't miss something).

DECLARE @siloIds SiloIdsTableType
INSERT INTO @siloIds VALUES
(1),(2),(3)

-- Query 1
SELECT *
FROM [Transaction]
WHERE
SiloId IN (1,2,3)
AND Time > '2000-02-01'

-- Query 2
SELECT *
FROM [Transaction]
WHERE
SiloId IN (select SiloId from @siloIds)
AND Time > '2000-02-01'


I was thinking that one cannot beat constants declared in the query itself, but apparently the first query is few times slower than the second one. It seems that SQL server is not smart enough to provide a good plan for the hardcoded values, or am I missing something here?

It seems that one shall not use where in with a long list and TVP should be always favored

P.S. I use thousand values instead of 1,2,3 in my query

P.P.S. I have a non-clustered index on SiloId ASC, Time ASC, but it seems that the first query is not using it favoring clustered index scan for some reason.

P.P.P.S. Execution plan shares the cost 14% to 86% in favor of the second query

Execution plan:



Continue reading...