We have a table that has a nullable column (column C1) with some inserted NULL values:
We want to change the nullable C1 column to NOT NULL with default value (it will be value 'w').
We will add the default constraint (value 'w') to the column C1:
ALTER TABLE dbo.Test1
ADD CONSTRAINT col_c1_def DEFAULT 'w' FOR C1
Then we need to update all the records that are NULL to the value that will be the default value, before changing the column to NOT NULL:
UPDATE dbo.Test1
SET C1 = 'w'
WHERE C1 IS NULL
Now the table column C1 has no NULL values:
Now we can change the column to NOT NULL:
ALTER TABLE dbo.Test1
ALTER COLUMN C1 nvarchar(50) NOT NULL
Read related article:
How to Change a Nullable Column to NOT NULL