... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)


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:

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:

  filter AS (
    FROM dbo.tmpProjectFilter
    WHERE ...
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.

Blog Archive