Best way to add a column to a large table

hi - i want add not null column existing table, table looks this:

 

create table [data].[factdata](
 [timeid] [smallint] not null,
 [dateid] [int] not null,
 [actualtimestamp] [datetime] not null,
 [objectid] [int] not null,
 [datavalue] [float] null,
 [headerid] [int] not null,
 [threshold] [float] null,
 [daylightoffset] [tinyint] not null,
 [missingdata] [tinyint] not null,
 constraint [pk_factdata] primary key clustered
(
 [headerid] asc,
 [objectid] asc,
 [dateid] asc,
 [timeid] asc
)with (pad_index  = off, statistics_norecompute  = off, ignore_dup_key = off, allow_row_locks  = on, allow_page_locks  = on) on [factdataps]([dateid])
) on [factdataps]([dateid])

and want run:

 

alter table data.factdata

add plannedoutage tinyint not null

constraint addploutdflt default 0

 

to add additional column......

 

the thing there gazillion rows in table - 1,100,000,000 rows anyway (which close gazzilion)

 

the statement takes hours & hours & log gets huge & fails on log error, fix problem & have start on (sigh).

 

is there better way without making log huge?? (simple recovery @ mo')

 

i tempted create new table new definition & copy data accross in batches & drop original table & rename new 1 wondered if there better way???

 

its on dev system don't have worry dropping db single user or locking db hours on end must keep data!

 

i can't think missing - can advise best way this??

 

thanks!

try this:

 

1. rename tablex oldtablex

2.

code snippet

select *, newcolumn=convert(tinyint,0) tablex oldtablex

 

 

3. apply indexes, constraints, defaults, etc.

 

 

let know if works you.

 

 

SQL Server  >  SQL Server Database Engine