We use some 'aggregate' views to select from multiple tables using a discriminator (note: these views are not partitioned views, because the discriminator is not in the base tables). This normally works well when using
option(recompile), as the query planner will eliminate the non-reachable
union all paths before selection of a query plan.
However, this constant-folding optimization appears defeated when selecting the result into a scalar variable. Selecting the result into a temporary table variable does not de-optimize the recompilation.
Here is a reproduction case in SQL Server 2017:
-- 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
Normal query, which results in an optimized query plan touching only one of the
union all branches:
declare @descrim int = 2; select count(col1) from [test].test_view where descrim = @descrim option (recompile) -- explicit recompile here "works"
However, as soon as a "select into scalar variable" is used, the plan becomes de-optimized as it does not eliminate the non-used union. (The plan is still correctly optimized when using a literal value in the query text.)
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!
1. Is this de-optimization "expected"?
2. Where is this significant de-optimization behavior with respect to
option(recompile) and/or selecting into a scalar variable documented or otherwise discussed in depth?
3. Is there an simple way to get an recompile-optimized plan with
select @x = .. without using a temporary table (variable)?
While during query execution the
union all will prevent actual IO access to the secondary artifact, such is still an issue with query plan generation. In the specific error case spawning this question, leaving in multiple tables for consideration prevents SQL Server from choosing an appropriate seek plan and the resulting plan options are very poor choices in the given domain.
The first "good" plan:
The second and "bad" plan:
This "bad" plan also has an implicit conversion warning, making me suspect that the select into a scalar variable might be bypassing many different optimizations - or even ignoring the
option(recompile) hint entirely.