How do I add a column, with a default value, to an existing table in SQL Server 2008 or 2012?

Question

How do I add a column, with a default value, to an existing table in SQLServer 2008?

Pritesh Tayade · Accepted Answer · 2019-03-14T07:50:35.040

20

ALTER TABLE {TABLENAME} ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

edited Mar 14 '19 at 07:50

answered Jun 05 '13 at 06:11

Pritesh Tayade

score 9 · Answer 2 · answered Jun 05 '13 at 06:10

9

Use ALTER TABLE. For example

ALTER TABLE Table1 
ADD  col2 INT DEFAULT 0;

answered Jun 05 '13 at 06:10

peterm

2

As a general rule, it is best to name your constraints. If you don't it will get a random name, and they will differ between different databases (eg at different client installations). This will make scripting changes difficult in some cases (ie you will have to resort to workarounds). – TT. Jun 05 '13 at 06:22
1

@TT. Fair point. Agreed. – peterm Jun 05 '13 at 07:47

2 Answers2