CODEWITHSUNDEEP
sql-servert-sql
masa
masa

Reputation: 2820

How to define a non-null computed column in T-SQL?

I have the following column in a database table:

FieldA   (decimal(19, 2), not null)

Applying the following change:

ALTER TABLE dbo.MyTable
ADD
   Field1 AS FieldA,
   Field2 AS CONVERT(decimal(19,2), FieldA),
   Field3 AS CAST(FieldA AS decimal(19, 2))

Gives me the following new columns:

Field1    (Computed, decimal(19, 2), not null)
Field2    (Computed, decimal(19, 2), null)
Field3    (Computed, decimal(19, 2), null)

Why are Field2 and Field3 nullable? How to make either of them non-nullable?

Please do note that FieldAin Field2and Field3 will be replaced with some computation on FieldA, thus need to use either CONVERT() or CAST() to force the type of the result.

Upvotes: 0

Views: 522

Answers (2)

Zohar Peled
Zohar Peled

Reputation: 82514

While I like HoneyBadger's answer, I want to offer another possibility:
Mark the computed columns as persisted not null. (non persisted computed columns does not support the not null constraint)

This means that instead of being computed on demand (meaning, only when they are used in a select statement), the computed columns will be physically stored with the rest of the table's columns - and only gets computed when the value they rely on changes.

Persisted computed columns can only rely on constants, columns and deterministic functions - but convert and cast are deterministic, so no problem there:

CREATE TABLE dbo.MyTable
(
    FieldA decimal(19, 2) not null
);

GO

ALTER TABLE dbo.MyTable
ADD
   Field1 AS FieldA,
   Field2 AS CONVERT(decimal(19,2), FieldA) PERSISTED NOT NULL,
   Field3 AS CAST(FieldA AS decimal(19, 2)) PERSISTED NOT NULL;

GO

SELECT Column_Name, Is_Nullable
FROM Information_schema.Columns
WHERE Table_Name = 'MyTable'

Results:

Column_Name     Is_Nullable
FieldA          NO
Field1          NO
Field2          NO
Field3          NO

Upvotes: 1

HoneyBadger
HoneyBadger

Reputation: 15150

@RaduGheorghiu Explained in their comment why the attributes are nullable. Now, to make them not nullable, I wrote this little test:

DROP TABLE IF EXISTS dbo.test
GO

CREATE TABLE dbo.test (id INT NOT NULL)
GO

ALTER TABLE dbo.test ADD 
field1 AS id
, field2 AS CONVERT(DECIMAL(5,2), id)
, field3 AS ISNULL(CONVERT(DECIMAL(5,2), id), 0.0)
, field4 AS COALESCE(CONVERT(DECIMAL(5,2), id), 0.0)
, field5 AS CASE WHEN id IS NOT NULL THEN CONVERT(DECIMAL(5,2), id)
                 ELSE 0
            END
GO

This produces the following table:

+-------------+-----------+-------------+
| COLUMN_NAME | DATA_TYPE | IS_NULLABLE |
+-------------+-----------+-------------+
| id          | int       | NO          |
| field1      | int       | NO          |
| field2      | decimal   | YES         |
| field3      | decimal   | NO          |
| field4      | decimal   | YES         |
| field5      | decimal   | YES         |
+-------------+-----------+-------------+

So you can force nullability with ISNULL, but, rather surprisingly, not with COALESCE or a CASE expression.

Upvotes: 1

Related Questions