DEFAULT KEYWORD AND NULL V2K

DEFAULTs can be used in a variety of ways, including create table
definitions, specific constraint definitions, or as a keyword in a SQL
statement. But there can be confusion associated with the use of 
DEFAULTs with
regard to the NULL value and how SQL Server handles these situations. 
For
example:

CREATE TABLE Test1 (
Col1 INT NULL CONSTRAINT dfZero DEFAULT 0,
Col2 CHAR(3) NOT NULL
CONSTRAINT dfNA DEFAULT 'N/A')
GO

Having a DEFAULT bound to a column does not mean that the column will
always obtain the 'defaulted' value. The following statements may help 
to
explain this concept.

INSERT INTO Test1 (Col1, Col2) VALUES (1, DEFAULT)
GO
INSERT INTO Test1 (Col1) VALUES (3)
GO

Col1 Col2
----------- ----
1 N/A
3 N/A

Notice when the keyword DEFAULT or the value are not explicitly
supplied, a default value will be placed in the second column of the 
table. Both
rows will obtain the 'N/A' default.

INSERT INTO Test1 (Col1, Col2)
VALUES (5, NULL)
GO

This statement fails due to the NOT NULL constraint on Col2. The 
default
on Col2 is irrelevant at the point of execution since the attempt to
insert NULL in a NOT NULL column failed. You will get the following SQL
error.

Server: Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'Col2',
table 'pubs.dbo.Test1'; column does not
allow nulls. INSERT fails.
The statement has been terminated.

Also know that when you have a nullable column that also has a DEFAULT
bound to it, you can actually store the NULL. If you explicitly provide
NULL as a value, it will obtain the value NULL and not the DEFAULT.

INSERT INTO Test1 (Col1, Col2) VALUES (NULL, DEFAULT)
GO

Col1 Col2
----------- ----
1 N/A
3 N/A
NULL N/A
INSERT INTO Test1 (Col1, Col2) VALUES (DEFAULT, DEFAULT)
GO

----------------------------------------