Hugo Kornelis (11/4/2010)
David Ziffer (11/4/2010)
... a query that references an inline table-valued function almost always runs rings around a query that uses a multi-statement table-valued function, for the reason I explained in my previous post. If you build the Person and PersonName tables you use in the last example in your article and populate them with a few thousand rows of sample data, you should see the difference. Bump the tables up to a few million rows, and it becomes painfully obvious.
Well the thing that makes me suspicious here is that your "union" code potentially always hits both tables, whereas the RAP-generated code can hit only the one table that it needs to hit. In the "union" code you presented there is the danger that the optimizer would not be smart enough to realize that in the event of the @AsOfDate being NULL, it doesn't need to even touch the archive table at all.
But I think I will try your suggested test soon to see if your assertion holds true.