If you are on this page you likely got an error similar to the following:
Msg 4901, Level 16, State 1, Line 8 ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified
The reason for this is that you are trying to add a column to a table without specifying the value for it, columns must have a value or be null. Let us say we have the following table:
CREATE TABLE People (
[Id] INT PRIMARY KEY IDENTITY(1,1),
[Name] NVARCHAR(30) NOT NULL
)
INSERT INTO People (Name) VALUES ('Peter')
In the above we have a simple table called People with an Id and a Name. If I try to run the following alter statement on the table, I get the previously mentioned error:
ALTER TABLE People
Add [Lastname] NVARCHAR(50) NOT NULL
As you can see the new column does not allow null and it has no default value. Sometimes we want to create a new column with a value for all old rows, but new rows should not be allowed to be null or have a default value. There is no way to do this without adding these constraints and then removing them. However by first adding a default value (a constraint) and removing it afterwards we can achieve the same thing:
ALTER TABLE People
Add [Lastname] NVARCHAR(50) NOT NULL
CONSTRAINT People_Lastname_Temp_Constraint DEFAULT ('Rasmussen')
ALTER TABLE People
DROP CONSTRAINT People_Lastname_Temp_Constraint
By running the above script we get all the old rows in the table set to the value "Rasmussen", but going forward you have to provide a value.
I hope this helps you to alter your table, please let me know in the comments if it did. If you know a better way please let me know :)
A newer version of this post can be found here.