lundi 1 décembre 2014

Full outer joins, coalescing, indexes and views


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 a.Date and 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?





Aucun commentaire:

Enregistrer un commentaire