drop a column

Hi,
What's the effect to drop a column from a table has 13 million records? I am
mainly concern the time it takes. Thanks
Hi Jen
"Jen" wrote:

> Hi,
> What's the effect to drop a column from a table has 13 million records? I am
> mainly concern the time it takes. Thanks
If you are using the ALTER TABLE statement to do this there will be a schema
modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
incompatible with all lock modes therefore no-one will have access to the
table.
You may want to check out the topics "Alter Table" and "Lock Compatibilty"
in Books Online.
John
|||It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
Compatibilty". Is there other way to drop? Thanks
"John Bell" wrote:

> Hi Jen
> "Jen" wrote:
>
> If you are using the ALTER TABLE statement to do this there will be a schema
> modify (Sch-M) lock on the table whilst this is occuring. A Sch-M lock is
> incompatible with all lock modes therefore no-one will have access to the
> table.
> You may want to check out the topics "Alter Table" and "Lock Compatibilty"
> in Books Online.
> John
|||Hi Jen
"Jen" wrote:
[vbcol=seagreen]
> It seems it doesn't mention Lock in ALTER TABLE, and can't seach for "Lock
> Compatibilty". Is there other way to drop? Thanks
> "John Bell" wrote:
For the "Lock Compatibility" subject see
http://msdn2.microsoft.com/en-us/library/aa213041(sql.80).aspx
If you used EM to drop the column it would firstly create a new table, then
suck all the data from the old table, then drop the old table and finally
rename the new table. As you can imaging you would can grow quite a bit older
whilst this is happening! ALTER TABLE is your best option and you will need
to plan for an interuption to service. Your best indicator of how long it
would take may be to try it on a test system first.
Is there an issue leaving this column in place?
John