title: Performance of different approaches to time-based data
body: <p>I'm asking this in the context of PerformanceDBA's claim in <a href="https://stackoverflow.com/questions/4083464/design-database-relating-to-time-attribute/4115511#4115511">this answer to another question</a> that this query:</p>
  
  <pre><code>SELECT  ProductId,
          Description
      FROM  Product       p,
            ProductStatus ps
      WHERE p.ProductId = ps.ProductId  -- Join
      AND   StatusCode  = 2             -- Request
      AND   DateTime    = (             -- Current Status on the left ...
          SELECT MAX(DateTime)          -- Current Status row for outer Product
              FROM  ProductStatus ps_inner
              WHERE p.ProductId = ps_inner.ProductId
              )
  </code></pre>
  
  <p>using a ProductStatus table that holds only an effective (start) date for a status that changes over time, will outperform this query:</p>
  
  <pre><code>SELECT  ProductId,
          Description
      FROM  Product       p,
            ProductStatus ps
      WHERE p.ProductId = ps.ProductId  -- Join
      AND   StatusCode  = 2             -- Request
      AND   getdate() BETWEEN DateFrom AND Dateto
  </code></pre>
  
  <p>using a ProductStatus table that holds both a start and an end date for the status.</p>
  
  <p>While I accept the other claims made for the first approach being better than the second, I would however expect the second approach to be <strong>faster</strong> (based on my experience with Oracle only) because it merely filters the data rather than performing an additional subquery and comparing with it.  </p>
  
  <p>I'd like to know how Sybase or SQL Server would process these queries, and what the relative performance is in some simple tests.</p>