title: SQL Server custom record sort in table, allowing to delete records
body: <p>SQL Server table with custom sort has columns: <strong>ID</strong> (PK, auto-increment), <strong>OrderNumber</strong>, <strong>Col1</strong>, <strong>Col2</strong>..</p>
  
  <p>By default an insert trigger copies value from ID to OrderNumber as suggested <a href="https://stackoverflow.com/a/848182/475861">here</a>.
  Using some visual interface, user can sort records by incrementing or decrementing OrderNumber values.</p>
  
  <p>However, how to deal with records being deleted in the meantime? </p>
  
  <p><strong>Example:</strong>
  Say you add records with PK ID: 1,2,3,4,5 - OrderNumber receives same values. Then you delete records with ID=4,ID=5. Next record will have ID=6 and OrderNumber will receive the same value. <strong>Having a span of 2 missing OrderNumbers would force user to decrement record with ID=6 like 3 times to change it's order</strong> (i.e. 3x button pressed).</p>
  
  <p>Alternatively, one could insert <code>select count(*) from table</code> into OrderNumber, but it would allow to have several similar values in table, when some old rows are deleted.</p>
  
  <p>If one doesn't delete records, but only "deactivate" them, they're still included in sort order, just invisible for user. At the moment, solution in Java is needed, but I think the issue is language-independent.</p>
  
  <p>Is there a better approach at this?</p>