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?

Question

This Question brings a situation where the Query Optimizer chooses poorly the seek predicate among the existing predicates of a simple query. After running some tests I got to the conclusion that the poor decision is due to the use of a local temp table instead of a global temp table or a regular table.

db fiddle: Local Temp Table, Global Temp Table, Regular Table.

I couldn't find any characteristic on the Temporary Tables doc that would explain the different behavior we see when using a local temporary table instead of a global temporary table or a regular table. Is there a logical reason for this or could it be a bug?

Answer 1

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 <> 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.