The NULL in SQL Server

Dealing with NULLs can always be fun in SQL Server. Creation of nullable columns can bring some complexity in the application logic and should be handled with caution.
Kalen Delaney recommends in his book “Microsoft SQL Server 2012 Internals” to never allow NULL values in our tables.

However there are some cases that requires using of nullable properties and defining NULL columns in our tables and here are the different options we have as SQL developers in order to handle the NULL values.

ISNULL

Replaces NULL with the specified replacement value.

Examlpe:

DECLARE @NullProperty VARCHAR(10) = NULL
DECLARE @NotNullProperty VARCHAR(10) = '123'

SELECT ISNULL(@NullProperty, 'test') as NullProperty,
       ISNULL(@NotNullProperty, 'test') as NotNullProperty

Output:

For more details check the following link:
https://docs.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017

COALESCE

Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.

Examlpe:

SELECT COALESCE(NULL, NULL, 3, 4) as Result

Output:

For more information about the COALESCE expression visit the link:
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/coalesce-transact-sql?view=sql-server-2017

NULLIF

Returns a null value if the two specified expressions are equal. Otherwise returns the first expression result.

Examlpe:

SELECT NULLIF(1, 1) AS Same, NULLIF(9, 7) AS Different;

Output:

CONCAT_NULL_YIELDS_NULL

That is a database option and a session setting. If it is turned on (set to ON) when you try to concatenate a string with a NULL value will return NULL result.

When it is OFF – the string itself will be returned.

Examlpe:

SET CONCAT_NULL_YIELDS_NULL OFF
SELECT 'test' + NULL AS CNYNOff

SET CONCAT_NULL_YIELDS_NULL ON
SELECT 'test' + NULL AS CNYNOn

Output:

ANSI_NULLS

This is another database option and a session setting.
When its value is set to OFF this allows us to check whether a value for a property is NULL or not by comparing it with NULL. This means that SQL Server will allow us to use the syntax X = NULL as equivalent of X IS NULL check and also the X <> NULL (or X != NULL) like X IS NOT NULL.

By default its value is ON.

Examlpe:

SET ANSI_NULLS OFF
SELECT  CASE WHEN (NULL = NULL) THEN 1 
	     WHEN (NULL IS NULL) THEN 2
	ELSE 0 END AS ANOff

SET ANSI_NULLS ON
SELECT  CASE WHEN (NULL = NULL) THEN 1 
             WHEN (NULL IS NULL) THEN 2
	ELSE 0 END AS ANOn

Output:

ANSI_DEFAULTS

When this session setting is set to ON it enables also ANSI_NULLS and ANSI_NULL_DFLT_ON.

ANSI_NULL_DFLT_ON

Modifies the behavior of the session to override default nullability of new columns when the ANSI null default option for the database is false:

https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-null-dflt-on-transact-sql?view=sql-server-2017

That means that when we set this option to false when a new column is created by CREATE or ALTER TABLE command by default the column will be NOT NULL.

Check the SET values for the mentioned options

We can check the already set values in the current session for the options mentioned in this article by the querying the dm_exec_sessions dmv.

SELECT ANSI_DEFAULTS, ANSI_NULL_DFLT_ON, CONCAT_NULL_YIELDS_NULL, ANSI_NULLS 
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;

Output:

One thought on “The NULL in SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s