title: Suitable indexes for sorting in ranking functions
body: <p>I have a table which keeps parent-child-relations between items. Those can be changed over time, and it is necessary to keep a complete history so that I can query how the relations were at any time.</p>
  
  <p>The table is something like this (I removed some columns and the primary key etc. to reduce noise):</p>
  
  <pre><code>CREATE TABLE [tblRelation](
      [dtCreated] [datetime] NOT NULL,
      [uidNode] [uniqueidentifier] NOT NULL,
      [uidParentNode] [uniqueidentifier] NOT NULL
  )
  </code></pre>
  
  <p>My query to get the relations at a specific time is like this (assume @dt is a datetime with the desired date):</p>
  
  <pre><code>SELECT *
    FROM (
      SELECT ROW_NUMBER() OVER (PARTITION BY r.uidNode ORDER BY r.dtCreated DESC) ix, r.*
        FROM [tblRelation] r
        WHERE (r.dtCreated &lt; @dt)
      ) r
    WHERE r.ix = 1
  </code></pre>
  
  <p>This query works well. However, the performance is not yet as good as I would like. When looking at the execution plan, it basically boils down to a clustered index scan (36% of cost) and a sort (63% of cost).</p>
  
  <p>What indexes should I use to make this query faster? Or is there a better way altogether to perform this query on this table?</p>