Reset or Update Row Position Integer in Database Table

Question

Hello,

I am working on a stored procedure in SQL Server 2008 for resetting an integer column in a database table. This integer column stores or persists the display order of the item rows. Users are able to drag and drop items in a particular sort order and we persist that order in the database table using this "Order Rank Integer".

Display queries for items always append a "ORDER BY OrderRankInt" when retrieving data so the user sees the items in the order they previously specified.

The problem is that this integer column collects a lot of duplicate values after the table items are re-ordered a bit. Hence...

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 3
d    | 4
e    | 5
f    | 6

After a lot of reordering by the user becomes....

Table
--------
Name | OrderRankInt
a    | 1
b    | 2
c    | 2
d    | 2
e    | 2
f    | 6

These duplicates are primarily because of insertions and user specified order numbers. We're not trying to prevent duplicate order ranks, but we'd like a way to 'Fix' the table on item inserts/modifies.

Is there a way I can reset the OrderRankInt column with a single UPDATE Query? Or do I need to use a cursor? What would the syntax for that cursor look like?

Thanks, Kervin

EDIT

Update with Remus Rusanu solution. Thanks!!

CREATE PROCEDURE EPC_FixTableOrder
@sectionId int = 0
AS
BEGIN

-- "Common Table Expression" to append a 'Row Number' to the table
WITH tempTable AS 
(
 SELECT OrderRankInt, ROW_NUMBER() OVER (ORDER BY OrderRankInt) AS rn
 FROM dbo.[Table]
 WHERE sectionId = @sectionId -- Fix for a specified section
)
UPDATE tempTable  
SET OrderRankInt = rn; -- Set the Order number to the row number via CTE

END
GO

Answer 1

Fake it. Make the column nullable, set the values to NULL, alter it to be an autonumber, and then turn off autonumber and nullable.

(You could skip the nullable steps.)

Answer 2

with cte as (
 select OrderId, row_number() over (order by Name) as rn
 from Table)
update cte
 set OrderId = rn;

This doesn't account for any foreign key relationships, I hope you are taken care of those.