Post reply

Changing Null to Not Null

  • June 4, 2003 at 7:00 pm

    #80961

    I have a database consisting of several filegroups. One of these filegroups is just over 4 Gb. and consists of a single table together with its many indexes.

    This table has many columns, only one of which (by accident) allows Nulls (it's Tinyint). In actual fact, not a single row in this table contains Null in that column (it's always had a Default value of 0).

    Having seen that Null was allowed, I changed it to Not Null (using Enterprise Manager).

    From what I've told you, it's clear that no database values need to be changed anywhere. Yet this operation takes more than an hour to complete, increases the log file from 500k to 1.2 Meg. and the file comprising the filegroup from 4 Gb. to 9 Gb.

    WHY?

    Edited by - Paul Thornett on 06/04/2003 7:02:45 PM

  • June 5, 2003 at 4:46 am

    #459884

    Because most everything done the EM actualy duplicates the table and moves the data into it, then drops the old and renames the new, all the while making sure triggers, permissions, yadayadayada, stay intact.

    On large tables it general is better to go the TSQL route with ALTER TABLE with these types of situations.

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

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