SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE dbo.table3(id int identity(1,1) PRIMARY KEY, Type int);
CREATE INDEX ix ON dbo.table3(Type);

INSERT dbo.table3(Type) VALUES(1),(1),(2),(4),(3),(7);
6 rows affected

SET SHOWPLAN_XML ON;
DECLARE @Daily bit = 1;

    DECLARE @Constants table(Daily bit, Type int, 
      PRIMARY KEY(Daily, Type));

    INSERT @Constants VALUES(1,1),(1,2),(1,3),(1,4),
                            (0,5),(0,6),(0,7),(0,8);

    SELECT *
      FROM dbo.table3 AS t3
      WHERE EXISTS
      (
        SELECT 1 
          FROM @Constants AS c
          WHERE c.Daily = @Daily
          AND c.Type = t3.Type
      );


Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.539" Build="15.0.4223.1"><BatchSequence><Batch><Statements><StmtSimple StatementText="DECLARE @Daily bit = 1;&#xa;&#xa;    " StatementId="1" StatementCompId="1" StatementType="ASSIGN" RetrievedFromCache="false"></StmtSimple><StmtSimple StatementText="DECLARE @Constants table(Daily bit, Type int, &#xa;      PRIMARY KEY(Daily, Type));&#xa;&#xa;    INSERT @Constants VALUES(1,1),(1,2),(1,3),(1,4),&#xa;                            (0,5),(0,6),(0,7),(0,8)" StatementId="2" StatementCompId="3" StatementType="INSERT" RetrievedFromCache="false" StatementSubTreeCost="0.010017" StatementEstRows="8" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0xF653D0D39BC8EBA9" QueryPlanHash="0x43255F3A91E0E3E0" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="CouldNotGenerateValidParallelPlan" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1269096"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="0" PhysicalOp="Clustered Index Insert" LogicalOp="Insert" EstimateRows="8" EstimateIO="0.01" EstimateCPU="8e-06" AvgRowSize="9" EstimatedTotalSubtreeCost="0.010017" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><Update DMLRequestSort="0"><Object Table="[@Constants]" Index="[PK__#A121B08__0CAA35338632D7F2]" Storage="RowStore"></Object><SetPredicate><ScalarOperator ScalarString="[Daily] = RaiseIfNullInsert([Expr1020]),[Type] = [Union1019]"><ScalarExpressionList><ScalarOperator><MultipleAssign><Assign><ColumnReference Column="Daily"></ColumnReference><ScalarOperator><Intrinsic FunctionName="RaiseIfNullInsert"><ScalarOperator><Identifier><ColumnReference Column="Expr1020"></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Assign><Assign><ColumnReference Column="Type"></ColumnReference><ScalarOperator><Identifier><ColumnReference Column="Union1019"></ColumnReference></Identifier></ScalarOperator></Assign></MultipleAssign></ScalarOperator></ScalarExpressionList></ScalarOperator></SetPredicate><RelOp NodeId="1" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="8" EstimateIO="0" EstimateCPU="8e-07" AvgRowSize="12" EstimatedTotalSubtreeCost="8.957e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1019"></ColumnReference><ColumnReference Column="Expr1020"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1020"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(bit,[Union1018],0)"><Convert DataType="bit" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Union1018"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="8" EstimateIO="0" EstimateCPU="8.157e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="8.157e-06" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Union1018"></ColumnReference><ColumnReference Column="Union1019"></ColumnReference></OutputList><ConstantScan><Values><Row><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(2)"><Const ConstValue="(2)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(3)"><Const ConstValue="(3)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator ScalarString="(4)"><Const ConstValue="(4)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator ScalarString="(5)"><Const ConstValue="(5)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator ScalarString="(6)"><Const ConstValue="(6)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator ScalarString="(7)"><Const ConstValue="(7)"></Const></ScalarOperator></Row><Row><ScalarOperator ScalarString="(0)"><Const ConstValue="(0)"></Const></ScalarOperator><ScalarOperator ScalarString="(8)"><Const ConstValue="(8)"></Const></ScalarOperator></Row></Values></ConstantScan></RelOp></ComputeScalar></RelOp></Update></RelOp></QueryPlan></StmtSimple><StmtSimple StatementText=";&#xa;&#xa;    SELECT *&#xa;      FROM dbo.table3 AS t3&#xa;      WHERE EXISTS&#xa;      (&#xa;        SELECT 1 &#xa;          FROM @Constants AS c&#xa;          WHERE c.Daily = @Daily&#xa;          AND c.Type = t3.Type&#xa;      )" StatementId="3" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00657144" StatementEstRows="1.2" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x2ADB70B2B4973D74" QueryPlanHash="0x74CAB1835F6FC6CB" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="150"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"></StatementSetOptions><QueryPlan NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="24" CompileTime="0" CompileCPU="0" CompileMemory="216"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1269096"></OptimizerHardwareDependentProperties><OptimizerStatsUsage><StatisticsInfo LastUpdate="2022-10-24T23:31:25.26" ModificationCount="0" SamplingPercent="100" Statistics="[ix]" Table="[table3]" Schema="[dbo]" Database="[fiddle_497980e3785f4fcba41a61382448045a]"></StatisticsInfo></OptimizerStatsUsage><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1.2" EstimateIO="0" EstimateCPU="5.016e-06" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00657144" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="Type"></ColumnReference></OutputList><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="@Constants" Alias="[c]" Column="Type"></ColumnReference></OuterReferences><RelOp NodeId="2" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="11" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@Constants" Alias="[c]" Column="Type"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@Constants" Alias="[c]" Column="Type"></ColumnReference></DefinedValue></DefinedValues><Object Table="[@Constants]" Index="[PK__#A121B08__0CAA35338632D7F2]" Alias="[c]" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Table="@Constants" Alias="[c]" Column="Daily"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[@Daily]"><Identifier><ColumnReference Column="@Daily"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="3" PhysicalOp="Index Seek" LogicalOp="Index Seek" EstimateRows="1.2" EstimatedRowsRead="1.2" EstimateIO="0.003125" EstimateCPU="0.00015832" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00328332" TableCardinality="6" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="id"></ColumnReference><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="Type"></ColumnReference></OutputList><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="id"></ColumnReference></DefinedValue><DefinedValue><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="Type"></ColumnReference></DefinedValue></DefinedValues><Object Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Index="[ix]" Alias="[t3]" IndexKind="NonClustered" Storage="RowStore"></Object><SeekPredicates><SeekPredicateNew><SeekKeys><Prefix ScanType="EQ"><RangeColumns><ColumnReference Database="[fiddle_497980e3785f4fcba41a61382448045a]" Schema="[dbo]" Table="[table3]" Alias="[t3]" Column="Type"></ColumnReference></RangeColumns><RangeExpressions><ScalarOperator ScalarString="@Constants.[Type] as [c].[Type]"><Identifier><ColumnReference Table="@Constants" Alias="[c]" Column="Type"></ColumnReference></Identifier></ScalarOperator></RangeExpressions></Prefix></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp></NestedLoops></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>