Add new Column problem

[rsrinivasan]

Hi all,

I have big table of 10 million records. When i add new column, it takes more than 6hrs. How can i reduce the time to add a new column in this? Is any other way to do this?

This is my query:

alter table USAGE add
USAGE_DETAILS_ID  INT NOT NULL DEFAULT 0

Thanks in advance...

[amitpatel66]

Hi all,

I have big table of 10 million records. When i add new column, it takes more than 6hrs. How can i reduce the time to add a new column in this? Is any other way to do this?

This is my query:

alter table USAGE add
USAGE_DETAILS_ID  INT NOT NULL DEFAULT 0

Thanks in advance...

Try adding a column without NOT NULL DEFAULT statement.
Update the column to '0'. Then add the constraint NOT NULL

[ck9663]

or a

select mytable.*, space(99) as mynewcolumn into mynewtable from mytable

drop table mytable

sp_rename mynewtable, mytable...

this a long step, but might save you some time...though it will eat similar number of bytes on your disk

- CK

[rsrinivasan]

or a

select mytable.*, space(99) as mynewcolumn into mynewtable from mytable

drop table mytable

sp_rename mynewtable, mytable...

this a long step, but might save you some time...though it will eat similar number of bytes on your disk

- CK

Ok fine. But i need to create mynewcolumn of integer type. How can i do it?

Thanks,

[ck9663]

Ok fine. But i need to create mynewcolumn of integer type. How can i do it?

Thanks,

try:

select mytable.*, cast(000.0000 as int) as mynewcolumn into mynewtable from mytable

drop table mytable

sp_rename mynewtable, mytable...

-- CK

[rsrinivasan]

try:

select mytable.*, cast(000.0000 as int) as mynewcolumn into mynewtable from mytable

drop table mytable

sp_rename mynewtable, mytable...

-- CK

ok thanks a lot. It is working...