sqlsql-servernullnvarchar

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

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.

SSMS Table Designer

Solution

  • 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)
);