Add Row number to a new column

问题:

I want to create a new column and populate the value of each row in that column with the row number.

This could be achieved in oracle by doing this.

alter table mytable add (myfield integer);
update mytable set myfield = rownum;

How would this be done in SYBASE ASE?

回答1:

alter table mytable 
add id bigint identity not null

That's all. There will be a column id in every row that contains a unique sequence number for that row.

回答2:

create column table some_names
     (ID bigint not null primary key generated by default as IDENTITY,
      NAME nvarchar(30));

And now we can do things like this:

insert into some_names (name) values ('Huey');
insert into some_names (name) values ('Dewey');      
insert into some_names (name) values ('Louie');   

回答3:

In my case when I tried to do it with identity column time it wasn't able to finish in reasonable time (196400 rows).

Fast and easy way was to create an INT column and fill it up using a temporary variable @id incremented after each row. This solution finished in ~4 seconds.

alter mytable add myfield INT

DECLARE @id INT 
SET @id = 0 
UPDATE mytable
SET @id = myfield = @id + 1 

You can find more info here: https://www.mssqltips.com/sqlservertip/1467/populate-a-sql-server-column-with-a-sequential-number-not-using-an-identity/