Different alter table behaviour on different DBMS's

[laitinen]

Hi!

For test purposes I have a table called 'testtable' with one column 'c1' which is an integer and the primary key of the table.

Then I want to add a new integer column to the table with a default value of 0.

On SQLServer 2005 this is my sql statement:

alter table testtable add c2 int default 0;

This results in a new column where all rows get the value NULL.


When I do the same thing on MySQL or Oracle all rows get the value 0.


Can anyone explain this difference (I know that I could set the "NOT NULL" constraint on the column)


Cheers,

Laitinen

[davide++]

Hi all.

Well, I think that it depends on how DEFAULT is interpreted by various db.
Probably SqlServer applies the default condition to the next records that will be inserted, so the existing records contain NULL. On the contrary, Oracle immediately applies the default value to all (old) records.

[laitinen]

Hi all.

Well, I think that it depends on how DEFAULT is interpreted by various db.
Probably SqlServer applies the default condition to the next records that will be inserted, so the existing records contain NULL. On the contrary, Oracle immediately applies the default value to all (old) records.

Yup that is correct.

I also found the solution: To enforce SQL Server to apply the default value to all old records, you will have to do like this:

alter table testtable add c2 int default 0 with values;

Cheers,

Laitinen