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

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

Answer 2

As Scoregraphic notes, you can do this 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.