title: How to Optimize View With Union All
body: <p>I have a view with two separate queries with a union all. For example:</p>
  
  <pre><code>CREATE VIEW MyView AS
  
  SELECT foo.id, bar.data
  FROM foo
  INNER JOIN bar ON foo.id = bar.id
  
  UNION ALL
  
  SELECT baz.id, baz.data
  FROM baz
  </code></pre>
  
  <p>I then run a select statement against the view like this:</p>
  
  <pre><code>SELECT * FROM MyView WHERE id = 1
  </code></pre>
  
  <p>The id of 1 only exists in the first select statement. The one with the <code>foo</code> and <code>bar</code> tables, but the union with the <code>baz</code> table is still causing it to run slower. It is faster if take out the union. I have to leave it there though, because sometimes an id will be used that will return results from the second select statement.</p>
  
  <p>My question is: Is there any way to help sql server know that there are no results in the second part of the union all, so it doesn't have to run that query?</p>
  
  <p>All of this data used to be in one table. This is a new data structure and all of our older applications will be relying on this view to read data from this new data structure. This is why we don't know which table will actually have the results beforehand.</p>