ansaurus

Question

How long does it take to create an identity column?

Answer 1

A:

Isn't it something you'll only have to do once, and therefore isn't really a problem how long it takes? (Assuming it doesn't take days...)

nickf 2009-08-20 12:39:09

I need know, because I created the identity column since 16 hours ago... and not finished yet...

Zanoni 2009-08-20 12:42:17

@Zanoni: Sound like a good time to take a day off.

Developer Art 2009-08-20 12:43:15

@New in town: or not...

Zanoni 2009-08-20 13:10:46

Answer 2

A:

Can you not create a test copy of the database and create the column on that to see how long it takes?

Mark Redman 2009-08-20 12:40:57

it's already running. he wants to know if it will finish, or if something is wrong.

geowa4 2009-08-20 12:45:09

I answered before we all knew that, so yes, we know that now.

Mark Redman 2009-08-20 12:49:47

Its a difficult one, One could possibly cancel/stop the script and try on a test database? Or test this again in parallel to see if it works quicker on the test indicating something went wrong? Is the database stil operating for the application?

Mark Redman 2009-08-20 12:52:47

I'm doing in a test database. No, the database queries are very slow.

Zanoni 2009-08-20 13:09:46

Answer 3

+2 A:

This kind of depends. Creating an identity column won't take that long (well ok this is relative to the size of the table), assuming you appended it to the end of the table. If you didn't, the server has to create a new table with the identity column at the desired position, export all the rows to the new table, and then change the table name. I am guessing that is what is taking so long.

Kevin 2009-08-20 12:45:52

makes a lot of sense why in my experience the amount of time is about the same. Thanks for the explanation.

sql_mommy 2009-08-20 12:52:12

Answer 4

A:

I think a lot depends upon the hardware and which DBMS you are in. In my environment, creating a new table and copying the old data into it would take about 3 or 4 hours. I would expect the addition of an identity column to take around the same amount of time, just based on other experiences. I'm on Oracle with multiple servers on a SAN, so things can run faster than in a single server environment. You may just have to sit back and wait.

sql_mommy 2009-08-20 12:50:40

It's a test server using Microsoft SQL Server 2005. Processor Intel Core 2 DUO 2.20GHz with 4GB RAM.

Zanoni 2009-08-20 13:19:53

Answer 5

+1 A:

I'm guessing it's blocked - did you use the GUI or a query window (do you know the SPID it's running under?)

Try these - let us know if they give results and you're not sure what to do:

USE master

SELECT * FROM sysprocesses WHERE blocked <> 0

SELECT * FROM sysprocesses WHERE status = 'runnable' AND spid <> @@SPID

Mike DeFehr 2009-08-20 14:34:36

Both queries returned nothing.

Zanoni 2009-08-20 18:04:58

Answer 6

A:

If you used ALTER TABLE [...] ADD ... in a query window, it is pretty fast, in fact it would have finished long ago. If you used the Management Studio table designer it is copying the table into a new table, dropping the old one, then renaming the new table as the old one. It will take a while, specially if you did not pre-grow the database and the log to accommodate the extra space needed. Because is all one single transaction, it would take about another 16 hours to rollback if you stop it now.

Remus Rusanu 2009-08-20 15:00:18

I've used ALTER TABLE ... ADD ...

Zanoni 2009-08-20 18:05:58

Is the querry blocked or macking progress? that does seem way too long. check wait_time and wait_resource is sys.dm_exec_requests

Remus Rusanu 2009-08-20 18:31:11

@Remus Rusanu => The query still running, but I don't know where's the problem. >>>wait_time = 0, wait_resource = 31:1:1219931, status = SUSPENDED, command = ALTER TABLE,wait_type = PAGEIOLATCH_EXtotal_elapsed_time = 86053703

Zanoni 2009-08-20 20:15:07

So is just waiting for IO to fetch in pages to modify them. Looks like a realy slow disk system. See how long is the average disk queue length on the physical disks hosting the mdf(s) and ldf, the disk throughput, try to avoid any unnecessary I/O operations on those drives until the ALTER finishes. Does the sys.dm_exec_requests show the percent complete so you get an ETA till finish?

Remus Rusanu 2009-08-20 21:23:32

btw, did you also add a constraint when adding the column, like PRIMARY KEY?

Remus Rusanu 2009-08-21 01:26:14

@Remus Rusanu: No, the sys.dm_exec_requests command doesn't show percent_complete column. The value is 0 until now. No, no constraint (primary key) was added. The complete command is: ALTER TABLE TABLE_TESTADD UNIQUEID INT NOT NULL IDENTITY (1, 1)

Zanoni 2009-08-21 12:30:51

ansaurus

tags:

views:

answers:

How long does it take to create an identity column?

related questions