Syntax Difference in Table Alter/Add with and without a NULL

Question

I have a question, but let me first say that this is being performed on a database which I inherited and I am currently trying to improve the design on. Also the reason for the syntax is the fact that there are a lot of tables that the datatypes have been 'tweaked' by tech support people (/facepalm) causing lots of issues.

IF NOT EXISTS(Select * 
                FROM INFORMATION_SCHEMA.COLUMNS 
               WHERE TABLE_NAME = N'RXINFO' 
                 AND TABLE_SCHEMA = N'scriptassist' 
                 AND COLUMN_NAME = N'Price')
BEGIN

  Alter Table [scriptassist].[RXINFO] Add [Price] FLOAT
  Print 'Price Field  nonexistant creating this field'

END
ELSE
BEGIN
  If Not Exists(Select * 
                  From Information_Schema.Columns 
                 Where Table_Name = N'RXINFO' 
                   And Table_Schema = N'scriptassist' 
                   And Column_Name = N'Price'
                   And DATA_Type = N'FLOAT' 
                   AND IsNull(CHARACTER_MAXIMUM_LENGTH, 0) = 0)
  BEGIN

    Alter Table [scriptassist].[RXINFO] Alter Column Price FLOAT
    Print 'Price Field  needed type updating'

  END
END

Is what I am currently doing to determine if a column needs to be altered or added to a database. However even in the case of only having to add say 3-4 columns on a 500K-750K line database, where the table is about 100 columns wide, I'm estimating that this is taking anywhere from 15-20 minutes per column.

Things I have done to try to speed it up:

• Removed the indexes before and then re-add after
• Single user mode
• ensured no connection to the database other than mine

I still don't feel like it should be taking as long as it is, so my question is do I need to explicitly add the NULL after the column type for this to work as fast as I think it should?

Answer 1

If you specifiy "NULL" or perhaps 'NOT NULL WITH DEFAULT 0.0" then only the schema will be updated and the rows in the table will not be altered, so the change will sub second for each table.

If the column is NULLable it doesn't have to be there so there is no need to update the rows when you alter the schema.

If the column is not NULLable without a default then when you ALTER the schema to add the column every row in the existing table will be updated to add a column with the default default value of 0.0. This is why your alters take minutes.

If you specify 'NOT NULL WITH DEFAULT' then the "DEFAULT" is added when the row is read so again there is no need to update the table rows.

In both cases the current INSERT/UPDATE SQL will work without any changes. If you add a "NOT NULL" column with no default then current update SQLs will bomb out unless you add in the price column. This could be good thing or a bad thing depending on what you want to achieve.