Re: Adding Not Null with Default column to large Table

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Adam Machanic (amachanic_at_hotmail._removetoemail_.com)
Date: 09/03/04 

Date: Fri, 3 Sep 2004 09:28:20 -0400

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.
>

Relevant Pages

  • Re: Adding Not Null with Default column to large Table
    ... Not sure if you can set the constrainsts to nocheck, alter the column then ... Perhaps you could re-index the clustered index for the ... >> 2) Running multiple updates to the table ...
    (microsoft.public.sqlserver.server)
  • Re: alter column from int to bigint - timeframe
    ... Start with the clustered index ... the removal of the nonclustered indexes is instantaneous. ... I?m going to alter the CommandId column from int to bigint in the ... the CommandId field was an PK / Identity field but I?m planning ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance degradation after changing data type
    ... changes were made with 4 successive ALTER TABLE ALTER COLUMN statements ... clustered index, it might help (and be faster than creating a clustered ... I also think that using ALTER TABLE ALTER COLUMN to revert to REAL will ... So chances are you have lots of fragmentation due to the ...
    (microsoft.public.sqlserver.server)
  • Re: Row size and DBCC SHOWCONTIG
    ... I have changed the data types drastically - from a total row size of about ... Since I had to drop all dependent objects before issuing the ALTER COLUMN, ... the clustered index was dropped and afterwards recreated. ... Amy ...
    (microsoft.public.sqlserver.programming)
  • Re: Performance degradation after changing data type
    ... I've put a clustered index on here. ... >changes were made with 4 successive ALTER TABLE ALTER ... fragmentation due to the ... That will defrag ...
    (microsoft.public.sqlserver.server)