Add a non null column to a sql table script - getting an error.

Question

SQL Server 2005.

The following 3 lines of sql work without error if the Is_Active column had previously existed in the Dim_Form table.

ie if Is_Active has not existed previously, running the following 3 lines gives an error as displayed below;

ALTER TABLE dbo.Dim_form add Is_Active bit NULL 
UPDATE dbo.Dim_form set Is_Active = 1
ALTER table dbo.Dim_form alter column Is_Active bit NOT NULL

I get this error only if its a 'brand new' column

Msg 207, Level 16, State 1, Line 2
Invalid column name 'Is_Active'.

If I run this

ALTER TABLE dbo.Dim_form add Is_Active bit NULL 
GO

UPDATE dbo.Dim_form set Is_Active = 1
ALTER TABLE dbo.Dim_form alter column Is_Active bit NOT NULL 

-- and now drop the newly created column
ALTER TABLE dbo.Dim_form DROP COLUMN Is_Active

Now I can run the original 3 lines of sql successfully - any ideas what is happening with this?

Why would the prior existence of a column have an effect on the script?

Answer 1

When the SQL Is parsed in the first version the column doesn't exist. So the Parsing phase fails.

In the second version you are splitting the SQL up in to separate batches, so each batch is parsed on its own. The first batch creates the column, the second batch is then parsed prior to running and everything is okay because the column now exists.

Answer 2

This doesnt answer my question, but is the script I used that worked consistently whether or not the Is_Active column had existed previously in a table.

    ALTER TABLE dbo.Dim_form
        ADD Is_Active bit NOT NULL
           CONSTRAINT TEMP_ISACTIVE_CONSTRAINT_Dim_form DEFAULT 1

    ALTER TABLE dbo.Dim_form
        DROP CONSTRAINT TEMP_ISACTIVE_CONSTRAINT_Dim_form