如何转换sql_variant使函数具备确定性？解决SQL Server持久化计算列问题

尝试在启用SYSTEM_VERSIONING的dbo.Users表中创建持久化计算列，执行语句如下：

ALTER TABLE dbo.Users 
    ADD SessionId AS usr.GetSession() PERSISTED 
        CONSTRAINT FK_dboUsers_IdSession 
            FOREIGN KEY REFERENCES dbo.Sessions(IdSession)

其中usr.GetSession()函数用于获取存储在SESSION_CONTEXT('IdSession')中的BIGINT值并转换为BIGINT类型，函数定义如下：

CREATE OR ALTER FUNCTION usr.GetSession()
RETURNS BIGINT WITH SCHEMABINDING
AS
BEGIN
    RETURN CONVERT(BIGINT, SESSION_CONTEXT(N'IdSession'))
END

执行时出现错误：

Computed column 'SessionId' in table 'Users' cannot be persisted because the column is non-deterministic.

经检查，执行以下语句返回0（表示函数非确定性）：

SELECT OBJECTPROPERTY(OBJECT_ID('usr.GetSession'), 'IsDeterministic') AS IsDeterministic;

查阅文档得知，当CONVERT函数的源类型为sql_variant时，函数会被判定为非确定性，而SESSION_CONTEXT返回的正是sql_variant类型，因此导致计算列无法持久化。

解决方案 #

要解决这个问题，需要将函数修改为确定性函数，核心思路是绕开直接从sql_variant转换为BIGINT的操作，先将sql_variant转换为确定性的中间类型（比如VARCHAR），再转换为BIGINT：

修改后的函数定义 #

CREATE OR ALTER FUNCTION usr.GetSession()
RETURNS BIGINT WITH SCHEMABINDING
AS
BEGIN
    -- 先将sql_variant转为VARCHAR，再转为BIGINT，确保转换操作是确定性的
    RETURN CONVERT(BIGINT, CONVERT(VARCHAR(20), SESSION_CONTEXT(N'IdSession')))
END

可选：添加容错处理 #

如果SESSION_CONTEXT('IdSession')可能存在无效值，可以使用TRY_CONVERT避免转换报错，返回NULL：

CREATE OR ALTER FUNCTION usr.GetSession()
RETURNS BIGINT WITH SCHEMABINDING
AS
BEGIN
    RETURN TRY_CONVERT(BIGINT, CONVERT(VARCHAR(20), SESSION_CONTEXT(N'IdSession')))
END

验证函数确定性 #

修改函数后，再次执行以下语句，返回值应为1（表示函数已变为确定性）：

SELECT OBJECTPROPERTY(OBJECT_ID('usr.GetSession'), 'IsDeterministic') AS IsDeterministic;

此时重新执行最初的ALTER TABLE语句，即可成功创建持久化计算列。

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