How to ALTER a Column from Null to NOT Null in SQL Server ?

Published on August 20, 2016 by abundantcode

Filed under SQL Server

Last modified August 20, 2016

Print this page

rate 1 star rate 2 star rate 3 star rate 4 star rate 5 star
Your rating: none, Average: 0 (0 votes)

This article have been viewed 1739 times

Assume a scenario where you have a table that contains columns which are Nullable integer column. You might want to update them to NOT NULL column (adding NOT NULL constraint) and set its value to zero.

How to ALTER a Column from Null to NOT Null in SQL Server ?

This would be a two step process where you should first update the column values to 0 and then write a Data Definition Language (DDL) to alter the column of the table.

Assuming the table Name is "Employee" and the field is LeavesTaken, this is how both the queries would look like.

UPDATE Employee SET LeaveTaken=0 WHERE LeaveTaken IS NULL

The ALTER table query would look like this.

ALTER TABLE Employee ALTER COLUMN LeaveTaken INTEGER NOT NULL
If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment


x

You might be Interested in these Posts

How to Perform Inner Join using LINQ in C# ?

Are you looking for the syntax on how to perform inner join using LINQ in C# ? . Below is a sample c...

C Program to find the G.C.D of a number using Recursive function

Problem Write a program in C to find the find the G.C.D of a number using recursion and display the ...

C Program to find the factorial of a number using Recursive function

Problem Write a program in C to find the factorial of a number using recursion and display the resu...