What is the fastest way to update existing records with a sequence?

Question

I've got a data set with a primary key, a foreign key and an empty sequence value. What is the fastest way of adding a sequence number to the data based on the foreign key?

QuestionConditionId is my primary key, QuestionId is foreign key. This is what the data looks like after selecting with an order by on QuestionId:

After the update my data should look like this:

I could write a loop for this, but I'm hoping for something smaller and more efficient. Any ideas?

Answer 1

WITH T
     AS (SELECT *,
                ROW_NUMBER() OVER (PARTITION BY QuestionId 
                                       ORDER BY QuestionConditionId ) AS RN
         FROM   YourTable)
UPDATE T
SET    Sequence = RN 

But keeping this Sequence column synchronised after inserts, updates, deletes may be more hassle than it is worth. You can also use ROW_NUMBER to calculate at SELECT time rather than storing it.