How long does it take to create an identity column?

Question

I have a table with 40 million records.

Which is better (faster)? Create a column directly in this table or create another table with an identifier column and insert data first?

If I create an identifier column in a table with 40 million records, is it possible to estimate how long it takes to create it?

Answer 1

If you used ALTER TABLE [...] ADD ... in the query window, this is pretty fast, in fact it would be over a long time ago. If you used the Management Studio table designer, it copies the table to the new table, discarding the old one, and then renaming the new table as the old one. This will take some time, especially if you have not previously prepared the database and the log to accommodate additional space. Since this is one single transaction, it will take another 16 hours to roll back if you stop it now.

Answer 2

It depends on it. Creating an identity column will not take much time (well, this refers to the size of the table), assuming you added it to the end of the table. If you did not do this, the server should create a new table with the identity column in the right place, export all the rows to the new table and then change the name of the table. I guess it takes so long.

Answer 3

I assume it is locked - you used the GUI or the query window (did you know that it works under?)

Try this - let us know if they give results, and you don’t know what to do:

 USE master SELECT * FROM sysprocesses WHERE blocked <> 0 SELECT * FROM sysprocesses WHERE status = 'runnable' AND spid <> @@SPID 

Answer 4

Isn't that what you need to do only once, and therefore it is not a problem how long it takes? (Assuming it doesn't take days ...)

Answer 5

Can you create a test copy of the database and create a column for this to find out how long it takes?

Answer 6

I think that a lot depends on the hardware and which DBMS you are on. In my environment, creating a new table and copying old data into it would take about 3 or 4 hours. I expect that adding an identification column will take about the same time as in other experiments. I am in Oracle with multiple servers in a SAN, so everything can work faster than in a single server environment. You can just sit back and wait.