How to enforce NOT NULL in a view's computed column

Question

Dear Colleagues,

I want to alter a view as follows:

ALTER VIEW [dbo].[ViewOne] as  
SELECT columnOne,  -- not null  
       columnTwo, --not null
      (convert(decimal(2,0), columnOne)) as columnThree -- I want this not to be NULL
FROM DBOne.TableOne

Since columnOne is "not null" I want to force columnThree to be "not null" also. Is is possible, impossible, implicit, useless or could cause serious problems since columnOne is char(2) populated with algarisms only.

I simply would like to know the syntax

Answer 1

You can use ISNULL() to ensure a default value when null.

ALTER VIEW [dbo].[ViewOne] as   
SELECT columnOne,  -- not null   
       columnTwo, --not null 
      ISNULL((convert(decimal(2,0), columnOne)),0.00) as columnThree
FROM DBOne.TableOne 

Answer 2

If column1 is constrained to be NOT NULL, then column3 can't be NULL, so there's no need to worry about it.

Answer 3

How you enforce it depends on your business rules.

Do you want those rows to not show up in the view results? Then add that criteria to the view WHERE clause.

Do you want to use a default value if the column would be NULL? Then use COALESCE to return your default value for NULLs (just remember to alias the column).

Do you want an error returned if a row is inserted into the underlying table(s) that would cause such a thing? In that case I would put the constraint on the underlying table(s). If your view includes JOINs and aggregates then that might be difficult, but without a specific example I can't really help on that.

In any case, for your specific example you shouldn't see any NULL values since columnOne is NOT NULL.

Answer 4

ColumnThree will never be null if the source of the Cast is itself never null. However, that does not mean you will not get an exception if ColumnOne cannot be cast to decimal(2,0) and you will not know whether you will get an exception until you query against the view. You should consider adding an additional check to determine whether the cast will fail and help mitigate the possibility of a cast error:

Alter View dbo.ViewOne
As
Select ColumnOne, ColumnTwo
    , Case
        When IsNumeric( ColumnOne ) = 0 Then 0
        Else Cast( ColumnOne As decimal(2,0) )
        End As ColumnThree