It's a common enough problem : you have a table with a sequence number, or sort number, or similar.  It's just a number that orders a set of records for you.

But then you have to delete one of the middle records, or insert one, or something changes. Your sequence number is no longer in sequence.  What to do?

I've seen some truly bizarre implementations of code for this, but there is a simple, effective and fast way of achieving this seemingly simple task.

To explain, imagine a table like this:

CREATE TABLE MySortedTable (MyValue varchar(5), SeqNum int)

To assign an incrementing sequence number to the SeqNum column, all you need to do is this :

/* T-sql code*/ 
declare @seq int
set @seq = 0
UPDATE MySortedTable
SET @seq = SortValue = @seq + 1

 

That's it! Sql Server will update each value in the table, and increment the variable @seq with each row.