question

create table #temp(id int) create table #temp1(id int, value varchar(100)) insert into #temp(id) select ROW_NUMBER( ) over (order by column_id) from sys.all_columns insert into #temp1(value) select name from sys.all_columns select count(1) from #temp --5190 select * from #temp1 --5190 UPDATE t1 SET t1.id = t.id FROM ( SELECT row_number() OVER ( ORDER BY id ) AS rno ,id FROM #temp1 ) AS t1 ,#temp t WHERE t1.rno = t.id select id,value from #temp1 drop table #temp drop table #temp1

If you create a NULL-able int column to the table, you can set consecutive numbers to the ID column in batches. That way, you won't need as much log space. Creating the column is just a metadata operation and requires hardly any log at all. Updating the id-column requires as much log as the rowsize * number of rows per batch. Once the whole table is updated with consecutive numbers, you can alter the column to be not NULL-able (also requires no log, but does require scanning through the table). Then you create a new table, say B, with the same columns as A, but with id as an identity column, starting at whatever is your max ID value from table A + 1. Then switch table A over to table B, drop table A and rename table B. Sample code below. Backup you database prior to doing this. It's pretty easy to make a typo... And don't run it all at once, check that each step does what you expect it to Before moving on the the next step. --SETUP demo code CREATE TABLE A(name varchar(50)); GO INSERT A(name) VALUES ('John Doe' + CAST(newid() as varchar(100))), ('Jane Doe' + CAST(newid() as varchar(100))); GO 100000 CREATE INDEX ix_Name ON A(name); GO --Done setting up demo --Add nullable int column to A ALTER TABLE A ADD id int NULL; GO --Variables for batch-handling DECLARE @rowcount int=1000; DECLARE @offset int=0; DECLARE @batchsize int=1000; --Update in batches of 1000, set consecutive numbers to id, ordered by name WHILE @rowcount>0 BEGIN WITH CTE AS ( SELECT TOP(@batchsize) name, id, new_id = ROW_NUMBER() OVER(Order BY name) + @offset FROM A WHERE a.id is null ORDER BY A.name )UPDATE CTE SET id=new_id; SET @rowcount = @@ROWCOUNT; PRINT @rowcount; SET @offset = @offset + @batchsize END GO --Prepare Creation of table B. DECLARE @maxid int; SELECT @maxid=MAX(id) FROM A; DECLARE @sql nvarchar(max)=' CREATE TABLE B(name varchar(50),id int identity(' + cast(@maxid+1 as varchar(10)) + ',1) NOT NULL); ' --Create table B EXEC sp_executesql @sql; --Add same indexes to B as A CREATE INDEX ix_name ON B(name); --Set id as not NULL-able in A (because that is how it is in B) ALTER TABLE A ALTER COLUMN id INT NOT NULL; --Switch A to B ALTER TABLE A SWITCH TO B; --Drop old table A DROP TABLE A; --Rename new table B to A EXEC sp_rename N'B',N'A','OBJECT'; --Voila select * from A ORDER BY name;