SQL Server Performance Forum – Threads Archive

Create Auto Increment

Hi I found this site and was hoping somebody could help me out. I’ve got a large table (about 500,000+ rows), and I need to set the primary key to autoincrement, the problem is when I attempt this in the management studio it time-outs. So my next attempt was to create a new table with the same columns but set the PK to autoincrement, and then do a insert into ‘blah’ select * from ‘original blah’. This worked fairly well, but still took 20+ minutes to copy the data into the new table. Has anybody got any ideas on how to reduce the time it takes to copy data between the two tables? Or any other ideas for that matter, I’ve tried to look into bulk inserts, but I’m not sure if that will help. Any help would be greatly appreciated, thanks!

---

– make sure you have enough free space in the database before you populate the new table. If the database has to grow, it’ll take longer.
– create the new table with no indexes on it. Create the indexes afterwards Are you adding a new column with the identity property? i.e. a new PK?

---

Make sure no one is inserting the data into the old table while populating the new table
OR
Copy new rows from old table to the new table after populating the new table…
Otherwise you may loose/miss some data… Use SELECT INTO option OR use DTS with fast load enabled to load the data into new table and make sure indexes on the new table… as Thomas mentioned create the indexes after populating the data…
MohammedU.
Moderator
SQL-Server-Performance.com

---

If you can schedule this dtS task as a job then it might have better performance than running from query analyzer, at the same time its better to run during less traffic hours on database for performance aspect (if it is an ongoing process). Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. The greatest discovery of my generation is that a human being can alter his life by altering his attitudes of mind.

---

Thanks for everyone’s replies.

---

The current PK doesn’t autoincrement, it finds the PK id of the last record and +1 then inserts a new record. I need to change the PK so it uses identity, but with making sure that it doesn’t affect the 500,000+ records that already exist.

It took 22 minutes to populate the new table with no indexes, I need it to populate faster.

I will also have to make sure nobody can update exisiting rows if I use a new table! So if my only choice is to add the identity column by creating a new table I will have to take the site offline, which I was ideally hoping to avoid.

The 22 minutes I mentioned was with a scheduled job at the least busy time of my website, I want to limit the time the site is down if I have no other choice. I can create a new column on the existing table with identity set, is there a way to update the field with it’s PK ID value using something similar to identity insert? Although I feel this will take a very long time to execute. I really appreciate the help guys.

---

You can do what you want by using IDENTITY_INSERT For example (i have left the primary key definition out to make the code a bit easier to read.)
Create oldtable
(OldPrimaryKey int, — this is your current manually incrementing column
OtherColumn1 char(1),
OtherColumn2 char(1),
OtherColumn3 char(1))
go
Create newtable
(NewPrimaryKey int identity (1, 1),
OtherColumn1 char(1),
OtherColumn2 char(1),
OtherColumn3 char(1)) go
Set identity_insert newtable on
insert into newtable (newprimarykey, OtherColumn1, OtherColumn2, OtherColumn3)
Select OldPrimaryKey, OtherColumn1, OtherColumn2, OtherColumn3
from oldtable set identity_insert newtable off
go
sp_rename oldtable, oldtable_save
go
sp_rename newtable, oldtable
go

---

I did do this. My request was about speeding up this process. THanks anyway. I have come to the conclusion that I have to just live with it taking this long. I appreciate everyone’s help.

---

You could speed-up the query using nolock hint, but you would have to indentify updated/deleted rows later and update new table.

---

]]>