NOT NULL is integrity CONSTRAINT. It does not allow creating of the row where column contains NULL value. Most discussed question about NULL is what is NULL? I will not go in depth analysis it. Simply put NULL is unknown or missing data. When NULL is present in database columns, it can affect the integrity of the database. I really do not prefer NULL in database unless they are absolutely necessary. (Please make sure it is just my preference, and I use NULL it is absolutely needed).
To prevent nulls to be inserted in the database, table should have NOT NULL constraint. Two ways NOT NULL constraint can be implemented on database.
1) Implement Constraint when Table is created
2) Implement Constraint after Table is created
Following example demonstrates both the way to create NOT NULL constraints.
-- NOT NULL Constraint when Table is created
CREATE TABLE ConstraintTable
(ID INT, ColSecond INT NOT NULL)
-- NOT NULL Constraint after Table is created
ALTER TABLE ConstraintTable
ALTER COLUMN ID INT NOT NULL
DROP TABLE ConstraintTable
Reference : Pinal Dave (http://blog.SQLAuthority.com)