24 April 2014

How to Change a Nullable Column to NOT NULL

We have a table that has a nullable column (column C1) with some inserted NULL values:
nullable_column_null_values
We want to change the nullable C1 column to NOT NULL.
First we need to update all the records that are NULL to some other 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:
nullable_column_null_values_replaced
Now we can change the column to NOT NULL:
ALTER TABLE dbo.Test1
ALTER COLUMN C1 nvarchar(50) NOT NULL

No comments:

Post a Comment