Cant add column with out dropping table in sql server [duplicate]

Question

I have a production table that has 100 million rows , and for a reporting purpose we need to add one additional column which will be a Numeric (18,0).Obviously SQL won't let me add that column with out dropping the table.Any other way that i can do this with out affecting the data?

Answer 1

You can add a column without dropping the table.

ALTER TABLE YourTable
ADD  SomeColumnName NUMERIC(18,0)

UPDATE

If you want the column NOT NULL then you'll have to make it accept NULL first, then set the values through an update, and lastly alter the column to NOT NULL.

Or set a default value such as

ALTER TABLE YourTable
ADD SomeColumnName DECIMAL(18,0) NOT NULL DEFAULT 1