SQL Server Computed Column: how to set it to NOT NULL, without persistence

Question

I am adding a computed column, which should be NOT NULL on an existing table. Both my source column for the computed value are NOT NULL of course. I would like to make the computed column not persistent, to save space, but I fail to do so:

ALTER TABLE UnitHistory
ADD 
    [PrefixedUnitId] AS ((UnitHistory.UnitIdPrefix + cast(UnitHistory.UnitId     
    AS nvarchar(8)))) NOT NULL
GO

gives

Only UNIQUE or PRIMARY KEY constraints can be created on computed columns, while CHECK, FOREIGN KEY, and NOT NULL constraints require that computed columns be persisted. Msg 15135, Level 16, State 15, Procedure sp_addextendedproperty, Line 72 Object is invalid. Extended properties are not permitted on 'dbo.UnitHistory.PrefixedUnitId', or the object does not exist.

Now, I understand what the message says, and sure enough, using PERSISTED NOT NULL does produce the column.

To save space however, I would like to have the column not be persisted.

Is there a way to have a computed column NOT NULL, but not persistent, when the underlying columns are NOT NULL?

Note: It should work on a SQL Server 2012 SP2

It's not immediately clear why you need this constraint. The only way this column could be NULL is if either of the source columns (`UnitIdPrefix` and `UnitId`) is NULL. You can check for this by setting NOT NULL on the source columns (if they aren't already). — Ed Harper, Jul 04 '19 at 07:12
@EdHarper *"Both my source column for the computed value are NOT NULL of course"*. — GSerg, Jul 04 '19 at 07:17
@Marcel Wrap the column expression in an `isnull(..., N'')`. — GSerg, Jul 04 '19 at 07:18
`COALESCE` and `ISNULL` are the only expressions that will set the "not nullable" flag for metadata discovery purposes. All other expressions, even the most trivial ones, will mark the computed column as nullable (save for a direct alias of a non-nullable column, e.g. `ComputedColumn AS NonNullableColumn`), regardless of whether the expression can ever actually yield `NULL`. — Jeroen Mostert, Jul 04 '19 at 14:04

score 2 · Answer 1 · answered Jul 04 '19 at 12:23

This seems totally redundant, but you can repeat the condition in a check constraint:

ALTER TABLE UnitHistory
    ADD PrefixedUnitId AS ( UnitHistory.UnitIdPrefix + cast(UnitHistory.UnitId AS nvarchar(8)) );

ALTER TABLE UnitHistory
    ADD CONSTRAINT CHECK ( UnitHistory.UnitIdPrefix + cast(UnitHistory.UnitId AS nvarchar(8)) IS NOT NULL );

However, because the source columns are NOT NULL, the result can never be NULL.

The notion of non-persisted computed columns is that they are calculated on output not input. I think this explains why check constraints are not appropriate.

For instance, you can have a table that has a computed column that generates an error and still fully use the table:

create table t (
    id int identity primary key,
    x int,
    computed as (1 / 0)
);

-- works
insert into t (x) values (1);

-- works
select x
from t;

-- fails
select *
from t;

Here is a db<>fiddle.

Incidentally, a computed column that always generates an error has a nice side effect -- it prevents users from using select * if that is something you want to do.

I find using a constraint rather odd. Would an ORM like Entity Framework or a Browser like MSSQL Studio handle that as intended (processing the column as NOT NULL)? — Marcel, Jul 04 '19 at 12:52
@Marcel . . . No. `NOT NULL` is a special type of constraint that is stored in the data pages using a `NULL` bit in SQL Server. In other words, if you want something to be fully understood as `NOT NULL` you *have to persist it*. — Gordon Linoff, Jul 04 '19 at 12:59

SQL Server Computed Column: how to set it to NOT NULL, without persistence

1 Answers1