Modify the NULL Default Value of a Column

If you are changing only the NULL default value of a column, you need not specify the column’s datatype.

For example, this command modifies the address column in the authors table from NULL to NOT NULL:

alter table authors
modify address not null

If you modify a column and specify the datatype as NOT NULL, the operation succeeds as long as none of the rows have NULL values. If, however, any of the rows have a NULL value, the operation fails and any incomplete transactions are rolled back. For example, the following statement fails because the titles table contains NULL values for the The Psychology of Computer Cooking:

alter table titles
modify advance numeric(15,5) not null

Attempt to insert NULL value into column ‘advance’, table ‘pubs2.dbo.titles’;
column does not allow nulls. Update fails.
Command has been aborted.

To run this command successfully, update the table to change all NULL values of the modified column to NOT NULL, then reissue the command.