Sql – Save and get arbitrary sort order in SQL Server


My client wants to sort products by drag & drop. The drag & drop part is easy with javascript.

My problem is how do I save and get the sort order?

I'm using .net c# and SQL Server 2008.

When I move a product and drop it in a new position I get the id of the product that's moved, product in front and product behind. With this data I want to update the sort order of products.

I was thinking of adding a field with position, but then I guess I have to update every item when position changes.

Best Solution

In general adding an additional position field is the only thing you can do, to get truly arbitrary ordering.

But you can implement it in several ways. Here are two ways I've implemented myself some time ago.

1. Method: Update all position values, by looping over your items and performing an UPDATE statement for every position.

This is easy to implement, but because of the many updates, it's not good for many items and/or large tables. Especially if you do it via Ajax and perform a complete re-ordering on every change in the list.

2. Method: Do a smart update of only the affected rows.

  1. SELECT all items in the current sort order (The "old list") (Usually fast compared to an UPDATE statement)
  2. Iterate over all items from the "new list" and compare each item to the item from the old list at the same position/index. If the items are the same, don't do anything
  3. If the items are different find that item from the old list, which should actually be at that position and update its position value accordingly (Some lookup data structure might be useful here)

That way you only have to perform minimal database updates, but you'll have more complex code.

Personally I'd go with the first way, until the database updates actually become a performance problem.