SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create schema test
-- A table, don't need any data.
create table [test].test_table (col1 int, primary key (col1))

-- A simple 'aggregate' view. Using the same table here is irrelevant and,
-- while the view shows the scenario, it might not be required to reproduce the issue.
create view [test].test_view as
select col1, descrim = 1 from [test].test_table
union all
select col1, descrim = 2 from [test].test_table
set statistics xml on;
declare @descrim int = 2;

select count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here "works"

(No column name)

0

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3335.7"><BatchSequence><Batch><Statements><StmtSimple StatementText="select count(col1)&#xa;from [test].test_view&#xa;where descrim = @descrim&#xa;option (recompile)" StatementId="1" StatementCompId="3" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00328438" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="TRIVIAL" QueryHash="0x5382CBA375D55261" QueryPlanHash="0xD126A4DDC0DE878C" CardinalityEstimationModelVersion="140"><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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="160"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="682616"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00328438" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1009],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1009"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.1e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00328438" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1009"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="1.8e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00328328" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="3" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_67c7987bc0634715af5d0ee121871d5c]" Schema="[test]" Table="[test_table]" Index="[PK__test_tab__357D0D3EE0E22338]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="(2)=(2)"><Compare CompareOp="EQ"><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>

declare @descrim int = 2;
declare @brokeit int;

select @brokeit = count(col1)
from [test].test_view
where descrim = @descrim
option (recompile) -- explicit recompile here does NOT optimize plan for @descrim!

Microsoft SQL Server 2005 XML Showplan

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.481" Build="14.0.3335.7"><BatchSequence><Batch><Statements><StmtSimple StatementText="select @brokeit = count(col1)&#xd;&#xa;from [test].test_view&#xd;&#xa;where descrim = @descrim&#xd;&#xa;option (recompile)" StatementId="1" StatementCompId="2" StatementType="SELECT" RetrievedFromCache="false" StatementSubTreeCost="0.00656906" StatementEstRows="1" SecurityPolicyApplied="false" StatementOptmLevel="FULL" QueryHash="0x5382CBA375D55261" QueryPlanHash="0xCEB197835146E0B8" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="140"><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 DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="16" CompileTime="0" CompileCPU="0" CompileMemory="240"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"></MemoryGrantInfo><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="682616"></OptimizerHardwareDependentProperties><TraceFlags IsCompileTime="1"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="460" Scope="Global"></TraceFlag><TraceFlag Value="8017" Scope="Global"></TraceFlag></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"></QueryTimeStats><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="0" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00656906" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1008"></ColumnReference></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1008"></ColumnReference><ScalarOperator ScalarString="CONVERT_IMPLICIT(int,[Expr1011],0)"><Convert DataType="int" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1011"></ColumnReference></Identifier></ScalarOperator></Convert></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Stream Aggregate" LogicalOp="Aggregate" EstimateRows="1" EstimateIO="0" EstimateCPU="1.7e-006" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00656906" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1011"></ColumnReference></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="1" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><StreamAggregate><DefinedValues><DefinedValue><ColumnReference Column="Expr1011"></ColumnReference><ScalarOperator ScalarString="Count(*)"><Aggregate AggType="countstar" Distinct="0"></Aggregate></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="2" PhysicalOp="Concatenation" LogicalOp="Concatenation" EstimateRows="2" EstimateIO="0" EstimateCPU="2e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00656736" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Concat><DefinedValues></DefinedValues><RelOp NodeId="3" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00328358" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="4" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="0" ActualExecutions="0"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_67c7987bc0634715af5d0ee121871d5c]" Schema="[test]" Table="[test_table]" Index="[PK__test_tab__357D0D3EE0E22338]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="(1)=[@descrim]"><Compare CompareOp="EQ"><ScalarOperator><Const ConstValue="(1)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@descrim"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp><RelOp NodeId="5" PhysicalOp="Filter" LogicalOp="Filter" EstimateRows="1" EstimateIO="0" EstimateCPU="4.8e-007" AvgRowSize="9" EstimatedTotalSubtreeCost="0.00328358" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualRebinds="1" ActualRewinds="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><Filter StartupExpression="1"><RelOp NodeId="6" PhysicalOp="Clustered Index Scan" LogicalOp="Clustered Index Scan" EstimateRows="1" EstimatedRowsRead="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="9" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="0" Batches="0" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualEndOfScans="1" ActualExecutions="1"></RunTimeCountersPerThread></RunTimeInformation><IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues></DefinedValues><Object Database="[fiddle_67c7987bc0634715af5d0ee121871d5c]" Schema="[test]" Table="[test_table]" Index="[PK__test_tab__357D0D3EE0E22338]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"></Object></IndexScan></RelOp><Predicate><ScalarOperator ScalarString="(2)=[@descrim]"><Compare CompareOp="EQ"><ScalarOperator><Const ConstValue="(2)"></Const></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="@descrim"></ColumnReference></Identifier></ScalarOperator></Compare></ScalarOperator></Predicate></Filter></RelOp></Concat></RelOp></StreamAggregate></RelOp></ComputeScalar></RelOp><ParameterList><ColumnReference Column="@brokeit" ParameterDataType="int" ParameterCompiledValue="NULL" ParameterRuntimeValue="(0)"></ColumnReference><ColumnReference Column="@descrim" ParameterDataType="int" ParameterCompiledValue="(2)" ParameterRuntimeValue="(2)"></ColumnReference></ParameterList></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>