Using TSQL Ranking Function on a sort column
Where there is a sort column containing numbers from 1..n representing the order in which a list of items in the database should be displayed, you have problems managing the insert and delete of records. Insert needs the records to be moved up that are above the sort order of the item getting inserted. Delete will leave a hole in the sort order.
Here you may see how the TSQL Ranking and Partitioning functions helps out.
WITH TempNewsTable (NewSort,OriginalSort) AS SELECT ROW_NUMBER() OVER (ORDER BY SortOrder DESC),SortOrder FROM dbo.NewsSummaries) UPDATE TempNewsTable SET OriginalSort=NewSort
Here the sort column is regenerated based on the existing sort order, any gaps will be filled in.
If we want to insert a record then a tweak will leave us a gap,
WITH TempNewsTable (NewSort,OriginalSort) AS SELECT ROW_NUMBER() OVER (ORDER BY SortOrder DESC),SortOrder FROM dbo.NewsSummaries) UPDATE TempNewsTable SET OriginalSort=NewSort+1 WHERE SortOrder >= @InsertedItemSortOrder
Where @InsertedItemSortOrder is the sort order of the item we are inserting. A whole is left for the new row to be inserted.
Update is similar again, make a hole for new item and renumber to close up the hole we leave by moving the sort order of the item we are editing.