Why is a LEAST column expression considered imprecise and not indexable?

Question

I have a SQL Server database project:

• With COMPATIBILITY_LEVEL = 160
• With ANSI-compliant SET options:
  • ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER all ON.
  • NUMERIC_ROUNDABORT is OFF
  • I also verified my SSMS connection-specific ANSI_NULLS is ON.

It's deployed in both Azure SQL and SQL Server 2022.

I created a table, like this:

CREATE TABLE dbo.Foobar ( 
    FooId               int          NOT NULL IDENTITY PRIMARY KEY,
    DateCreated         datetime2(7)     NULL,
    DateDiscombobulated datetime2(7)     NULL,
    DatePalm            datetime2(7)     NULL,
    DateIsActuallyAFig  datetime2(7)     NULL
);

And a computed column:

ALTER TABLE dbo.Foobar ADD
    LeastDate AS LEAST(
        DateCreated,
        DateDiscombobulated,
        DatePalm,
        DateIsActuallyAFig
    ),
    GreatestDate AS GREATEST(
        DateCreated,
        DateDiscombobulated,
        DatePalm,
        DateIsActuallyAFig
    );

I want to create a nonclustered index over LeastDate and GreatestDate - ideally without making them PERSISTED columns; according to the documentation the following requirements must be met:

• All function references in the computed column must have the same owner as the table. - ✔️ No UDFs are being referenced.
• The computed_column_expression must be deterministic - ✔️ The LEAST expression has IsDeterministic = 1 according to COLUMNPROPERTY().
• When the database compatibility level setting is 90, you can't create indexes on computed columns that contain [non-Unicode] expressions. - ✔️ This is not a char/varchar expression.
• The computed_column_expression must be precise:
  • It isn't an expression of the float or real data types. - ✔️ Indeed.
  • It doesn't use a float or real data type in its definition. - ✔️ Indeed, it does not.
  • The IsPrecise property of the COLUMNPROPERTY function reports whether a computed_column_expression is precise. - ❌ IsPrecise = 0 here.
• Data-type requirements: cannot evaluate to text, ntext, image - or be derived from xml or max-length types. ✔️
• SET options must be: ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, and QUOTED_IDENTIFIER all ON - and NUMERIC_ROUNDABORT is OFF - ✔️

---

Here is the COLUMNPROPERTY query:

DECLARE @tableId int = OBJECT_ID('dbo.Foobar');

SELECT
    OBJECTPROPERTY( @tableId, 'IsAnsiNullsOn' ) AS IsAnsiNullsOn;

SELECT
    c.COLUMN_NAME,
    COLUMNPROPERTY( @tableId, c.COLUMN_NAME, 'IsDeterministic') AS IsDeterministic,
    COLUMNPROPERTY( @tableId, c.COLUMN_NAME, 'IsIndexable'    ) AS IsIndexable,
    COLUMNPROPERTY( @tableId, c.COLUMN_NAME, 'IsPrecise'      ) AS IsPrecise
FROM
    INFORMATION_SCHEMA.COLUMNS AS c
WHERE
    c.TABLE_NAME = N'Foobar';

Results:

COLUMN_NAME	IsDeterministic	IsIndexable	IsPrecise
FooId	NULL	1	NULL
DateCreated	NULL	1	NULL
DateDiscombobulated	NULL	1	NULL
DatePalm	NULL	1	NULL
DateIsActuallyAFig	NULL	1	NULL
LeastDate	1	0	0
GreatestDate	1	0	0

It looks like it's because IsPrecise = 0, but IsPrecise should be NULL for datetime2(7) columns - the documentation says it's only NOT NULL for float/real typed-columns (I think?).

---

Converting the computed columns to PERSISTED allows them to be indexed, but this isn't ideal for my situation:

ALTER TABLE dbo.Foobar DROP LeastDate, GreatestDate;
GO

ALTER TABLE dbo.Foobar 
    ADD LeastDate AS LEAST(
        DateCreated, DateDiscombobulated, DatePalm, DateIsActuallyAFig
    ) PERSISTED,
    GreatestDate AS GREATEST(
        DateCreated, DateDiscombobulated, DatePalm, DateIsActuallyAFig
    ) PERSISTED;

Re-running the COLUMNPROPERTY query now returns:

COLUMN_NAME	IsDeterministic	IsIndexable	IsPrecise
LeastDate	1	1	0
GreatestDate	1	1	0

Answer 1

The LEAST() and GREATEST() functions are always evaluated as imprecise when used in a computed column. The SQL engine will not recognise that all function arguments are of the same type.

There's 2 things you could try.

Use CONVERT to explicitly specify data type

ALTER TABLE dbo.Foobar ADD
    LeastDate AS CONVERT(datetime2(7), LEAST(
        CONVERT(datetime2(7), DateCreated),
        CONVERT(datetime2(7), DateDiscombobulated),
        CONVERT(datetime2(7), DatePalm),
        CONVERT(datetime2(7), DateIsActuallyAFig)
    )),
    GreatestDate AS CONVERT(datetime2(7), GREATEST(
        CONVERT(datetime2(7), DateCreated),
        CONVERT(datetime2(7), DateDiscombobulated),
        CONVERT(datetime2(7), DatePalm),
        CONVERT(datetime2(7), DateIsActuallyAFig)
    ));

Use CASE comparison

You'll need to take a different approach using CASE. From a computation overhead perspective I don't expect this will be much (if at all) different to using the functions.

ALTER TABLE dbo.Foobar ADD
    LeastDate AS CASE
                    WHEN DateCreated <= DateDiscombobulated AND DateCreated <= DatePalm AND DateCreated <= DateIsActuallyAFig THEN DateCreated
                    WHEN DateDiscombobulated <= DatePalm AND DateDiscombobulated <= DateIsActuallyAFig THEN DateDiscombobulated
                    WHEN DatePalm <= DateIsActuallyAFig THEN DatePalm
                    ELSE DateIsActuallyAFig
                 END,
    GreatestDate AS CASE
                       WHEN DateCreated >= DateDiscombobulated AND DateCreated >= DatePalm AND DateCreated >= DateIsActuallyAFig THEN DateCreated
                       WHEN DateDiscombobulated >= DatePalm AND DateDiscombobulated >= DateIsActuallyAFig THEN DateDiscombobulated
                       WHEN DatePalm >= DateIsActuallyAFig THEN DatePalm
                       ELSE DateIsActuallyAFig
                    END;

NOTE: Negating use of PERSISTED will obviously have an impact on performance, since SQL needs to maintain the index, and any updates to the compated dates will cause reevaluation of the computed column and possible index changes.