title: SQL Primary key sort order
body: <p>I am using MS SQL server 2005</p>
  
  <p>I have a table with 3 columns where I store user-message mapping like:</p>
  
  <pre><code>msg_for msg_from msg_id 
  bob     bob      1 
  bob     john     1 
  bob     steve    1 
  bob     bob      2 
  bob     john     2 
  bob     bob      3 
  bob     john     3 
  bob     steve    3
  </code></pre>
  
  <p>The PK is on 3 columns and msg_id is FK to messages table that stores the messages</p>
  
  <p>The above is the physical storage I see according to the PK on 3 columns</p>
  
  <p>Now my query MUST return the messages for a given user having latest msg at top (order by msg_id DESC)</p>
  
  <pre><code>bob john  3
  bob steve 3
  bob john  2
  bob steve 2
  bob john  1
  bob steve 1
  </code></pre>
  
  <p>This mapping table has millions of rows. I see 95% of the cost is to SORT the result.</p>
  
  <p>Is it possible to have the PK or some other way store data physically like this (avoid SORT)?</p>
  
  <pre><code>msg_for msg_from msg_id
  bob     bob      3
  bob     john     3
  bob     steve    3
  bob     bob      2
  bob     john     2
  bob     bob      1
  bob     john     1
  bob     steve    1
  </code></pre>
  
  <p>Thanks</p>