lad2025 lad2025 - 21 days ago 12
SQL Question

SQL Server calculated column's metadata

Suppose that we have table like below:

CREATE TABLE dbo.tab(id INT PRIMARY KEY
-- other columns
,is_active BIT);

INSERT INTO dbo.tab(id, is_active)
VALUES (1, NULL), (2, 1), (3,0);


The case is to add calculated column that will change
NULL
to
0
and return original values when possible.

Final result:


  • original columns have to stay untouched (so there is no possibility to
    UPDATE
    and
    ALTER
    table)

  • calculated column has to have correct type and nullability

  • cannot use
    VIEW/TRIGGER/...



So let's just add that column:

CREATE TABLE dbo.tab(
id INT PRIMARY KEY
,is_active BIT
,calc_flag1 AS CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT)

,calc_flag2 AS CAST(IIF(is_active IS NULL,0 ,ISNULL(is_active,0)) AS BIT)
,calc_flag3 AS IIF(is_active IS NULL,0 , ISNULL(is_active,0))

,calc_flag4 AS CAST(ISNULL(IIF(is_active IS NULL,0 , is_active), 0) AS BIT)
,calc_flag5 AS ISNULL(IIF(is_active IS NULL,0 ,is_active),0)

,calc_flag6 AS ISNULL(CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT),
CAST(0 AS BIT))
);


LiveDemo


Data:

SELECT * FROM dbo.tab;

╔══╦═════════╦══════════╦══════════╦══════════╦══════════╦══════════╦══════════╗
║id║is_active║calc_flag1║calc_flag2║calc_flag3║calc_flag4║calc_flag5║calc_flag6║
╠══╬═════════╬══════════╬══════════╬══════════╬══════════╬══════════╬══════════╣
║ 1║ NULL ║ False ║ False ║ 0 ║ False ║ 0 ║ False ║
║ 2║ True ║ True ║ True ║ 1 ║ True ║ 1 ║ True ║
║ 3║ False ║ False ║ False ║ 0 ║ False ║ 0 ║ False ║
╚══╩═════════╩══════════╩══════════╩══════════╩══════════╩══════════╩══════════╝


And metadata check:

EXEC sp_help 'dbo.tab';

╔═════════════╦══════╦══════════╦════════╦══════╦═══════╦══════════╗
║ Column_name ║ Type ║ Computed ║ Length ║ Prec ║ Scale ║ Nullable ║
╠═════════════╬══════╬══════════╬════════╬══════╬═══════╬══════════╣
║ id ║ int ║ no ║ 4 ║ 10 ║ 0 ║ no ║
║ is_active ║ bit ║ no ║ 1 ║ ║ ║ yes ║
║ calc_flag1 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag2 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag3 ║ int ║ yes ║ 4 ║ 10 ║ 0 ║ no ║
║ calc_flag4 ║ bit ║ yes ║ 1 ║ ║ ║ yes ║
║ calc_flag5 ║ int ║ yes ║ 4 ║ 10 ║ 0 ║ no ║
║ calc_flag6 ║ bit ║ yes ║ 1 ║ ║ ║ no ║
╚═════════════╩══════╩══════════╩════════╩══════╩═══════╩══════════╝


First attempt:

,calc_flag1 AS CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT)


Correct datatype but it cannot get nullability. I can understand that because it has hardcoded value and column that is nullable so the entire expression is evaluated as nullable.

Second attempt:

,calc_flag2 AS CAST(IIF(is_active IS NULL,0 ,ISNULL(is_active,0)) AS BIT)


Same as before but with explicit
ISNULL(is_active, 0)
. Now it should work because there is hardcoded value and
ISNULL
but it doesn't.

,calc_flag3 AS IIF(is_active IS NULL,0 , ISNULL(is_active,0))


That is interesting, without
CAST
it get
nullable
-
no
, but datatype is
INT
now.

Third attempt:

,calc_flag4 AS CAST(ISNULL(IIF(is_active IS NULL,0 , is_active), 0) AS BIT)


Casting
ISNULL
when the second value is hardcoded. Why this can be
nullable
?


,calc_flag5 AS ISNULL(IIF(is_active IS NULL,0 ,is_active),0)


Of course without casting it works as it should.

Final attempt:

,calc_flag6 AS ISNULL(CAST(IIF(is_active IS NULL,0 ,is_active) AS BIT),
CAST(0 AS BIT))


Now I get correct datatype and nullability but it is somehow ugly.




The question is why it behaves that way and cannot get correct metadata when
calc_flag2
or
calc_flag4
is used.

Answer

The first thing to note is that when using IIF (which is expanded to a CASE expression behind the scenes), the result will only be nullable if all the return expressions are not nullable, so when you use:

IIF(is_active IS NULL,0,is_active)

Although logically when you get to is_active in the expression for false it will never be null because of the condition set, this is irrelevant to the compiler, it can just see that one of the returned expressions is is_active which is a nullable column, therefore the type returned is nullable.

I think the problem can be simplified to why does ISNULL(is_active,0) yield a non nullable bit column, yet simply adding a convert like CONVERT(BIT, ISNULL(is_active,0)), causes the same column to be nullable.

A quick Demo:

CREATE TABLE #tab(
   id INT PRIMARY KEY
  ,is_active BIT
  ,calc_flag1 AS ISNULL(is_active, 0)
  ,calc_falg2 AS CONVERT(BIT, ISNULL(is_active, 0))
);

EXECUTE tempdb.dbo.sp_help '#tab';

Which gives pertinent results of

Column_name Type    Computed    Nullable
--------------------------------------------
id          int     no          no
is_active   bit     no          yes
calc_flag1  bit     yes         no
calc_falg2  bit     yes         yes

Using a specific section from this answer (Credit to Paul White) the reason is that some setting sessions can cause a conversion overflow to return null, so the only way to ensure a non nullable column is if the outer most function is ISNULL.

The required solution can be achieved simply using ISNULL(is_active, 0) as demonstrated above, since this returns a non nullable bit column, but it is worth noting that if a conversion is required, for example, if you needed it to be a int column, then the conversion must be inside the ISNULL. Since ISNULL will return the type of the first argument, only one conversion is required, e.g.

CREATE TABLE #tab(
   id INT PRIMARY KEY
  ,is_active BIT
  ,calc_flag1 AS ISNULL(is_active, 0)
  ,calc_falg2 AS CONVERT(BIT, ISNULL(is_active, 0))
  ,calc_flag_int AS ISNULL(CONVERT(INT, is_active), 0)
);

EXECUTE tempdb.dbo.sp_help '#tab';

Which gives pertinent results of

Column_name     Type    Computed    Nullable
--------------------------------------------
id              int     no          no
is_active       bit     no          yes
calc_flag1      bit     yes         no
calc_falg2      bit     yes         yes
calc_falg_int   int     yes         no
Comments