为何LEAST表达式计算列被视为不精确且无法创建索引？

环境信息 #

• 数据库兼容级别（COMPATIBILITY_LEVEL）：160
• ANSI标准SET选项配置：
  • ANSI_NULLS、ANSI_PADDING、ANSI_WARNINGS、ARITHABORT、CONCAT_NULL_YIELDS_NULL、QUOTED_IDENTIFIER 均设为ON
  • NUMERIC_ROUNDABORT 设为OFF
  • 已验证SSMS连接的ANSI_NULLS为ON
• 部署目标：Azure SQL、SQL Server 2022

表与计算列定义 #

创建表 #

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

添加非持久化计算列 #

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

索引创建需求与条件检查 #

希望在LeastDate和GreatestDate上创建非聚集索引，且不将其设置为PERSISTED列。根据官方文档，计算列创建索引需满足以下条件，检查结果如下：

• 计算列引用的所有函数与表所有者一致：✅ 未使用用户定义函数（UDF）
• 计算列表达式具有确定性：✅ 通过COLUMNPROPERTY()验证，LEAST/GREATEST表达式的IsDeterministic=1
• 兼容级别90时无法在非Unicode表达式计算列建索引：✅ 表达式非char/varchar类型
• 计算列表达式必须精确：
  • 结果非float/real类型：✅ 符合
  • 定义中未使用float/real类型：✅ 符合
  • COLUMNPROPERTY的IsPrecise属性：❌ 返回0（但文档指出该属性仅对float/real类型列非NULL，datetime2(7)列应返回NULL）
• 结果数据类型合规：✅ 非text、ntext、image、xml或max长度类型
• SET选项符合要求：✅ 已按标准配置

COLUMNPROPERTY查询与结果 #

查询语句 #

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';

查询结果 #

可见问题核心为IsPrecise=0，但按文档描述，datetime2(7)类型的列该属性应为NULL。

临时解决方案（非理想） #

将计算列改为PERSISTED后即可创建索引：

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;

修改后COLUMNPROPERTY查询结果 #

内容的提问来源于stack exchange，提问作者Dai