What is the difference between NULL and Blank ?
Null and Empty or Blank values looks similar as both does not have value but it is not like that. There some difference in between them.
Lets look at the difference between Null and Empty as given below:
- Null can be a unknown value or an absence of a value, where as an Empty or Blank string is a value, but is just empty.
- Null can be used for string, Integer, date, or any fields in a database where as Empty is used for string fields.
- As NULL is a unknown value, so a field having NULL is not allocated any memory, where as empty fields have empty value with allocated space in memory.
Lets try to understand NULL and Empty using an example.
Here, we have sample table named Department.
In table you can see column phoneNo1 have a null value while PhoneNo2 have an empty value .
Select * from Department
As, you know Null value is considered as unknown value and memory is not allocated for it while Empty value is value with empty and memory allocated for it.
Lets, check the length for these fields in bytes using Datalength function.
Select DptId, DepartmentName, DATALENGTH(PhoneNo1) as PhoneNo1, DATALENGTH(PhoneNo2) as PhoneNo2 from Department
As you can see , PhoneNo1 length in bytes is still null means memory is not allocated to it yet which means that it is unknown value, while phoneNo2 length is zero (0), that means memory is allocated to empty field.