How to Alter a Column from Null to Not Null in SQL Server?

To alter a column from NULL to NOT NULL in SQL Server, you can use the ALTER TABLE statement with the MODIFY keyword. There are two methods to do it:

Method 1: Using ALTER TABLE statement with MODIFY keyword


ALTER TABLE table_name
ALTER COLUMN column_name data_type NOT NULL;


Suppose we have a table named "employees" with a column named "salary" which allows NULL values. We want to alter the column to not allow NULL values.

ALTER TABLE employees

This will modify the "salary" column to not allow NULL values.

Method 2: Using SQL Server Management Studio (SSMS)

  • Open SSMS and connect to the SQL Server instance.
  • Expand the Databases folder and select the database containing the table you want to modify.
  • Expand the Tables folder and select the table you want to modify.
  • Right-click on the table and select "Design".
  • In the table designer, select the column you want to modify.
  • In the "Column Properties" section, change the "Allow Nulls" property to "No".
  • Save the changes by clicking the "Save" button or pressing Ctrl + S.

Note: Before altering a column from NULL to NOT NULL, make sure that there are no NULL values in the column. If there are NULL values, you will need to either update them to non-NULL values or delete them before altering the column.

About the author

William Pham is the Admin and primary author of With over 10 years of experience in programming. William Pham is fluent in several programming languages, including Python, PHP, JavaScript, Java, C++.