Why using a local temp table (instead of a global temp table or a regular table) influences the Query Optimizer to choose a poor query plan?
This just looks like a quirk with the optimisation of that query producing a slightly different tree shape when simple parameterisation is attempted vs when it isn't.
This is not attempted in the case of the local temp table but is for the global temp table and permanent table.
The auto parameterisation does not succeed in any case but when attempted on this query it results in a slightly different tree shape with the order of the predicates retained from the query text (rather than with the &lt;&gt; first as happens when it is not attempted).
My supposition is that this ordering the predicates end up in before cost based optimisation somewhat arbitrarily determines what will be the seek predicate and what will be the residual in this case (i.e. the competing options aren't considered during cost based optimisation).
Adding and 1=1 is one way of preventing this and the plan reverts.