Conditional UNION ALL in table function

Question

So use case is as follows - there're some parameter, based on which I want to select data from one table or another.

create table dbo.TEST1 (id int primary key, name nvarchar(128))
create table dbo.TEST2 (id int primary key, name nvarchar(128))

So I've created function like this:

create function [dbo].[f_TEST]
(
    @test bit
)
returns table
as
return (
    select id, name from TEST1 where @test = 1

    union all

    select id, name from TEST2 where @test = 0
)

When I run it with constant, the execution plan is great - only one table is scanned

select * from dbo.f_TEST(1)

But, then, when I use variable, plan is not that good - both tables are scanned

declare @test bit = 1

select * from dbo.f_TEST(@test)

So are there any hints (or tricks) to force SQL Server to understand that in a certain query only one table should be scanned?

Answer 1

If your function is inline-TVP(as in example) then you could use:

declare @test bit = 1
select * from dbo.f_TEST(@test) OPTION (RECOMPILE);

Then in both cases you will get single clustered index scan.

DBFiddle Demo

From Option RECOMPILE:

When compiling query plans, the RECOMPILE query hint uses the current values of any local variables in the query and, if the query is inside a stored procedure, the current values passed to any parameters.

Answer 2

It works fine as is. Take a look at "Number of Executions" on the relevant table, as you change your parameter value. The tables that will be excluded appear in the plan, because they must be considered, but that does not mean they will be scanned.

Also, look at the Startup Expression on the Filter: