Save and get arbitrary sort order in SQL Server

Question

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.

Answer 1

We have a sort column but yes we have to re-index all rows as things change. You could mitigate this by assigning sort's in large enough increments to allow some level of movability before you have to do this, such as in 10's or 100's but that's not the best solution and I'd be interested to see what other ideas people have.

Answer 2

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.

Answer 3

If you can capture each move programatically (with up and down buttons for example) then you can just swap the position numbers of the row moving and the row being moved. Make sure that you add new rows at the max position + 1.