Adding Not Null with Default column to large Table

I am running an upgrade to an existing system which I have found is using th
e
Alter Table statement to add a Not Null with Default column to huge table
(100 million records). Not surprisingly it is taking rather a long time!
Can anyone give me any other options to perform this operation ?
I'm currently thinking about
1) Adding the column Nullable
2) Running multiple updates to the table
3) Altering the column to make it Not-Nullable.I don't think there's any way to get a performance boost, if that's what
you're after... Perhaps you could re-index the clustered index for the table
first with a large fillfactor. The ALTER may be going slowly because your
index pages are very full and adding the new column is forcing a lot of page
splits. But re-indexing the table may take just as long.
"Neil K" <Neil K@.discussions.microsoft.com> wrote in message
news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> I am running an upgrade to an existing system which I have found is using
the
> Alter Table statement to add a Not Null with Default column to huge table
> (100 million records). Not surprisingly it is taking rather a long time!
> Can anyone give me any other options to perform this operation ?
> I'm currently thinking about
> 1) Adding the column Nullable
> 2) Running multiple updates to the table
> 3) Altering the column to make it Not-Nullable.
>|||Not sure if you can set the constrainsts to nocheck, alter the column then
set the contraints to check
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:O2fPinbkEHA.1048@.tk2msftngp13.phx.gbl...
>I don't think there's any way to get a performance boost, if that's what
> you're after... Perhaps you could re-index the clustered index for the
> table
> first with a large fillfactor. The ALTER may be going slowly because your
> index pages are very full and adding the new column is forcing a lot of
> page
> splits. But re-indexing the table may take just as long.
>
> "Neil K" <Neil K@.discussions.microsoft.com> wrote in message
> news:6037A939-4461-427E-8869-69B3171FA96F@.microsoft.com...
> the
>|||"Gene Black" <geblack@.hotmail.com> wrote in message
news:uN%23TUvdkEHA.1348@.TK2MSFTNGP15.phx.gbl...
> Not sure if you can set the constrainsts to nocheck, alter the column then
> set the contraints to check
Not a bad idea, but apparently constraints aren't checked when you do an
ALTER:
create table #a (id int)
insert #a values (2)
alter table #a with nocheck add constraint b check (id = 1)
alter table #a add b varchar(20) not null default('abc')|||Interesting suggestions ... thanks guys.
If I attempt my original plan of
1) Add column Nullable.
2) Run Batch updates for new column to Non-Null Value
3) Alter column to make non-null with default
Will the last step still take a long time to run , even though no data is
being added or amended, or will the Add Constraint Check be ignored ?|||"Neil K" <NeilK@.discussions.microsoft.com> wrote in message
news:3B4AF7FB-6138-4144-8B80-B014FCC18633@.microsoft.com...
> If I attempt my original plan of
> 1) Add column Nullable.
> 2) Run Batch updates for new column to Non-Null Value
> 3) Alter column to make non-null with default
> Will the last step still take a long time to run , even though no data is
> being added or amended, or will the Add Constraint Check be ignored ?
It should only take as long as a full table scan.
To simulate it, you could try:
SELECT COUNT(*)
FROM YourTable
WHERE SomeNonIndexedCol = SomeNonIndexedCol --Same column name
This will force a full table scan. You can add a NOLOCK hint or run it
with a READ UNCOMMITTED isolation level to make sure it doesn't interfere
with other processes.