SQL Server - Overriding a Default Value with NULL

Problem

You are inserting into a column having a default value, and you wish to override that default value by setting the column to NULL. Consider the following table:

 create table D (id integer default 0, foo VARCHAR(10))

You wish to insert a row with a NULL value for ID.

Solution

You can explicitly specify NULL in your values list:

 insert into d (id, foo) values (null, 'Brighten')

Discussion

Not everyone realizes that you can explicitly specify NULL in the values list of an INSERT statement. Typically, when you do not wish to specify a value for a column, you leave that column out of your column and values lists:

 insert into d (foo) values ('Brighten')

Here, no value for ID is specified. Many would expect the column to taken on the null value, but, alas, a default value was specified at table creation time, so the result of the preceding INSERT is that ID takes on the value 0 (the default). By specifying NULL as the value for a column, you can set the column to NULL despite any default value.

** If you want the Full Table detail. Refer the SQL Table in Label List. Or Click here to View the Table