Add a column of zeros to table sql server

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, Apr 26 '12 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, Apr 26 '12 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, Apr 26 '12 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, Jul 12 '13 at 10:14

score 26 · Accepted Answer · edited May 23 '17 at 11:47

26

If I recall correctly, it should be something like:

ALTER TABLE table_name 
ADD extra REAL DEFAULT 0

edited May 23 '17 at 11:47

Community

answered Apr 26 '12 at 23:30

hookenz

I tried it ,but get the column full of NULL... – edgarmtze Apr 26 '12 at 23:33
4

if the column is nullable must add the `WITH VALUES` clause: `alter table table_name add extra real default 0 with values;` – Remus Rusanu Apr 27 '12 at 00:09

score 4 · Answer 2 · answered Apr 27 '12 at 01:18

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 
)

2 Answers2