Can you optimize a view with many UNION ALLs to external databases?

Question

Given these views in a Sql Server 2012 database:

create view vManyUnions as
    select 1 as p, a from vSubQuery_1
    union all
    select 2 as p, a from vSubQuery_2
    union all
    select 3 as p, a from vSubQuery_3
    ...
    union all
    select n as p, a from vSubQuery_n

create view vSubQuery_1 as
    select a from [externalDb].[dbo].[someTable] where b = something

create view vSubQuery_2 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElse

create view vSubQuery_3 as
    select a from [externalDb].[dbo].[someTable] where b = somethingElseStill

...

create view vSubQuery_n as
    select a from [externalDb].[dbo].[someTable] where b = youGetTheIdea

And given this query:

select * from vManyUnions where p = 1

What optimizations, if any, can be made such that Sql Server 2012 only evaluates vSubQuery_1 when running the query?

*Note again, the sub query views call external databases.

FOLLOW UP: This was an example I contrived from a proprietary, much more complicated case (~1K subviews) where the execution planner includes more subqueries than the one subquery I expect. Thanks to those that replied and comfirmed that Sql should optimize queries against this type of view. It made me realize I was being lazy and hadn't actually tested my basic assumptions on how the optimizer works.

So I did that. I built a simple proof of concept and confirmed very fast that Sql optimizes the query. Following that, I worked more with the real-world view that still doesn't optimize. I found that, strangely, the planner is optimizing out many of the subviews. There are particular subviews that the planner always includes though. At this point I don't know why the planner always includes these particular subviews, any ideas? ...otherwise I'll post something if we figure it out.

Answer 1

Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation.

If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero.

You can see me demonstrate this in a talk that was recorded in 2009 for SQLBits, at http://bit.ly/Simplification