title: Using a sort order column in a database table
body: <p>Let's say I have a <code>Product</code> table in a shopping site's database to keep description, price, etc of store's products. What is the most efficient way to make my client able to re-order these products?</p>
  
  <p>I create an <code>Order</code> column (integer) to use for sorting records but that gives me some headaches regarding performance due to the primitive methods I use to change the order of every record after the one I actually need to change. An example:</p>
  
  <pre><code>Id    Order
  5     3
  8     1
  26    2
  32    5
  120   4
  </code></pre>
  
  <p>Now what can I do to change the order of the record with <code>ID=26</code> to 3?</p>
  
  <p>What I did was creating a procedure which checks whether there is a record in the target order (3) and updates the order of the row (ID=26) if not. If there is a record in target order the procedure executes itself sending that row's ID with <code>target order + 1</code> as parameters.</p>
  
  <p>That causes to update every single record after the one I want to change to make room:</p>
  
  <pre><code>Id    Order
  5     4
  8     1
  26    3
  32    6
  120   5
  </code></pre>
  
  <p>So what would a smarter person do?</p>
  
  <ul>
  <li>I use SQL Server 2008 R2.</li>
  </ul>
  
  <p><strong>Edit:</strong></p>
  
  <p>I need the order column of an item to be enough for sorting with no secondary keys involved. Order column alone must specify a unique place for its record.</p>
  
  <p>In addition to all, I wonder if I can implement something like of a linked list: A 'Next' column instead of an 'Order' column to keep the next items ID. But I have no idea how to write the query that retrieves the records with correct order. If anyone has an idea about this approach as well, please share.</p>