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.orgDon'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
>