Altering column in a large table

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
ManuThis 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
"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|||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" <manu@.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|||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" <manu@.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
>
>|||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
"manu" <manu@.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" <manu@.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
>>