Conditional UNION ALL in table function

Viewed 1607

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)

enter image description here

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)

enter image description here

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

5 Answers
Related