ALTER TABLE taking a long time ?

Does anyone know why ALTER TABLE seems to take so long. For instance
consider a small example table called TestTable. Adding a new field does

ALTER TABLE dbo.TestTable ADD
TestID int NOT NULL CONSTRAINT DF_TestTable_TestID DEFAULT -1
COMMIT

which surely should be fairly quick. If I had the same table and one field allows NULL's on it, and I wanted
to take off the NULL constraint then it would do the following
CREATE a tmp table
Then Insert into it everything from the original table.
I know it does this as I was profiling at the time.

I would like to know how it takes SQL Server 2000 over twice as long to do the ALTER TABLE than to remove the NULLS
as surely they are very similar things (i'm guessing that ALTER TABLE does a create table and insert into behind the scenes, so how does the addition of the new field, which
is probably done during the select into, cause it to take twice as long.) The time taken also increases dramtically when the table contains any indexes.
I tested this on a table of 40 million rows.

Any help would be much appreciated on this query.

Regards,
Oneilldo

Sorry I forgot to say. Nobody else was connected to the DB at that time, no other Jobs were running on the DB (I was using profiler whilst I was making the change). I made the change using Enterprise Manager and added a new field that way. Anyone else know why it takes SQL Server 2000 so long to add a field compared to removing NULL's?

Don't use EM to do DBA work... You can't control what it does behind the scenes. Remember that EM has to cater for every eventuality, so it will create a new table, drop all fks, constraints and indexes on the original table, rename the old table and then rename the new table, recreate all fks, constraints and indexes.

Use Query Analyser instead... With the default=-1, remember that it will have to update every row whereas a nullable column does not

Cheers
Twan

Thanks for your help.
However taking the nulls off in EM also created a new table, inserted everything into it and then renamed them. I still dont understand how this took twice as long as just removing the nulls, I know that it would have to allocate more space for the new field, but it was only an int field, how can this take twice as long as removing the nulls off an existing field?

I also forgot to mention that this table did not have any indexes on (so it cant be that) and it also had no other constraints specified.
I don't use EM normally I was just generally wondering whilst adding a new field takes twice as long as removing a NULL constraint from an existing field as both operations seem very similar.
ANy more help would be much appreciated

Just to echo an earlier reply. Don't use Enterprise Manager for most DBA work, especially DDL. EM can be dumb as a box of rocks and frequently picks the least efficient way to do the kinds of operations you described. As to why EM, might do one thing a little faster than another, you would have to monitor what it's actually doing in the background. Based on my experience, there is no situation in which EM does a given task faster than an equivalent SQL command. The other plus to using SQL commands is that they can saved for audit trails, repeatability, etc.

I know EM is not very good, however I was using profiler at the same time so it would
show exactly what was running.
I suppose the real question is what is the difference between using ALTER TABLE to add a new column and using CREATE TABLE to create a table with the new column (and the existing old columns) and then inserting everything from the old table into this new table (with the new field on it) using INSERT INTO.

So why does ALTER TABLE take longer than the other method even if the table has no indexes on it and no other constraints?

i.e.

ALTER TABLE dbo.TestTable ADD
TestID int NOT NULL CONSTRAINT DF_TestTable_TestID DEFAULT -1 COMMIT

or

CREATE TABLE dbo.Tmp_TestTable
( other field names.....
TestID int NOT NULL
) ON [PRIMARY]

IF EXISTS(SELECT * FROM dbo.TestTable)
EXEC('INSERT INTO dbo.Tmp_TestTable (Other field names...,TestID )
SELECT Other field names..., TestID FROM dbo.TestTable TABLOCKX')

The tables are on the same file group
The 1st example takes twice as long as the second!

The first statement will require SQL to go through every row, try to extend it. It will have to move the row if it can't fit in the same slot (due to another row being in the slot next to it)

The second statement will be set based, which may well be much faster if there are no indexes, constraints, etc. to put back

Cheers
Twan

I'm sorry Twan, I dont understand what you mean by slots. Do you mean SQL Server 2000 will try to extend each row on the data page and if it cant it will cause a page split that will slow it down, whereas the other way is set based (does this mean that it will insert multiple columns at once and hence the read and write operations will be faster cos it wont keep stop-starting). and because its set based its faster as it will be inserting multiple rows into the new table at once. If the second version is faster, and it can yeild the same end result then why does EM use alter table, when it could theretically do it using create table and then insert into, which as we have seen is a lot faster.

Thanks for all the help

Hi ya,

yeah I mean that in the alter table case SQL will ahve to move rows and causes fragmentation in the table, this will be slow and result in a larger table size. The create table, insert is a fairly simple operation and fairly fast.

I'm not sure why EM uses alter table, I don't think it used to... perhaps it has been 'improved' for cases where there is only a new column added to the end of the table?

Cheers
Twan