如何判断SQL Server表中的列是否为GENERATED ALWAYS类型时态列

你之前使用COLUMNPROPERTY的IsComputed属性返回0，是因为时态表的GENERATED ALWAYS AS ROW START/END列不属于常规的计算列范畴——该属性仅针对用户自定义的计算列，无法识别系统版本控制专用的这类列。

要正确判断某列是否为GENERATED ALWAYS类型，可直接查询SQL Server的系统视图sys.columns，该视图中的generated_always_type字段专门标识这类列：

• 值0：非GENERATED ALWAYS列
• 值1：GENERATED ALWAYS AS ROW START
• 值2：GENERATED ALWAYS AS ROW END

对应的查询语句 #

declare @TABLE_SCHEMA as nvarchar(255) = 'dbo'
declare @TABLE_NAME as nvarchar(255) = 'Profile'
declare @COLUMN_NAME as nvarchar(255) = 'CreatedDate'

SELECT 
    CASE 
        WHEN c.generated_always_type IN (1, 2) THEN 1 
        ELSE 0 
    END AS IsGeneratedAlways
FROM 
    sys.columns c
JOIN 
    sys.tables t ON c.object_id = t.object_id
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    s.name = @TABLE_SCHEMA
    AND t.name = @TABLE_NAME
    AND c.name = @COLUMN_NAME

执行后，返回1表示该列是GENERATED ALWAYS类型，返回0则不是。

补充：通过时态表的PERIOD信息验证 #

如果你想通过时态表的周期定义来验证，也可以关联sys.periods视图查询，确认列是否属于系统时间周期的起始/结束列：

declare @TABLE_SCHEMA as nvarchar(255) = 'dbo'
declare @TABLE_NAME as nvarchar(255) = 'Profile'
declare @COLUMN_NAME as nvarchar(255) = 'CreatedDate'

SELECT 
    1 AS IsGeneratedAlways
FROM 
    sys.periods p
JOIN 
    sys.tables t ON p.object_id = t.object_id
JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    s.name = @TABLE_SCHEMA
    AND t.name = @TABLE_NAME
    AND (p.start_column_id = (SELECT column_id FROM sys.columns WHERE object_id = t.object_id AND name = @COLUMN_NAME)
         OR p.end_column_id = (SELECT column_id FROM sys.columns WHERE object_id = t.object_id AND name = @COLUMN_NAME))
UNION ALL
SELECT 0
WHERE NOT EXISTS (
    SELECT 1
    FROM 
        sys.periods p
    JOIN 
        sys.tables t ON p.object_id = t.object_id
    JOIN 
        sys.schemas s ON t.schema_id = s.schema_id
    WHERE 
        s.name = @TABLE_SCHEMA
        AND t.name = @TABLE_NAME
        AND (p.start_column_id = (SELECT column_id FROM sys.columns WHERE object_id = t.object_id AND name = @COLUMN_NAME)
             OR p.end_column_id = (SELECT column_id FROM sys.columns WHERE object_id = t.object_id AND name = @COLUMN_NAME))
)

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