This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Should we allow NULL or We should apply NOT NULL ?
As a Database Architect, I would also like to express my opinions and views on this topic.
First single line statement, Should NULL or Should NOT NULL is depending on the situation and importance of data.
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 an empty string in datetime column, it inserts the default value like ‘1900-01-01 00:00:00.000’. – Which is wrong.
In this type of column NOT NULL will not work and we should allow NULL.
Another example is, CHAR data-type column. If we apply NOT NULL and someone enters empty string because of NOT NULL, it occupies the same size of bytes like other rows and which is wrong.
We have to identify all columns and take decisions that in which columns we should allow NULL and in 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.
Many programming languages don’t recognize a NULL value so for that reason, many Database Professionals applied NOT NULL constraint and putting (”)Empty string as a NULL value.
But this is not an enough reason to avoid a NULL because NULL is created for a purpose to express the unknown data which can be valid.
Don’t make all columns nullable, and Don’t make them all NOT NULL. Use NULL only when necessary where the unknown is a valid condition. Everywhere else, use NOT NULL.
