Add a column of zeros to table sql server [duplicate]

Question

Possible Duplicate:
Add column, with default value, to existing table in SQL Server

I have a table in sql server, but want to add a extra column full of zeros

What would be the best approach to do this

att1 att2
---------
1.0   5.8
2.7   3.8
5.1   6.8

becomes

att1 att2  extra
----------------
1.0   5.8   0.0
2.7   3.8   0.0
5.1   6.8   0.0

The default should be 0, so all you have to do is add the column. — Travis J
– Travis J, Commented Apr 26, 2012 at 23:33
Travis, default is NULL isn't it? so if you specify it not null that would probably default it to zero. Anyway, same as the other question. — hookenz
– hookenz, Commented Apr 26, 2012 at 23:37
Whatever you are doing that requires this, stop now. It is a bad idea. Why store data that is always the same? — JohnFx
– JohnFx, Commented Apr 26, 2012 at 23:49
Could be a 'starting point' where some process will come along later and amend some of the rows. Default of 0's prevents having to deal with NULLs... — Ryan
– Ryan, Commented Jul 12, 2013 at 10:14

Community · Accepted Answer · 2017-05-23 11:47:35Z

26

If I recall correctly, it should be something like:

ALTER TABLE table_name 
ADD extra REAL DEFAULT 0

CommunityBot

11 silver badge

answered Apr 26, 2012 at 23:30

hookenz

39.5k50 gold badges197 silver badges305 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

I tried it ,but get the column full of NULL...

if the column is nullable must add the WITH VALUES clause: alter table table_name add extra real default 0 with values;

satcat66 · Accepted Answer · 2012-04-27 01:18:34Z

4

I understand this column will always have value of 0.0. Then it does not have to be a real column

CREATE TABLE extraColumn (
     att1   float  NULL
    ,att2   float  NULL
    ,extra AS 0.0 
)

But if you need that, you can persist it

CREATE TABLE extraColumn (
     att1   float  NULL
    ,att2   float  NULL
    ,extra AS 0.0  PERSISTED 
)

answered Apr 27, 2012 at 1:18

satcat66

1163 bronze badges