MSSQL - Adding a column to a table without a default value or allowing null

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 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 :)