How to populate a column with a count of numbers?

Question

I am converting to an integer primary key and am having trouble seeding the new column data with a count of integer numbers.

Given an existing table:

create table t1 (
  Id uniqueidentifier, 
  NewId int,
  Data nvarchar(100)
)

How would I update existing rows with a count of numbers from 1 to the # of rows in the result set?

So:

|id      |NewId       |Data
-------------------------------
|ABC     |null        |first
|DEF     |null        |second
|GHI     |null        |third

Would become:

|id      |NewId    |Data
----------------------------
|ABC     |1        |first
|DEF     |2        |second
|GHI     |3        |third

This is for a migration to using a hilo primary key with nhibernate, which is needed to reduce database round trips with between my application and database tiers, so IDENTITY is not an option for me.

Answer 1

Rather than trying to do this yourself, why not just add the column as identity.

ALTER TABLE YourTable
    ADD NewId INT IDENTITY(1,1)

This will take care of it for you.

Answer 2

You could use a windowing function like row_number:

update t
set NewId = sub.rn
from YourTable t
join (
    select
        id
    ,   row_number() over (order by id) as rn
    from YourTable
) sub on sub.id = t.id

Answer 3

not sure how efficient it is, but this works - see the update command near the bottom

create table t1 (Id uniqueidentifier, 
                 NewId int,
                 Data nvarchar(100))
create index idx_t1 on t1(data)

insert into t1 values (newid(), null, 'B')
insert into t1 values (newid(), null, 'A')
insert into t1 values (newid(), null, 'F')
insert into t1 values (newid(), null, 'C')
insert into t1 values (newid(), null, 'E')

update t1
   set [newid]=(select count(*) from t1 as temp where temp.data <= t12.data)
from t1 as t12

select * from t1

Answer 4

DECLARE @Id INT
DECLARE @CurrentRow INT
SET @CurrentRow = 1

DECLARE SetIDCursor INSENSITIVE CURSOR FOR  
    SELECT  Id
    FROM t1
    ORDER BY Id
    FOR READ ONLY

OPEN SetIDCursor

WHILE (0=0)
   BEGIN
    FETCH NEXT FROM SetIDCursor
    INTO    @Id

    IF (@@FETCH_STATUS <> 0) BREAK

    UPDATE t1
    SET NewID = @CurrentRow
    WHERE Id = @Id

    SET @CurrentRow = @CurrentRow + 1
   END

CLOSE SetIDCursor
DEALLOCATE SetIDCursor

Answer 5

Use RowNumber(), it is great for things like this:

;WITH SequencedData AS
(
  SELECT Id, ROW_NUMBER() OVER (ORDER BY Id) Sequence 
  FROM [YourTable]
)
UPDATE t
SET NewId = sd.Sequence
FROM [YourTable] t
JOIN SequencedData sd
  ON sd.Id = t.Id