Understanding and working with NULL in SQL Server
|Graphic representation of the difference between 0 and NULL|
Image taken from 9gag.com
SQL Server implements the lack of value with NULL, that is datatype independent and indicates missing value, so the logical validations are different, this is better known as Three-Valued Logic, where any predicate can evaluate to True, False or Unknown.
I see a common error, referring to null like "Null values" but the correct definition of null is "Lack of value" so you must refer to it as null, in singular form.
On this post, we will learn how to work with null in SQL Server.
Declaring and assigning NULL
For working with null, SQL Server engine uses the reserved word NULL to refer to it.
It is datatype independent so you just have to assign it to any variable or field, using the equal operator =, as you can see on this example:
If we run the statement, we will obtain these results, the same for each data type, as expected.
For inserting and updating fields with NULL we do it like on this example:
Be careful when working with null, the equal operator = is only used for assignment.
Operations and comparison against NULL
As we stated earlier, any predicate or comparison can evaluate to TRUE, FALSE or UNKNOWN, so when a value is unknown we don't know if it is true or false, so comparing or working any value with unknown is also unknown.
For example, the following operations result is NULL in all cases:
When comparing to null, we also obtain null as well, as in those examples, as you can see, even comparing null to null is unknown, and when we execute below code, we obtain NO for all:
So, if we want to compare value or column and check if is null or not what must we do?
SQL Server implements the IS NULL and IS NOT NULL to compare against null, usage is as follows:
With these tools we are ready to work with null in our databases, so now you should follow some considerations to not impact your database performance.
Special considerations for good performance
As the last point, I would like to give you some tips for dealing with NULL
Prefer IS NULL over ISNULL()
When possible, try to compare predicates using IS NULL, before casting NULL to default values using ISNULL(), because casted values are not SARGABLE.
Take as an example of these two queries, they are equivalent, but the first one has better performance over the second:
These are the execution plans:
|First query execution plan, an index seek is used :)|
|Second query execution plan, an index scan is used :(|
|We get a warning on the second execution plan|
Be careful with aggregations over nonexistent data
When you perform aggregations, be extra careful with no existent data, even when columns do not allow null, aggregate data that does not exist on the table returns null, contrary to what one could think can be the usual (a 0 value), as you can see on this example:
And the query results:
For those cases, you should use the ISNULL() function after SUM.
As I always recommend: test anything before going live, and use default values and not null columns when possible, to make your life easier.