How could I force the data type of a computed column to a bit field that don't allow nulls?

Question

I got a computed column that I need to be a bit field, here an example of formula :

case when ([some_field] < [Some_Other_field]) 
then 0 
else 1 
end

The data type of computed column set using this formula is int.

What's the best way to force the correct data type?

With a CONVERT statement on the whole case, data type is bit but it Allow Nulls

CONVERT([bit], 
        case when (([some_field] < [Some_Other_field]) 
        then 0 
        else 1 
        end,
        0)

Same thing with a CONVERT statement on the result expressions, data type is bit but it Allow Nulls

case when (([some_field] < [Some_Other_field]) 
then CONVERT([bit], (0), 0) 
else CONVERT([bit], (1), 0) 
end

Or there is a smarter way of doing this?

Damien_The_Unbeliever · Accepted Answer · 2011-08-30T14:07:04.510

22

Wrap the computed column definition in ISNULL, with whatever you like as the second argument (provided it's a bit, or convertible to such).

This is one of the few places where you have to use ISNULL rather than (the generally better designed) COALESCE. SQL Server has special case logic to realise that an ISNULL with a non-null second argument represents a non-nullable result.

E.g.:

ISNULL(CONVERT(bit,case when ([some_field] < [Some_Other_field]) 
then 0 
else 1 
end),0)

This can also be used in e.g. view definitions.

edited Aug 30 '11 at 14:07

answered Aug 30 '11 at 13:55

Damien_The_Unbeliever

234,701
27
340
448

Do you or anyone has an idea if there are performance differences between your example and the following alternative syntax: `case when ([some_field] < [Some_Other_field]) then ISNULL(CONVERT(bit,0),0) else ISNULL(CONVERT(bit,1),1) end`? – tomosius Sep 13 '17 at 15:34
@tomosius - I wouldn't expect a *measurable* difference in the grand scheme of things (e.g. I/O costs related to data access and network overheads). I'd also suspect that your variant may reintroduce the very issue that the question sought to avoid - that `CASE` expressions are frequently analysed as potentially being NULLable even though by inspection we know it cannot. – Damien_The_Unbeliever Sep 13 '17 at 16:27

score 12 · Answer 2 · answered Aug 30 '11 at 13:57

If you persist the computed column you can specify not null. http://msdn.microsoft.com/en-us/library/ms186241.aspx

CONVERT([bit], 
        case when (([some_field] < [Some_Other_field]) 
        then 0 
        else 1 
        end,
        0) PERSISTED NOT NULL

How could I force the data type of a computed column to a bit field that don't allow nulls?

2 Answers2