DDL on Huge Table with "text" Column

Hi,
I need to add a column to a SQL 2000 table that has a "text" column, say
Foo, and the table has gigabytes of data. I need to insert a column in the
table. The standard Enterprise Manager change script involves 1) creating a
tmp_Foo table with the new column, 2) copying the existing data from Foo to
tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
However, I know that the row data for a text field is just a pointer to the
head record for the blob data. Is there any way when I copy the data from
Foo to tmp_Foo that for my text data I could just copy the pointer and not
the actual data? That would make the change script run much faster and not
require us to open a maintenance window because the one script is going to
take half an hour to duplicate the blob data.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
Don't use the EM change script.
Use the ALTER TABLE command instead.
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Daniel Jameson" <danjam47@.newsgroup.nospam> wrote in message
news:uGz%238V$lHHA.4768@.TK2MSFTNGP05.phx.gbl...
> Hi,
> I need to add a column to a SQL 2000 table that has a "text" column, say
> Foo, and the table has gigabytes of data. I need to insert a column in
> the table. The standard Enterprise Manager change script involves 1)
> creating a tmp_Foo table with the new column, 2) copying the existing data
> from Foo to tmp_Foo, 3) dropping Foo and 4) renaming tmp_Foo to Foo.
> However, I know that the row data for a text field is just a pointer to
> the head record for the blob data. Is there any way when I copy the data
> from Foo to tmp_Foo that for my text data I could just copy the pointer
> and not the actual data? That would make the change script run much
> faster and not require us to open a maintenance window because the one
> script is going to take half an hour to duplicate the blob data.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>