what type of database?

Most likely not.
You would have to alter the table and update previous records.

“You can do it if you’ll determine a DEFAULT value for a new column added”

Yes, that could be an option, but depending on the DBMS, the DEFAULT clause may only apply when INSERTING new rows.

“but depending on the DBMS”

But this is Standard behaviour.
If you know this odd DBMS, what it’ll return when running the following

alter table T
add f1 int not null default 10;
?

“But this is Standard behaviour”

It depends on what you mean by “this”.

In your example, you made the column non nullable, but you didn’t mention the ‘not null’ part in the original answer (nor did the OP state that he/she wanted the column to be ‘not null’), and you can certainly add a new column with a DEFAULT value without specifying the NOT NULL constraint.

Run the following command on an Oracle database, and run it on a MS SQL Server one, and you will see that it is not ‘standard’:

alter table T
add f1 int default 10;
?

So, as it is not standard (well, it could even be ‘standard’ but that doesn’t mean all vendors will adhere to it), how it will behave depends on the DBMS. However, if the new column will be non nullable then the DEFAULT clause will work. If not, you could add the new column as non nullable, and then make it nullable again, but, depending on the DBMS (and the available indexes), it could be less efficient than adding the column and updating the needed rows afther that.

I realize, however, that my first comment (‘Most likely not…’), without more clarification, was misleading.

It depends on what you mean by “this”.
From SQL:2003 Standard:

Let C be the column added to Table.
i) If C is an identity column, then for each row
…
ii) Otherwise, every value in C is the default value for C.

but you didn’t mention the ‘not null’ part in the original answer

Because NULL is DEFAULT value by default , i.e.

add f1 int

implies

add f1 int default null

So this example is less presentable.

I guess you didn’t run the test I suggested, did you ?

alter table T
add f1 int default 10;

If you run this on Oracle (with the appropriate type change), old records will be updated with ’10′ on the new field (without the need to specify the NOT NULL constraint).
If you run it on SQL Server, the new column will be left null for all previous records (unless a NOT NULL constraint is added).

So, the only way to make sure it will work as desired on all platforms is adding a NOT NULL constraint, and that’s why I thought it was important to note it.

“Because NULL is DEFAULT value by default”
That’s true, but a default value is something different than a NOT NULL constraint. If a NOT NULL constraint is specified then obviously a default value must be defined, but not vice versa.

In the example above a DEFAULT value was specified, so the NULL default value doesn’t apply, but the column can still be left null for previous records (again, depending on the DBMS).

Anyway, it would be interesting to take a look at the SQL 2003 standard. Do you have a link ?

If you run it on SQL Server, the new column will be left null for all previous records (unless a NOT NULL constraint is added).

Indeed. Agreed.

Do you have a link ?

I’d prefer to give it via email, but I can’t find your e-address in profile.
You can write me on smois77@gmail[dot]com.

Carlosdl,

So, the only way to make sure it will work as desired on all platforms is adding a NOT NULL constraint, and that’s why I thought it was important to note it.

The following syntax (SQL Server) solves the issue:

alter table T
add f1 int default 10 with values;

Great.

No more debate