Marking persisted computed columns NOT NULL in SQL Server Management Studio

Question

It is possible in SQL Server 2005 to create a computed column that is both persisted and is defined as NOT NULL (cannot contain a null value). The 2nd attribute is of importance when using libraries like Linq2Sql if we want to avoid a lot of manual work to assure our code that the column 'always' has a value.

With straight SQL this is very simple:
ALTER TABLE Sales ADD Total AS (Price + Taxes) PERSISTED NOT NULL

When viewed in the design window of SQL Server Management studio this column is correctly shown as a computed column with no checkmark for 'allows nulls'. However I run into a problem creating new columns in the designer to match this pattern: The formula is entered in the Computed Column Specification -> (Formula) property, and the persisted property is specified by setting Is Persisted to Yes, but attempting to uncheck the 'allows nulls' on a new computed column results in a dialog stating "Property cannot be modified".

I need to cover a wide range of skill levels, and to do so I need to provide procedures for adding columns that even a novice can follow (which means the Management Studio designer window). Is there some secret in SQL Server Management Studio for creating a new computed column as NOT NULL in the designer, similar to say how CTRL+0 can be used to insert nulls into cells?

Answer 1

I tried this and looking at the treeview on the left it had indeed set the column up as not null, even though in the right pane designer the checkbox was not checked...

ALTER TABLE Sales ADD Total AS ISNULL(isnull(Price,0) + isnull(Taxes,0),0) PERSISTED NOT NULL

Answer 2

You may cheat this with ISNULL(Price + Taxes, 0) which uses the default value 0 for NULL computations.

Answer 3

According to this article nullability is determined by sqlserver based on the possible value of the computed expression. since Price or Taxes is probably nullable, it cannot be sure that their sum is never null. However, as @Richard suggests, using the ISNULL method avoids this. Declaring the column NOT NULL should however not be necesary as far as I have understood.

Answer 4

As Scoregraphic notes, you can do with with ISNULL.

I often use this for computed flags, e.g., in the User table, I have a DeletedDate to know when the account was deleted. I then create a computed non-nullable boolean column called IsDeleted (type bit) like this:

isnull(case when DeletedDate is null then 0 else 1 end, 0)

The important thing to note is that the ISNULL must be on the outermost part of the expression for the designer to realize it is a non-nullable computed column.