-- Create a sample table with primary key
Create table test3 ( id int primary key, name char(2))
go
-- Insert some records to this table
Insert into test3 values (1,'cc')
Insert into test3 values (2,'cq')
Insert into test3 values (3,'cw')
go
-- Add a new column ( this will be set primary key in subsequent steps)
Alter table test3 add new_id bigint
go
-- Get and drop the primary key constraint (to drop already created primary key)
declare @cons_name varchar(300);
Select @cons_name = name from sysobjects where xtype = 'PK' and parent_obj in ( select id from sysobjects where xtype = 'U' and name = 'test3')
exec (' Alter table test3 Drop constraint ' + @cons_name) ;
-- Update the new column with the sequence number
go
Update test3 set test3.new_id = tt.a
From (
Select row_number() over (order by name) as a ,name from test3) tt
Where test3.name = tt.name
go
-- Set the column as not null
alter table test3 alter column new_id bigint not null
go
-- Set the new column as primary key
alter table test3 add primary key (new_id)
go
Select * from test3
-- You could set the identity key to next value using dbcc_checkindent
-- DBCC CHECKIDENT ("table name", RESEED, 300)
