3

I have a table, with Id as the primary key.

create table Anything
(
    Id bigint not null primary key identity(1, 1)
)

When I look at this table in Object Explorer, I see this image of course:

Enter image description here

As you can see, column Id is not null.

Then I create a dummy view on this dummy table:

create view IdIsTwoView
as

select
    Id,
    (
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    ) as IdIsTwo
from Anything

But this time, in Object Explorer I see this result:

Enter image description here

As you can see, in spite of this fact that my case clause is all-encompassing and covers 100% of all records, and has an answer for all records, it's nullable.

Why does SQL Server have this weird behavior? And how do I force it to be not null?

P.S we have an infrastructure to generate code dynamically, and this behavior causes us trouble and we have to manually go and change all bool? types in C# to bool.

8

To answer the why part of your question, the CASE expression result is nullable because the CAST function result is nullable. This can be observed with:

EXEC sp_describe_first_result_set N'SELECT CAST(1 AS bit) AS result;';

Result (abbreviated):

+-----------+----------------+--------+-------------+----------------+------------------+
| is_hidden | column_ordinal |  name  | is_nullable | system_type_id | system_type_name |
+-----------+----------------+--------+-------------+----------------+------------------+
|         0 |              1 | result |           1 |            104 | bit              |
+-----------+----------------+--------+-------------+----------------+------------------+

The result is not null when a constant is specified without the function:

EXEC sp_describe_first_result_set N'SELECT 1 AS result;';

+-----------+----------------+--------+-------------+----------------+------------------+
| is_hidden | column_ordinal |  name  | is_nullable | system_type_id | system_type_name |
+-----------+----------------+--------+-------------+----------------+------------------+
|         0 |              1 | result |           0 |             56 | int              |
+-----------+----------------+--------+-------------+----------------+------------------+

But the result data type is int instead of the desired bit as SQL Server has no bit constant without implicit or explict conversion, ISNULL is needed to coerce a not null result as @MichaelGreen answered.

6

To answer your actual question - I have no idea. My guess is that the parser can examine columns directly to determine nullability. If there's anything between the column reference and the output - such as a function or an expression - that might affect the nullability the output is assumed to be nullable. MS simply hasn't written code to examine all circumstances in depth and reason about the outcomes. They, too, have budgets and timeline and backlog just like everyone else. This simply hasn't been given priority.

That hypothesis got me thinking, though. If it can interpret columns, what constructs can it reason about? If I could offer it a sufficiently simple expression that is guaranteed to be not null would it produce the desired output. The answer is yes, with ISNULL:

create or alter view IdIsTwoView
as

select
    Id,
    (
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    ) as IdIsTwo,
    isnull(
        case
        when Id = 2
        then cast(1 as bit)
        else cast(0 as bit)
        end
    , -1) as X
from Anything;

I chose -1 for the replacement value as it is outside the range of the identity so will be an obvious error if seen. Any constant would work, I'd think.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.