Why do case/iif return nullable datatypes?

Consider the following sproc:

create or alter procedure TestSproc
as
begin

declare @ZeroBit bit = 0, @OneBit bit = 1

-- Table.Id is a nullable integer column
select iif(Table.Id is null, @ZeroBit, @OneBit) as ColumnNotNull

end

Once it's been compiled, running the following:

select name, system_type_name, is_nullable
  from sys.dm_exec_describe_first_result_set_for_object(object_id('TestSproc'), 0)

shows the ColumnNotNull column correctly typed as a bit, but that it is nullable.

The nullability does not make sense to me, since the arguments I passed to iif are non-null constants, so why is the resultant column nullable? And is it possible to get it to be non-nullable without wrapping the expression in isnull to get around this, which to my mind is - or rather, should be - unnecessary?

The documentation for iif makes no mention of nullability, merely stating that this function "Returns the data type with the highest precedence from the types in true_value and false_value".

First off, the column is_nullable returned from sys.dm_exec_describe_first_result_set_for_object has the following definition as per docs.Microsoft.com:


Contains the value 1 if the column allows NULLs, 0 if the column does
not allow NULLs, and 1 if it cannot be determined that the column
allows NULLs.

This means that the engine could also be looking for a situation where it can't predict the nullability of the return column. (This is more for your information than anything else - in this case it doesn't appear to be happening)

Now when we simplify the stored procedure and eliminate the variables as such:

CREATE PROCEDURE TestProc
AS

SELECT IIF('hello' = 'hello','true','false')
GO

We can see the nullability goes away.

This is because the variables you are utilising in your procedure (@Zerobit and @Onebit) are NULLABLE by definition. The variables themselves are getting set at run-time, but the underlying metadata for the data type of the variable is still nullable.

There is also a possibility that the query optimiser needs to account for all scenarios, and it can't use the value of the variable at runtime for an all-use plan.

If we rewrite the query and do not set the value of the variables, we will see the same results (The column metadata for the return of the procedure is still NULLABLE):

CREATE PROCEDURE TestProc
AS

DECLARE @trueoutput VARCHAR(10)
DECLARE @falseoutput VARCHAR(10)

SELECT IIF('hello' = 'hello',@trueoutput,@falseoutput)
GO

My suggestion for the above case would be (as Mikael Eriksson says) to use constants - this is unless it's a cut down example and this isn't possible. The issue then would be the returned data type would always be nullable without being wrapped with ISNULL, CASE etc

StackExchange Database Administrators Q#206466, answer score: 6