ISNULL vs CASE Return Type

Question

I recently ran into an interesting issue with changing CASE statements to ISNULL functions in TSQL. The query I was working with is used to get some user attributes and permissions for a website I work on. Previously the query had a number of CASE statements similar to the following:

NOTE: a.column1 in the example is of type bit in the table. Also note that the [CanDoSomething] result column is sometimes used as a string in the website.

SELECT
    ...
    CASE
        WHEN a.column1 IS NULL THEN 0
        ELSE a.column1
    END [CanDoSomething]
    ...
FROM
    a

The DBA replaced these CASE statements with the ISNULL function:

SELECT
    ...
    ISNULL(a.column1, 0) [CanDoSomething]
    ...
FROM
    a

This seems like a fine change, but it caused something unexpected when retrieving the data in C#. With the previous query, the value of the [CanDoSomething] column when accessed from a DataTable in C# was 1 or 0. When we changed to using ISNULL, the value in C# was then changed to true or false which, when treated as a string, are obviously not the same as 1 or 0.

The errors this caused have already been addressed. I'm just curious why ISNULL returns a different value than an equivalent CASE statement and I can't seem to find any answers on Google.

Answer 1

To expand on @dasblinkenlight's explanation:

The real issue is that, per the documentation, case expressions

return the highest precedence type from the set of types in *result_expressions* and the optional *else_result_expression*. For more information, see Data Type Precedence (Transact-SQL).

While isnull()

returns the same type as *check_expression*

In your case, that is a bit. Your case expression:

CASE
  WHEN a.column1 IS NULL THEN 0
  ELSE                        a.column1
END

has execution paths that returns two different data types, an int (the literal value 0) and a bit (the value of the column). If you look at the data type precedence chart linked above, int is of higher precedence than bit and thus the bit value is cast to an int.

That is because the conversion from bit to int is a widening conversion. Sql Server's bit type essentially, a 1-bit integer value and so no data is lost. The conversion from int to bit is a narrowing conversion and the possibility of data loss exists (at least in concept).

You wouldn't have encountered this problem, if the DBA had used coalesce() instead of isnull(), which would have been my personal choice, as coalesce() is defined to return a type similar to that of case. coalesce()

Returns the data type of expression with the highest data type precedence. If all expressions are nonnullable, the result is typed as nonnullable.

Answer 2

The answer to this is in the documentation of ISNULL's return type:

ISNULL ( check_expression , replacement_value )

Returns the same type as check_expression. If a literal NULL is provided as check_expression, returns the datatype of the replacement_value. If a literal NULL is provided as check_expression and no replacement_value is provided, returns an int.

Unlike your CASE expression that always returns an int ^* returns an int because of zero in the first branch, ISNULL will return a value of the type of its first parameter, i.e. a.column1.

---

^* The CASE expression was WHEN a.column1 IS NULL THEN 0 ELSE 1 before the edit.