Alternate table with new not null Column in existing table in SQL

Question

How to add not null Column in existing table in SQL Server 2005

User · Answer

Choose either   a  Create not null with some valid default value  b  Create null  fill it  alter to not null

User · Answer

The easiest way to do this is    ALTER TABLE db TABLENAME ADD COLUMN  datatype  NOT NULL DEFAULT  value    Ex   Adding a column x  bit datatype  to a table ABC with default value 0  ALTER TABLE db ABC ADD COLUMN x bit NOT NULL DEFAULT 0   PS   I am not a big fan of using the table designer for this  Its so much easier being conventional   old fashioned sometimes      Hope this helps answer

User · Answer

IF NOT EXISTS  SELECT 1 FROM syscolumns sc JOIN sysobjects so ON sc id   so id WHERE so Name    Table1  AND sc Name    Col1   BEGIN ALTER TABLE Table1 ADD Col1 INT NOT NULL DEFAULT 0  END GO

User · Answer

There are two ways to add the NOT NULL Columns to the table     ALTER the table by adding the column with NULL constraint  Fill the column with some data  Ex  column can be updated with    ALTER the table by adding the column with NOT NULL constraint by giving DEFAULT values  ALTER table TableName ADD NewColumn DataType NOT NULL DEFAULT

User · Answer

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  lt YourTable gt   ADD  lt NewColumn gt   lt NewColumnType gt  NOT NULL DEFAULT  lt DefaultValue gt

[sql-server] Alternate table with new not null Column in existing table in SQL

Examples related to sql-server