Post reply

Fastest Way To Add Not Null Columns With Default

  • August 27, 2007 at 9:40 am

    #125436

    Question for the group, I need to add two not null columns to a very large Fact table running in SQL Server 2000. I’ve tried a number of different approaches including the default method:

     

    ALTER TABLE FCT_MACHINE_EVNT_DTL ADD [WAS_TERMINAL_SESSION50] [bit] DEFAULT 0 NOT NULL

     

    and the separate update approach:

     

    1. ALTER TABLE FCT_MACHINE_EVNT_DTL  ADD [WAS_TERMINAL_SESSION50] [bit] DEFAULT 0 NULL

     

    2. update FCT_MACHINE_EVNT_DTL set WAS_TERMINAL_SESSION50 = 0

     

    3. ALTER TABLE FCT_MACHINE_EVNT_DTL ALTER COLUMN WAS_TERMINAL_SESSION50 [bit] NOT NULL

     

    All take hours to process.

     

    Has anyone had luck with either approach or using a different method?

     

    If I could add a “with nocheck” to step three above that would help.

     

    Thanks in advance

  • August 27, 2007 at 9:49 am

    #729580

    ALTER TABLE FCT_MACHINE_EVNT_DTL ADD [WAS_TERMINAL_SESSION50] [bit] DEFAULT 0 NOT NULL WITH VALUES

    --- Only one pass


    * Noel

  • August 27, 2007 at 10:00 am

    #729588

    You state you are adding two columns, even thou these columns may b small adding columns will take a bit on VLDB as the engine has to add to each record the neccessary space whihc may also cause data page movement an page splits.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply