I have about 800,000 rows in a table and I need to add an identity column and it is taking over an hour. I end up cancelling the query because I'm not sure if something is wrong. ALTER TABLE PQT.DBO.TBLINFO ADD ID [int] IDENTITY(1,1) NOT NULL How long should this take? I also want to make this a primary key, but not sure if I should do that after I add the column or at the same time? Please advise. Thank in advance!
This could take a while. Alternatively this could take no time at all. On my very slow, extremely disk-bound desktop machine, it took 1.5 minutes to add this column to a 800,000 row table (albeit a very simple table with one integer column). You could be experiencing blocking - attempting to add a column will require an exclusive lock - is the system in use at the same time? Try running sp_who2, or the Activity Monitor in SSMS to see what your alter query is waiting on.
Thanks. So I did that last night when there where no other connections to the database. Just me when I looked at the activity monitor. This is our production server, so it concerns me. What can I look at to find the issue. Should I look into the tempDB first?