2009-06-16

SQL Server 2005 vs Table-valued Functions

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.

at