I have two tables, let's call them TableA and TableB.
TableA TableB ------ ------ ID ID Date Date Foo Bar
Both tables have an index on their date field.
Now, I want to create a view that has a full outer join between the two tables on the Date field, to show me the
Foo value for that date (if there is one) along with the
Bar for that date.
create view vFooBar as select isnull(a.Date, b.Date) Date, a.Foo, b.Bar from TableA a full outer join TableB b on a.Date = b.Date
Then I run a query:
select * from vFooBar where Date = '20141201'
This works... but the coalescing of
b.Date fools the compiler into not using the indexes, and I get a horrible execution plan involving table scans.
How can I create this view in such a way that querying it will still use my indexes?