How to make computed column not nullable?

Question

So far I've been using ISNULL(dbo.fn_GetPrice(ItemId), 0) to make it not nullable (rather call it default-valued, but whatever).

Is this the right way?

Answer 1

Yes, that is the right way to do it. By using the isnull function you are creating an expression that must return a value, no matter what. This is evaluated by SQL Server to be a computed column that is not null.

Answer 2

If it works for you, then yes. There's nothing inherently bad about doing it that way. You could also mark the column as persisted and not null.

Answer 3

I'd prefer the ANSI standard COALESCE function, but ISNULL is fine. To use COALESCE, define your computed column as:

COALESCE(dbo.fn_GetPrice(ItemId), 0)

EDIT Learn something new everyday. I did the following:

create table t (c1 int null
    , c2 as isnull(c1, 1) 
    , c3 as isnull(c1, null)
    , c4 as coalesce(c1, 1)
    , c5 as coalesce(c1, null)
    )

exec sp_help t

And c2 is indeed not nullable according to sp_help, but c4 is reported as being nullable, even though there is no way that coalesce expression could result in a null value.

Also as of 2008, I don't know whether the option exists in 2005, one can persist a computed column and add a constraint:

create table t (c1 int null
    , c2 as isnull(c1, 1) persisted not null
    , c3 as isnull(c1, null) persisted not null
    , c4 as coalesce(c1, 1) persisted not null
    , c5 as coalesce(c1, null) persisted not null
    )
go
insert into t (c1) values (null)

results in a constraint violation.