Clarification on ISNULL return type

Question

Hi,

I was assuming ISNULL would return NOT NULL on a computed column(or saved as a view) unless both values are NULL.

But incase if we are passing expression in the order of: NULL,NOT NULL

The output is varying in 2 different scenarios: If the first expression is of lower precedence, then column is marked as Nullable="yes" If the first expression is of higher precedence, then column is marked as Nullable="no"

Here is the example:

Table Create Statement:

Create Table TOne_1 ( col_1 varchar(40) NULL, col_2 varchar(50) NOT NULL, col_3 as ISNULL(col_1,col_2), col_4 as ISNULL(col_2,col_1) );

View Create Statement:

create view v_TOne_1 (v_col_3,v_col_4) as select ISNULL(col_1,col_2), ISNULL(col_2,col_1) from TOne_1

Observe Definitions: exec sp_help TOne_1 exec sp_help v_TOne_1

**Nullable column shows:** "yes" for col_3/v_col_3 "no" for col_4/v_col_4

Refer Screenshot: ISNULL_Table_view_definitions.jpg

Re-writing the modified example:

Table Create Statement:

Create Table TOne_2 ( col_1 varchar(40) NULL, col_2 varchar(50) NOT NULL, col_3 varchar(40) NOT NULL, col_4 varchar(50) NULL, col_5 as ISNULL(col_1,col_2), col_6 as ISNULL(col_4,col_3) );

exec sp_help TOne_2

So col_5 has nullable="yes" and col_6 has nullable="no"

Refer Screenshot: ISNULL_Table_definition_02.jpg

So datatype always remains same as first expression but the nullability varies

Can someone help clarify?

Note: 1.Problem Statement: No Modifications done 2. Modified example as its not as per scenario - did strikethrough and removed view just to be clear (as for view also same behavior is shown)

Answer 1

The problem here is that col_2, the non-nullable column, has a bigger data type than the nullable col_1. If you change col_2 to be varchar(40), col_3 is considered to be non-nullable.

I can't say why you get this result when col_2 has a bigger data type, but I tried this on some older versions of SQL Server, and I found that on SQL 2000 col_3 was considered non-nullable even when col_2 is varchar(50). Whereas SQL 2005 behaves as in your example - but only in compatibility level 90. In compat level 80, I got the SQL 2000 behaviour. This suggests that it is an intentional change, and not some form of regression bug. (On SQL 6.5, even further back, all computed columns were considered nullable.)

Important to keep in mind here is that the return type of isnull is always the data type of the first argument. Thus, the data type of col_3 is varchar(40).

Answer 2

Hi @Ashwin Kanumoori

Refer to this doc:

The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

My understand is that when check_expression returns NULL, the replacement_value is converted to the the type of check_expression first and then return this value*.*

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".