This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Should we allow NULL or We should apply NOT NULL ?
I have found this question and discussion in many forums and blogs.
As a Database Architecture, I would also like to express my opinions and views on this topic.
In this post, I am trying to explain this in my easy words.
First single line statement, Should NULL or Should NOT NULL is depending on the situation and importance of data.
First, NULL means the pointer set to 0x00 in the row header that means no data to access.
Any search query or engine can easily identify a data which has a NULL.
As per ANSI Standard NULL means UNKNOWN value whatever data-type like, integer, varchar or datetime.
I read many articles and found that many database professionals are suggesting that never go with NULL. Means, you have to enter a value in each column or else they will allow (”) empty string as NULL values.
e.g. In SQL Server if you enter empty string in datetime column, it inserts the default value like ‘1900-01-01 00:00:00.000’.
In this type of column NOT NULL will not work and we should allow NULL for this kind of columns.
Another example is, CHAR data-type column. If we apply NOT NULL and someone enter empty string because of NOT NULL, it occupies the same size of bytes like other rows and which is totally wrong.
We have to identify all columns and take decisions that in which columns we should allow NULL and which columns we should apply NOT NULL.
The Next is about performance,
IS NULL or IS NOT NULL condition is always faster than any other string operation.
We should also apply NOT NULL constraint on a required column like Password column but it doesn’t mean that user can enter (”) empty string again it shows as a blank password.
Many programming languages don’t recognize a NULL value so for that reason many Database Professional applied NOT NULL constraint and putting (”)Empty string as a NULL value.
But this is not a enough reason to avoid a NULL because NULL is created for a purpose to express the unknown condition which can be valid.
Don’t make all columns nullable, and Don’t make them all NOT NULL. Use NULL only when necessary, in situations where unknown is a valid condition. Everywhere else, use NOT NULL.