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.
Replaces NULL with the specified replacement value.
DECLARE @NullProperty VARCHAR(10) = NULL DECLARE @NotNullProperty VARCHAR(10) = '123' SELECT ISNULL(@NullProperty, 'test') as NullProperty, ISNULL(@NotNullProperty, 'test') as NotNullProperty
For more details check the following link:
Evaluates the arguments in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.
SELECT COALESCE(NULL, NULL, 3, 4) as Result
For more information about the COALESCE expression visit the link:
Returns a null value if the two specified expressions are equal. Otherwise returns the first expression result.
SELECT NULLIF(1, 1) AS Same, NULLIF(9, 7) AS Different;
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.
SET CONCAT_NULL_YIELDS_NULL OFF SELECT 'test' + NULL AS CNYNOff SET CONCAT_NULL_YIELDS_NULL ON SELECT 'test' + NULL AS CNYNOn
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.
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
When this session setting is set to ON it enables also ANSI_NULLS and 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:
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;