How to set a unique identifier for each row in a column on SQL Server

Question

Is there a way to set some column of each row in the table to some unique value, for example.

update mytable set myCol=rownum() 

using SQL (not T / SQL) on SQL Server 2000. I cannot touch the schema, but I have a spare column or two.

Answer 1

Provided that you have a primary key, you can create a unique value by counting the lines. For example (replace #twith the name of your table):

update  t
set     t.col1 = (select COUNT(*) from #t t2 where t.pk >= t2.pk)
from    #t t

If you have varchar(36)or more, you can generate GUIDs that are guaranteed to be unique:

update  #t
set     col1 = NEWID()

, . , , :

declare @i int
set @i = 1

update  #t
set     col1 = @i
,       @i = @i + 1