Altering column in a large table

26 Nov 2007 8:28 PM

manu

Hi,

I need to alter one column which is a part of a table with billions of rows
in it. What would be the finest and fastest approach to do it?

Thanks in advance
Manu

26 Nov 2007 9:19 PM

Ben Nevarez

This will make your transaction log file to grow as a huge file, make sure
you have plenty of disk space for the entire transaction. You can manually
shrink the file and recover this disk space later.

I would use ALTER TABLE ... ALTER COLUMN.

Hope this helps,

Ben Nevarez
Senior Database Administrator
AIG SunAmerica

Show quote

"manu" wrote:

> Hi,
>
> I need to alter one column which is a part of a table with billions of rows
> in it. What would be the finest and fastest approach to do it?
>
> Thanks in advance
> Manu

26 Nov 2007 9:44 PM

TheSQLGuru

What, exactly, is the alteration? Some changes could be done more
efficiently by bulk exporting data, dropping all structures on table,
rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
Others are nothing more than a meta-data change.

--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.

Show quote

"manu" <m***@discussions.microsoft.com> wrote in message
news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@microsoft.com...
> Hi,
>
> I need to alter one column which is a part of a table with billions of
> rows
> in it. What would be the finest and fastest approach to do it?
>
> Thanks in advance
> Manu

26 Nov 2007 10:04 PM

manu

The change is just to alter the not null property of a column in this huge
table to NULL.

Thanks
Manu

Show quote

"TheSQLGuru" wrote:

> What, exactly, is the alteration? Some changes could be done more
> efficiently by bulk exporting data, dropping all structures on table,
> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
> Others are nothing more than a meta-data change.
>
> --
> Kevin G. Boles
> TheSQLGuru
> Indicium Resources, Inc.
>
>
> "manu" <m***@discussions.microsoft.com> wrote in message
> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@microsoft.com...
> > Hi,
> >
> > I need to alter one column which is a part of a table with billions of
> > rows
> > in it. What would be the finest and fastest approach to do it?
> >
> > Thanks in advance
> > Manu
>
>
>

26 Nov 2007 10:18 PM

Tibor Karaszi

I'm pretty certain it is a meta-data only change (provided you use ALTER TABLE and not the GUI
tool). I suggest you create a table with some million rows and test, just to be certain...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

Show quote

"manu" <m***@discussions.microsoft.com> wrote in message
news:3C68C5CC-0532-4035-9D56-7599FBF9C53C@microsoft.com...
> The change is just to alter the not null property of a column in this huge
> table to NULL.
>
> Thanks
> Manu
>
> "TheSQLGuru" wrote:
>
>> What, exactly, is the alteration? Some changes could be done more
>> efficiently by bulk exporting data, dropping all structures on table,
>> rebuilding table, bulk loading in the data, rebuilding indexes/keys/etc.
>> Others are nothing more than a meta-data change.
>>
>> --
>> Kevin G. Boles
>> TheSQLGuru
>> Indicium Resources, Inc.
>>
>>
>> "manu" <m***@discussions.microsoft.com> wrote in message
>> news:BA0A9735-6BF4-457F-8426-F8D5FED1722C@microsoft.com...
>> > Hi,
>> >
>> > I need to alter one column which is a part of a table with billions of
>> > rows
>> > in it. What would be the finest and fastest approach to do it?
>> >
>> > Thanks in advance
>> > Manu
>>
>>
>>