Experimentation has revealed that SQL Server 2005 will sometimes come up with a better query plan if you express joins using IN clauses than if you express the same joins explicitly. For example:
SELECT ... FROM dbo.fnProduction(...) AS prod INNER JOIN dbo.tmpProjectFilter AS filter ON filter.projectId = prod.projectId AND ...
expresses a join between a normal table and a non-procedural table-valued function (i.e. pure query). It runs quite slowly. But if you re-express the query thus:
WITH filter AS ( SELECT projectId FROM dbo.tmpProjectFilter WHERE ... ) SELECT ... FROM dbo.fnProduction(...) AS prod WHERE prod.projectId IN (SELECT * FROM filter)
... it runs faster. It would seem that the latter form is doing a better job than the former when injecting the relevant query conditions into the table function.
This is a tool to consider when "optimizing" "declarative" SQL.