Re: alter table set default value for money type column

Kei,

Use the WITH VALUES option in your statement, or (probably better)
declare your new column as NOT NULL.  Here are the choices:

Alter table ItemStone add ISPurPrice money NOT NULL default 0
Alter table ItemStone add ISPurPrice money default 0 WITH VALUES

From Books Online, topic ALTER TABLE:

WITH VALUES

Specifies that the value given in DEFAULT constant_expression is stored in a new column added to existing rows. WITH VALUES can be specified only when DEFAULT is specified in an ADD column clause. If the added column allows null values and WITH VALUES is specified, the default value is stored in the new column added to existing rows. If WITH VALUES is not specified for columns that allow nulls, the value NULL is stored in the new column in existing rows. If the new column does not allow nulls, the default value is stored in new rows regardless of whether WITH VALUES is specified.

Steve Kass
Drew University

kei wrote:

I run the sql like the following
Alter table ItemStone add ISPurPrice money default 0 then when I select the itemStone table, I find the field ISPurPrice is still Null, not 0, why?
I'm using SQL Server ver 8.0 (2000)

Thx!! 

.