How to Change a Column to Allow NULL in MS SQL Server

If you have a column in a SQL Server table that does not allow NULL values and you want to insert row(s) with NULL values, it will then give you an error message. So, if you want to perform successful insert operation, you need to change the column to allow NULL. To do this, you need to use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE table_name
ALTER COLUMN column_name DATA_TYPE [(COLUMN_SIZE)] NULL;

In this syntax:

• First, specify the name of the table from which you want to change the column.
• Second, specify the column name with size which you want to change to allow NULL and then write NULL statement .

Let’s create a table for a demonstration:

CREATE TABLE [DataAnalytics].[dbo].[tbl_customer] (
  customer_id long IDENTITY(1,1) PRIMARY KEY,
  name NVARCHAR(50) NOT NULL,
  email NVARCHAR(30) NOT NULL
);

tbl_customer table has a email column which will not allow NULL values. Now you want to change the column email to allow NULL values. The following statement change the column email to allow NULL:

ALTER TABLE [DataAnalytics].[dbo].[tbl_customer] 
ALTER COLUMN email NVARCHAR(30) NULL;

I have an another tutorial similar to this tutorial where you will learn how to change column not to allow NULL in MS SQL Server. There is also an another important post MS SQL Server String Related Functions where you will learn about different string related functions of MS SQL Server which I think Every Data Engineer should know. You can bookmark this page for your working library.

In this tutorial, I have shown you how to change a column Name to allow NULL in MS SQL Server using ALTER TABLE ALTER COLUMN statement in an existing table. Hope you have enjoyed the tutorial. If you want to get updated, like the facebook page http://www.facebook.com/LearningBigDataAnalytics and stay connected.