How to add a column in MSSQL with default value?

Question

In MSSQL 2014 I would like to use an update syntax which is adding a new column with a default value. Is this possible somehow?

• Milad Aghamohammadi: You should use alter table, not update. visit stackoverflow.com/questions/92082/…
• Yann G: This has already been answered here, still works the same with 2014.
• Diado: Possible duplicate of Add a column with a default value to an existing table in SQL Server
• Roman: Hi Peter, greetings from Vienna, welcome on Stackoverflow! Check out this link on how to ask a good SQL question

Answer 1

Something Like below:

alter table myTable add myNewColumn nvarchar(20) default 'myDefaultValue' not null

Answer 2

Here is a complete reproducible example.

Create table

CREATE TABLE employees
( employee_id INT NOT NULL,
 last_name VARCHAR(50) NOT NULL,
 first_name VARCHAR(50) NOT NULL,
 salary MONEY
);
INSERT INTO employees
VALUES (1, 'Miller', 'Peter', 80000);
INSERT INTO employees
VALUES (2, 'Myer', 'Joana', 90000);

Check contents

SELECT * FROM employees;
employee_id last_name first_name salary 
1 Miller Peter 80000 
2 Myer Joana 90000 

Add new column with default value

ALTER TABLE employees
ADD email VARCHAR(50) DEFAULT '[email protected]' NOT NULL;

Check the result

SELECT * FROM employees;
employee_id last_name first_name salary email
1 Miller Peter 80000 [email protected]
2 Myer Joana 90000 [email protected]

Here is a SQL fiddle with this example.

Answer 3

Try This :-

CREATE TABLE employees
( employee_id INT NOT NULL DEFAULT 0,
 last_name VARCHAR(50) NOT NULL DEFAULT 'jon',
 first_name VARCHAR(50) NOT NULL DEFAULT 'jona',
 salary MONEY DEFAULT 0
);
ALTER TABLE employees ADD designation VARCHAR(50) DEFAULT 'TL' NOT NULL; 

• Roman: This creates a new table with default values, yes. But it does not update an existing table, which I think OP was after.
• Prakash Choudhary: Please try now I Added Alter Query also
• Roman: Hey Prakash, your SQL statement is faulty. You are adding a constraint instead of a column (and also reference #employees instead of employees). Happy coding!