CODEWITHSUNDEEP
sql-servercalculated-columnsdeterministicnon-deterministicsql-variant
Max Buzyak
Max Buzyak

Reputation: 55

How to convert sql_variant so it can be considered deterministic

I am trying to create a persisted computed column in a SYSTEM_VERSIONING table dbo.Users as follows:

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

Where usr.GetSession() is just retrieving the value stored as BIGINT in SESSION_CONTEXT('IdSession') and converting it to again to BIGINT.

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

But getting the following error:

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

It is obviously because:

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

Is returning 0

A little bit of searching found this about Deterministic and nondeterministic functions

CONVERT
Deterministic unless one of these conditions exists:

  • Source type is sql_variant.
  • Target type is sql_variant and its source type is nondeterministic.

So, I am understanding that there is no way to make my computed column persisted with a user defined scalar function as sql_variant cannot be handled as deterministic value.

Or there can be some walk around to solve my problem? Or some other solution? Any idea?

Upvotes: 2

Views: 943

Answers (1)

Charlieface
Charlieface

Reputation: 72194

No, there is no workaround. You cannot do anything with sql_variant unless you convert it (even implicitly), and as you mention, that is not deterministic.

Be that as it may, it seems you are going down the wrong road anyway.

A computed column is the wrong thing here, as in this case it would change every time it was read, whereas it seems you want it changed every time the row is inserted.

Instead you need a DEFAULT

ALTER TABLE dbo.Users 
    ADD SessionId bigint DEFAULT (usr.GetSession())

Upvotes: 1

Related Questions