How to Change Data Type of a Column in MS SQL Server

Sometimes you may need to change data type of a column in a table in MS SQL Server. To do this, you need to use the ALTER TABLE ALTER COLUMN statement as follows:

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type[(column_size)];

In this syntax:

• First, specify the name of the table from which you want to change the data type of a column.
• Second, specify the column name which you want to change the data type 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,
  dob varchar(10) NOT NULL,
  email NVARCHAR(30) NOT NULL
);

tbl_customer table has a dob column of varchar data type 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.

In this tutorial, I have shown you how to change a column Name to allow NULL in 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 my facebook page http://www.facebook.com/LearningBigDataAnalytics and stay connected.