Large table schema changes

Quick question:

Does SQL do table/schema changes "in place"?

I've got a large table (140+ million rows of very wide
data) that we want to change the schema on -- basically
to remove a number of the unused data elements that we
don't use.

Anyway, does anyone know if SQL will do an in-place
change, or if it will copy the table to a new table, thereby
increasing my space allocation needs? I'd effectively,
temporarily, need space for two tables while the change
is happening if it copies the table first. This is not good as
I do not have enough available space at the moment.

If you've got pointers to specific MS docs regarding
this issue, please let me have 'em.

Thanks in advance."Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?

It depends.

If you're adding a nullable column generally it'll do it in place.

If you're adding a non-nullable column generally it'll make a copy.

> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.

If you're dropping columns, I believe it will drop them in place.

> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.|||"Sgt. Sausage" <nobody@.nowhere.com> wrote in message
news:402d2d58$0$52159$a0465688@.nnrp.fuse.net...
> Quick question:
> Does SQL do table/schema changes "in place"?
> I've got a large table (140+ million rows of very wide
> data) that we want to change the schema on -- basically
> to remove a number of the unused data elements that we
> don't use.
> Anyway, does anyone know if SQL will do an in-place
> change, or if it will copy the table to a new table, thereby
> increasing my space allocation needs? I'd effectively,
> temporarily, need space for two tables while the change
> is happening if it copies the table first. This is not good as
> I do not have enough available space at the moment.
> If you've got pointers to specific MS docs regarding
> this issue, please let me have 'em.
> Thanks in advance.

You might want to check out this article by Kalen Delaney, which explains
ALTER TABLE in some detail:

http://www.sqlmag.com/Articles/Inde...ArticleID=40538

Simon