ISNULL vs CASE Return Type

Question

Recently, I came across an interesting problem 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 the website I'm working on. The query used to have several 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 row in a 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 great change, but it caused something unexpected when retrieving data in C #. With the previous query, the value of the [CanDoSomething] column when accessing 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 processed as a string, do not explicitly match 1 or 0 .

Errors caused by this have already been fixed. I'm just wondering why ISNULL returns a different value than the equivalent CASE expression, and I cannot find answers on Google.

Answer 1

To expand @ dasblinkenlight's description:

The real problem is that in the documentation , case expressions

returns the highest priority type from the type set in * result_expressions * and optional * else_result_expression *. For more information, see Data Type Priority (Transact-SQL) .

While isnull()

returns the same type as * check_expression *

In your case it is bit . Your case expression:

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

has execution paths that return two different data types: int (literal value 0 ) and bit (column value). If you look at the data type precedence table above, int has a higher priority than bit , and thus the bit value is passed to int .

This is because the conversion from bit to int is an expanding conversion. Sql Server bit type is essentially a single-bit integer value, and therefore data is not lost. The conversion from int to bit is a narrowing conversion and there is a chance of data loss (at least in concept).

You would not have encountered this problem if the DBA used coalesce() instead of isnull() , which would be my personal choice, since coalesce() is defined as a return type similar to the case type. coalesce()

Returns the data type of the expression with the highest data type priority. If all expressions are integral, the result is typed as irreplaceable.

Answer 2

The answer to this question is contained in the ISNULL return type documentation :

ISNULL ( check_expression , replacement_value )

Returns the same type as check_expression . If the NULL literal NULL provided as check_expression , returns the replace_value data type. If a literal NULL provided as check_expression and there is no replacement_value , an int returned.

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

<h / "> ^* The CASE experiment was WHEN a.column1 IS NULL THEN 0 ELSE 1 before editing.