Avoid Table Scan Using Multi Union View

Anyone see a way to trick the optimizer into not scanning all tables involved in the view?


-- create two test tables
create table dbo.test1
(testID int, TestName varchar(10))

create table dbo.test2
(testID int, TestName varchar(10))

-- populate
declare @i int
set @i = 1000
while @i > 0
begin
insert into dbo.test1
select @i, '1.' + cast(@i as varchar(5))
set @i = @i - 1
end

insert into dbo.test2
select 1, '2.1' union all
select 2, '2.2'
go

-- create view
create view dbo.vw_Test
as
select1 as QueryID,
TestName
fromdbo.Test1
union all
select2 as QueryID,
TestName
fromdbo.Test2;
go


-- this works as i want, only scans table dbo.Test2
select *
fromdbo.vw_Test
whereQueryId = 2

-- joining to a table triggers scan of both tables in view:
declare @table table (QueryID int)
insert into @table
select 2;

selectvt.TestName
fromdbo.vw_Test vt
join@table t on
vt.QueryID = t.QueryID



Using the showplan I can see why the optimizer ends up scanning all tables, but maybe there is a way to force it to use the QueryID param evaluation earlier in the filtering.

Nathan Skerl