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

16 October,2015 by Jack Vamvas

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 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Using NULL as an ordinary value in sql code - SQL Antipattern # 001


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer