Adding a new not null column to an existing table?

Answer 1

Hi,

You will either have to specify a DEFAULT, or add the column with NULLs allowed, update all the values, and then change the column to NOT NULL.

ALTER TABLE [YourTable]
ADD [NewColumn] [NewColumnType] NOT NULL DEFAULT [DefaultValue]

Hope this helps

Thanks

Bhanu

Answer 2

Fahma,

You may add the field as a not null column with a default constraint for the existing rows. Can be done as follows:

alter table table_name
add col_name varchar(255) not null
constraint DF_table_name_col_name default ('ABCDE')

After doing this, the table is altered with the new column and updated with the new column values. Then if you choose, you may also drop the constraint as follows:

alter table table_name drop constraint DF_table_name_col_name

---

Thanks,
Jay
<If the post was helpful mark as 'Helpful' and if the post answered your query, mark as 'Answered'>

Answer 3

>>>>After doing this, the table is altered with the new column and updated with the new column values. 

This has been changed in SQL Server 2012... A operation is very fast,

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Thanks that was helpful.