ISNULL - Clarification on how Nullable on data type affects for resultant columns

Question

Data Creation:

Create Table: create table tone_12 ( col_1 varchar(20) NULL, col_2 varchar(40) NULL, col_3 varchar(20) NOT NULL, col_4 varchar(40) NOT NULL, col_5 as isnull(col_1,col_4), col_6 as isnull(col_2,col_3) )

Insert statement: Insert into tone_12 (col_1,col_2,col_3,col_4) values ('aa1','bb1','cc1','dd1'), (NULL,NULL,'cc2','dd2');

Select Statement: select col_1,col_2,col_3,col_4, isnull(col_1,col_4) as col_55, isnull(col_2,col_3) as col_66 from tone_12

Results for select: col_1-->col_2-->col_3-->col_4-->col_55-->col_66 aa1-->bb1-->cc1-->dd1-->aa1-->bb1 NULL-->NULL-->cc2-->dd2-->dd2-->cc2

Create View Statement: create view v_tone_12 as select col_1,col_2,col_3,col_4, isnull(col_1,col_4) as col_555, isnull(col_2,col_3) as col_666 from tone_12

Viewing description of Table and View using sp_help: exec sp_help tone_12 exec sp_help v_tone_12

Refer screenshot for sp_help for table and view: table_and_view_sp_help.jpg

Clarifications needed:

1.Though we are seeing Nullable values for Point 1(table) and Point 3(view) as yes for col_5(table),col_555(for view) no for col_6(table),col_666(for view) (Based on higher data precedence present for first parameter) Because the second expression is NOT NULL in both the cases, anyway the resultant values would be NOT NULL in both the cases(as the values are taken from col_4 and col_3 respectively), though we insert data as NULL values for col_1 and col_2

Question: a)How and b)When does the Nullable of resultant column affect - for example col_5 and col_555 are Nullable but is there any scenario where this is allowed or comes into picture?

2.How can we check the resultant data type/nullable of col_55 col_66 which are part of Select statement (I know we can do it for table and view using sp_help)?

Can someone please help explain these 2 points?

Answer 1

For the first question the answer is the same as to your previous question. The resulting data type from isnull is always the data type of the first parameter. For col_5 the first parameter isnull is varchar(20), and the second is varchar(40). This means that truncation can occur. How the truncation can lead to a NULL, I fail to see, but it seems to be a concious decision. As I mentioned there is a difference between compatibility level 80 and later compat levels.

The answer to the other question is that you can do:

SELECT ...
INTO #temp
FROM  ...
EXEC tempdb..sp_help '#temp'
DROP TABLE #temp