Using NULL as an ordinary value in sql code - SQL Antipattern # 001

Using a  NULL value represents an unknown or missing value in a table column.  Based on this definition  , it’s easy to find  examples of code where NULL is used an ordinary value. This antipattern leads to further problems.  

Identifying and fixing this habit, leads to less ambiguity  of the data.

The first antipattern is when  a NULL is used as an ordinary value.  A typical example is :

SELECT NULL + 1000

This will result in a NULL . This leads to an unknown value. An unknown value leads to deeper problems in our code, with all sorts of exception handling and workarounds.

The second antipattern is when an ordinary value is used as a NULL.  Some developers are aware that using NULL as an ordinary value leads to further problems, so they attempt to use an ordinary value. A common ordinary value is -1. A typical example is:

 INSERT INTO musicians(reference) VALUES(-1)

In this scenario, the column “reference” is a foreign key therefore this won’t work.

It is important to use NULL in the correct way. One of the consequences of misusing NULL is it requires documentation to explain the use of NULL in a given column. The meaning of the value is not obvious. It has no relationship to the column.

Do it better

Read More on t-sql

Performance Tuning – The wrong way

5 things SQL Server developers should know about SQL Server

Object Dependency in SQL Server