Calculated, non-nullable, non-persisted nvarchar in SQL

Question

Consider the following schema:

CREATE TABLE [dbo].[User]
(
    [Id]                BIGINT              NOT NULL    IDENTITY (1, 1),
    [NameGiven]         NVARCHAR (256)      NOT NULL,
    [NameMiddle]        NVARCHAR (256)      NOT NULL    CONSTRAINT [DEFAULT_User_NameMiddle] DEFAULT (''),
    [NameFamily]        NVARCHAR (256)      NOT NULL,
    [Email]             NVARCHAR (256)      NOT NULL,
    [NameTest]          AS ' ', -- Shows as NON NULL.
    [NameFull]          AS  COALESCE(REPLACE(CONCAT(TRIM([NameGiven]), ' ', TRIM([NameMiddle]), ' ', TRIM([NameFamily])),'  ',' '), ''),
    [NameFullOutlook]   AS  COALESCE(REPLACE(CONCAT(TRIM([NameGiven]), ' ', TRIM([NameMiddle]), ' ', TRIM([NameFamily]), ' ', '(', [Email],')'), '  ', ' '), ''),
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);

The statement is legal and creates the desired table, but out of the three calculated columns, only [NameTest] shows up as NOT NULL when I query the schema or view it in the SSMS designer.

Note that the calculated fields refer only to other columns that are NVARCHAR and NOT NULL. I want the [NameFull] and [NameFullOutlook] columns to show up as non-null as well. These columns should not be persisted.

So I suspect that one or more of the functions being used (COALESCE, CONCAT, REPLACE, TRIM) are causing this. Is there an elegant way to fix this without making the formula too convoluted to read?

In case anyone wants to know, I am writing a small expression evaluator for end-users, hence want the syntax to be succint.

UPDATE: I even tried adding + ' ' to the end of the formulas, and it still shows up as null.

Answer 1

The problem is your use of COALESCE, which is defined as NULLable. There is, however, no need for it, in truth; you can make the whole expression more succinct with CONCAT_WS:

CREATE TABLE [dbo].[User] --USER is a reserved keyword, and is a poor choice of name for an object. Use a better, more descriptive, name
(
    [Id]                bigint              NOT NULL    IDENTITY (1, 1),
    [NameGiven]         nvarchar (256)      NOT NULL,
    [NameMiddle]        nvarchar (256)      NOT NULL    CONSTRAINT [DEFAULT_User_NameMiddle] DEFAULT (''),
    [NameFamily]        nvarchar (256)      NOT NULL,
    [Email]             nvarchar (256)      NOT NULL,
    [NameTest]          AS ' ', -- Shows as NON NULL.
    [NameFull]          AS  CONCAT_WS(' ',TRIM([NameGiven]),TRIM([NameMiddle]), TRIM([NameFamily])),
    [NameFullOutlook]   AS  CONCAT_WS(' ',TRIM([NameGiven]), TRIM([NameMiddle]), TRIM([NameFamily]), '(', [Email],')'),
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);

If you can't use CONCAT_WS,you can spoof it with CONCAT and STUFF instead, which is more succinct, but you'll still need a ISNULL, to force NULLability:

CREATE TABLE [dbo].[User]
(
    [Id]                BIGINT              NOT NULL    IDENTITY (1, 1),
    [NameGiven]         NVARCHAR (256)      NOT NULL,
    [NameMiddle]        NVARCHAR (256)      NOT NULL    CONSTRAINT [DEFAULT_User_NameMiddle] DEFAULT (''),
    [NameFamily]        NVARCHAR (256)      NOT NULL,
    [Email]             NVARCHAR (256)      NOT NULL,
    [NameTest]          AS ' ', -- Shows as NON NULL.
    [NameFull]          AS  ISNULL(STUFF(CONCAT(' ' + TRIM([NameGiven]), ' '+ TRIM([NameMiddle]), ' ' + TRIM([NameFamily])), 1, 1, ''),''),
    [NameFullOutlook]   AS  ISNULL(STUFF(CONCAT(' ' + TRIM([NameGiven]), ' '+ TRIM([NameMiddle]), ' ' + TRIM([NameFamily]), ' ' + '(', [Email],')'), 1, 1, ''),''),
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);

db<>fiddle

Answer 2

You can go for ISNULL instead of COALESCE, as they are treated differently for Nullability, as explained in the below MSDN article.

The NULLability of the result expression is different for ISNULL and COALESCE. The ISNULL return value is always considered NOT NULLable (assuming the return value is a non-nullable one). By contrast,COALESCE with non-null parameters is considered to be NULL. So the expressions ISNULL(NULL, 1) and COALESCE(NULL, 1), although equal, have different nullability values. These values make a difference if you're using these expressions in computed columns, creating key constraints or making the return value of a scalar UDF deterministic so that it can be indexed

You can go for ISNULL as given below:

CREATE TABLE [dbo].[User]
(
    [Id]                BIGINT              NOT NULL    IDENTITY (1, 1),
    [NameGiven]         NVARCHAR (256)      NOT NULL,
    [NameMiddle]        NVARCHAR (256)      NOT NULL    CONSTRAINT [DEFAULT_User_NameMiddle] DEFAULT (''),
    [NameFamily]        NVARCHAR (256)      NOT NULL,
    [Email]             NVARCHAR (256)      NOT NULL,
    [NameTest]          AS ' ', -- Shows as NON NULL.
    [NameFull]          AS  ISNULL(REPLACE(CONCAT(TRIM([NameGiven]), ' ', TRIM([NameMiddle]), ' ', TRIM([NameFamily])),'  ',' '), ''),
    [NameFullOutlook]   AS  ISNULL(REPLACE(CONCAT(TRIM([NameGiven]), ' ', TRIM([NameMiddle]), ' ', TRIM([NameFamily]), ' ', '(', [Email],')'), '  ', ' '), ''),
    CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ([Id] ASC)
);