This is a good basic question, thank you. One thing I would like to point out is that the update script in question is more complex than it needs to be and it uses what Jeff Moden calls a triangular join, which is one of the most evil things one can do to kill performance of the T-SQL query. Of course back in the dark ages when we had to work with SQL Server 2000, the triangular joins were a necessary evil sometimes, but in nowadays when the end of support for SQL Server 2000 is long as ended and the end of support for SQL Server 2005 is looming (12 of April 2011), it is about time to stop the triangular joins insanity and take a look at the windowing functions instead. For example, the update in question can be easily restated like this:
;with records (RecID, Seq) as
RecID, row_number() over(partition by Value order by RecID)
Seq = records.Seq
from #Test inner join records
on #Test.RecID = records.RecID;
Please, please read Jeff's article: http://www.sqlservercentral.com/articles/T-SQL/61539/[/url]
It is a true eye opener!
Just my 2 cents.