title: SQL Index Identity Column Sort Order
body: <p>I have a transaction table:</p>
  
  <pre><code>|rowID|value1|value2|...|
  |11111|12    |34    |...|
  |11112|23    |123   |...|
  |11113|99    |53    |...|
  ...
  </code></pre>
  
  <p><code>RowID</code> is the Identity and increments by 1. Indexing is also no problem. Lots of new values get inserted, updates happen and sometimes some rows may get deleted.</p>
  
  <p>But now I have a second table:</p>
  
  <pre><code>|rowID|flag1|flag2|...|
  |11113|0    |1    |...|
  |11111|1    |1    |...|
  |11112|0    |1    |...|
  ...
  </code></pre>
  
  <p>It is a user operation, which inserts rows from the transaction table into this second table. <code>RowID</code> corresponds to <code>RowID</code> from the transaction table.</p>
  
  <p>The insert to the second table are not sorted by the <code>RowID</code>. A higher <code>RowID</code> may be inserted earlier, than a lower <code>RowID</code>.</p>
  
  <p>What is the best indexing strategy for such a table?</p>
  
  <ul>
  <li>Is it wise to define <code>RowID</code> in the second table as primary key, resulting in a clustered index? Which, I think, is not ideal because the <code>RowID</code>'s are not sorted.</li>
  <li>Is it better to have no primary key, but a suitable non-clustered index?</li>
  </ul>
  
  <p>I wonder if there are some general advise for such a table (second table)?</p>