You either have an error in the Column or in the script.  A not null column will NEVER allow a null to be inserted.

Want to See Something Weird?

Now that we've established the exceptions for NULL handling, let's look at something really, really strange. When a NULL value is inserted into a non-nullable column with a check constraint that doesn't check for NULL, all bets are off! Consider Listing 6.

Listing 6. Check constraint exception to the exceptions

USE SSC
GO
CREATE TABLE #test (val INT NOT NULL CONSTRAINT ck_val CHECK(val < 0 AND val = 0 AND val > 0));

INSERT INTO #test (val) VALUES (NULL);
INSERT INTO #test (val) VALUES (NULL);

/*
Serveur : Msg 515, Niveau 16, État 2, Ligne 3
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.#test____________________00000000002D'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Serveur : Msg 515, Niveau 16, État 2, Ligne 4
Cannot insert the value NULL into column 'val', table 'tempdb.dbo.#test____________________00000000002D'; column does not allow nulls. INSERT fails.
The statement has been terminated
*/

SELECT val
FROM #test
ORDER BY val;

DROP TABLE #test;

Forgot to mention that it's gonna be an excellent refference for nulls from now on .

Nice article. Probably nothing new for those who use SQL for years, but very handy for those new to SQL and confused from the way it is described in BOL or some other book.

So Long, And Thanks For All The NULLs!

Thanks for sharing.it is very common one ,but peple even confuses.

it might clear the confusion of those

Akash